Free Microsoft Excel 2013 Quick Reference

Combine Multiple Like Operators in VBA

So, I have a few lines in VBA that should when run sort through cells in the range and then based on whether it finds the partial strings in the Range delete the entire column. However, I can't get it to work without splitting it up into two For statements. Currently it only searches for the Second Case.

For Each Cell In Range("L1:AX1")
    Select Case True
    Case Cell Like "*XBA*"
    Cell.EntireColumn.Delete
    Case Cell Like "LBO*"
    Cell.EntireColumn.Delete
End Select
Next Cell
Is there a way two do this without splitting it up into two statements?


Hi everyone,
I would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:
- There will always be exactly three independent variables
- There are no missing values
- The data is allways numerical
I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Does anybody know where I can find a (simple) source code allowing me to conduct a regression with three input parameters? Many thanks in advance

Hello

I am trying to do a SumIf based on 2 criterion in VBA:

Code:
I would use a SUMPRODUCT if using non-VBA method, equivalent would be:

=SUMPRODUCT(--(A1:A100=6100100),--(B1:B100="P700165",C1:C100)

I have tried using Union and Evaluate to join the criterion ranges PcCol and AcCol but to no avail. This is embedded in a loop which could have up to 50000 iterations so I think I would like to avoid using .Formula="=SUMPRODUCT(etc)" method.

Does anybody have a good idea about how to achieve equivalent multiple criterion SumIf in VBA?

For reference, PcCol, AcCol and pVal and column ranges returned thru Inputbox Type:=8.

Many thanks
Jon

Hi there,

Just a quick question as to how do I use the "AND" & "OR" operators in VBA. I know you can do it in excel where "AND(Logical,,,)" [same for OR], can be used. But how do I do that in VBA? What happened if I have and IF statement were it goes something like this:

if (X>0) then
if(y>0) then
do something
end if
end if

I can truncate to something more easier

if(X>0 "and" y>0) then
do something
end if

I know you can do it with Java where "&&" can be used for "AND"

Thanks

Phil

Hello all,

I have a problem with vlookup, some of the items in the lookup array are not unique and vlookup gets confused with multiple matches,

however if I can use a combination of 1st and second columns as the criterion then it would result in a unique match.

Problem is I don't know how to acheive this in vba (but I can with the worksheet formulas).

Any advice? (attached is a sample worksheet)

Hey all,

let's say I have something like this:

        With WorksheetFunction
           SubstString = Trim(.Substitute(ActiveSheet.Cells(i, 2).Value, "EST OF", ""))
           SubstString = Trim(.Substitute(SubstString, "(TRUST)", ""))
           SubstString = Trim(.Substitute(SubstString, "(IND)", ""))
           Range("C" & i).Value = SubstString
        End With
And the list of things I need to substitute can grow. Is there a better way to do multiple substitutions like this in VBA?

Thanks for response.

Hello

Using the "Select Case", is there any way to use the like operator?

Ex:


	VB:
	
 ChartTitles() 
    Dim sht As Worksheet 
    Dim ChtTitle As String 
     
    For Each sht In ThisWorkbook.Worksheets 
        If TypeName(ActiveSheet) = "Chart" Then 
             
            Select Case ActiveChart.Name 
            Case 1 
                Like "Profit/Loss*" 'Error here, does not like "Like" or "Is Like"
                ChtTitle = "Profit/Loss" & " For " & sht.Name 
            Case 2 
                Like "Evaluation*" 
                ChtTitle = "Evaluation" & " For " & sht.Name 
            Case 3 
                Like "Expenses/Revenues*" 
                ChtTitle = "Expenses/Revenues" & " For " & sht.Name 
            End Select 
             
            With ActiveChart 
                .ChartTitle.Characters.Text = ChtTitle 
            End With 
        End If 
    Next sht 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you

Terry

Hello,
What is a function in VBA witch do something like key combination
SHIFT+END+("DownArrow" or "ArrowUP")??

I want to select only cells with something inside not empty. For
example:
I have
____A_____B____
1 | string | |
2 | string | |
3 | string | |
4 | string | |
...........................
40| string | |
41| | |
Cell A41 is empty
I want to select only cells from A1 to A40 , but I dont want to use
something like this (from
Cells A1 to Cells A40 Select) I want to find a function which will do
it automaticaly without
enetering number of cells like from A1 to A40 Select.
Do you know is there a special function in Excel.
If you write some text in column A like mentioned above and go with
cursor on cel A1 than
turn holdSHIFT+ END + DownArrow than it select cells witch text inside
from A1 to A40, how to do something like this in VBA??
Thank you for response

Hi all

I am trying to combine index and match in vba.

I have the formula that is working, could someone assist on how I can do this in VBA.

    'Selection.FormulaArray = _
       
"=INDEX(sheet2!R21C15:R41C15,MATCH(1,(sheet2!R21C12:R41C12=R[-4]C2)*(sheet2!R21C13:R41C13<=R17C[8])*(sheet2!R21C14:R41C14>=R17C[8]),0))"


This is the kind of thing I thought. This is probably miles off...

I want to return the value in r4

r1 needs to be exact match
r2 needs to be <= date
r3 needs to be >= date

Dim sh As Worksheet
Dim r1, r2, r3, r4
Set sh = Sheets("sheet1")
Set r1 = sh.Range("L21:L41")
Set r2 = sh.Range("M21:M41")
Set r3 = sh.Range("N21:N41")
Set r4 = sh.Range("O21:O41")

Dim lEmp, dDate
lEmp = "983467"
dDate = CDate("03/04/11")

Dim v

With Application.WorksheetFunction

  v = .Index(r4, .Match(1, (r1 = lEmp) * (r2 <= dDate) * (r3 >= dDate), 0))

End With

End Sub
Thanks

Hi all;

I use 'like' statements in vba fairly regularly- example:


	VB:
	
 "*Pass*" 
 'and then..
If passcheck = True Then 
    totalpasscount = totalpasscount + 1 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
i was wondering if anyone knew a way to add more than one condition to a "like" statement- such as...

passcheck = activecell like "Pass","P","*ass"

which would sure help cause then i wouldnt have to code the same cellcheck 3 times. anyone know if its possible?

Thanks

How can we set two criterias in autofilter property in vba? For example if there are two columns - departments and grade ... i want that all those rows that have department as Finance and Grade as Grade A should be filtered automatically in VBA Coding. Please reply asap.

I am using dictionaries and collections in VBA, and I have a problems
whenever there is an error and I have to stop the code execution I
loose the contents of these data structures. I come from a c++
background and to get around this sort of issue I would use persistent
data structures. Is there anything like this in VBA?

Thanks
Tom

Hi guys

The following like operator doesn't work.

I hope anyone can help me out.

If
ComboBox1.Value Like "Scanning *" Then
        msgbox "Scanning"
   End If
Regards

Hi All,

I have some VB which combines multiple workbooks together in one worksheet called data, however, for some reason this code is failing to paste all the data in Column A and as a result the next section of data copies over the previous line.
I hope this makes sense, if not try the code below - I'm using 2007.

Sub combine()
 'This will copy data from all sheets of the selected workbooks
 'To a sheet  named 'Data' in the sheet in which the macro is run from
 
Dim ObjWBSource As Workbook
Dim ObjWBTarget As Workbook
 
Dim ObjWSData As Worksheet
Dim ws As Worksheet
 
Dim pasterow As Long
 
mainsheetname = ActiveWorkbook.Name
 
 MsgBox ("Please select spreadsheets to combine")
filestoopen = Application.GetOpenFilename(MultiSelect:=True)
 
responseval = MsgBox("Do you want to leave the combined spreadsheets open?", vbYesNo)
 
Set ObjWBTarget = ActiveWorkbook
Set ObjWSData = Worksheets("Data")
 
 'open workbooks
For Each w In filestoopen
 
    Set ObjWBSource = Workbooks.Open(Filename:=w)
 
    'copysheetname = ActiveWorkbook.Name
 
    'copy and paste sheets
    For Each ws In ObjWBSource.Sheets
 
        pasterow = ObjWBTarget.Worksheets(ObjWSData.Name).UsedRange.Rows.Count
        ObjWBSource.Sheets(ws.Name).UsedRange.Copy (ObjWBTarget.Worksheets(ObjWSData.Name).Range("A" &
pasterow))
 
    Next
 
    If responseval = 7 Then
        Application.DisplayAlerts = False
        ObjWBSource.Close
        Application.DisplayAlerts = True
    End If
 
Next w
 
End Sub


Hi, when i try to perform some of operation in this code i get completly different result than i should get. Can someone help me please i dont know what Im doing wrong.
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim iRow As Long

Dim wss As Worksheet
Set wss = Worksheets("PK1")


lRow = wss.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    lPart = Me.cbProviders.ListIndex

If Trim(Me.cbProviders.Value) = "" Then
  Me.cbProviders.SetFocus
  MsgBox "Please enter provider name."
  Exit Sub
End If

If Trim(Me.cbProviders.Value) = "Choose" Then
  Me.cbProviders.SetFocus
  MsgBox "Please enter provider name."
  Exit Sub
End If

If Trim(Me.txtBillNo.Value) = "" Then
  Me.txtBillNo.SetFocus
  MsgBox "Please enter Bill No."
  Exit Sub
End If

With wss
  .Cells(lRow, 3).Value = Me.cbProviders.Value
  .Cells(lRow, 2).Value = Me.txtDate.Value
  .Cells(lRow, 4).Value = Me.txtBillNo
  .Cells(lRow, 5).Value = Me.txtRetail
  .Cells(lRow, 6).Value = Me.txtRetail - Me.txtRetail * 0.152542373
  .Cells(lRow, 7).Value = Me.txtWholesale
  .Cells(lRow, 8).Value = Me.txtWholesale - Me.txtWholesale * 0.152542373
  .Cells(lRow, 9).Value = Me.txtRetail - Me.txtRetail * 0.152542373 - Me.txtWholesale - Me.txtWholesale * 0.152542373 'this
is a problem. i get different result
  
End With


Me.txtBillNo.Value = ""
Me.cbProviders.Value = "Choose"
Me.txtRetail.Value = ""
Me.txtDate.Value = Now()
Me.txtWholesale = ""

End Sub


Hi xl gurus

Actually I know how to use SUMIF function both in excel spreadsheet and in VBA.
I would like to know how it was written.
For example I can create something like VLOOKUP in VBA. But I cannot find a way to write SUMIF2 code which will do the same thing.

I actually was looping through all cells, but as I found later, it's not a good idea, because when I selected whole range E:E, UDF was running almost 1 minute. But when we use built-in SUMIF function it makes everything in milliseconds. On the assumption of that, I think that built-in SUMIF code doesn't loop through all selected cells..

So How was this done???

Hi all,

I'm trying to use the Data>Validation feature in Excel on a range of cells. I want to allow only entries that starts with the letters "MUT" followed by whatever. The following user defined function in VBA is equivalent to the "like" operator in VBA.


	VB:
	
 
    ISLIKE = arg Like pattern 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So I tried Data>Validation>Custom and entered =ISLIKE(B3,"MUT*") in the formula box but I get a message as if Excel was trying to reach some named range...

Can anyone help?
Thanks

I have a worksheet that gets data from another workbook. I have put vba code to convert the sheet to a printable format


	VB:
	
 Workbook 
. 
. 
. 
wk.Activate 
ActiveWindow.View = xlPageBreakPreview 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This shows multiple pagebreaks within the same worksheet. How to combine all the pageviews in the worksheet into one single pageview? It does not matter how small the cell content looks. I just want to fit it into one pageview!

Is there an inbuilt function for it or do I have to code it? If I have to code it, does anyone have an existing code?

Many thanks!

Hi, I have a problem with the WHERE condition syntax in Excel VBA and I cant seem to solve it.

For example, I want to include multiple "OR" conditions in my WHERE clause in my Excel VBA code:


	VB:
	
Select DateOfBirth, Name 
From AnimalTable 
WHERE PlaceOfBirth = ' " & USA & " ' OR PlaceOfBirth = ' " & Brazil & " ' AND Age

I am wanting to use the following code to combine worksheets from
multiple files. However I would like to be able to select folder which
contains files in a more automated way that having to change the code
every time, and also copy all worksheets with links and formulas
removed. Any help on this is greatly appreciated as I have limited
code knowledge.

Option Explicit
> Sub Copy_them()
>
> Dim TargetWkbk As Workbook
> Dim mrgWkbk As Workbook
>
> Dim i As Long
> Dim Wks As Worksheet
> Dim fName As String
>
> Application.ScreenUpdating = False
> Set TargetWkbk = Workbooks.Add(1)
> ActiveSheet.Name = "dummy"
>
> With Application.FileSearch
> .NewSearch
> .LookIn = "c:Temp" 'folder to use
> .SearchSubFolders = False
> .Filename = "*.xls"
> .FileType = msoFileTypeExcelWorkbooks
> If .Execute() > 0 Then
> ' MsgBox "There were " & .FoundFiles.Count & " file(s) found."
> For i = 1 To .FoundFiles.Count
> Set mrgWkbk = Workbooks.Open(.FoundFiles(i))
> For Each Wks In ActiveWorkbook.Worksheets
> With TargetWkbk
> Wks.Copy after:=.Worksheets(.Worksheets.Count)
> End With
> Next Wks
> mrgWkbk.Close False
> Next i
>
> Application.DisplayAlerts = False
> TargetWkbk.Worksheets("dummy").Delete
> Application.DisplayAlerts = True
>
> fName = Application.GetSaveAsFilename _
> (fileFilter:="MS Excel Workbook (*.Xls), *.Xls")
>
> TargetWkbk.SaveAs Filename:=fName, FileFormat:=xlNormal, _
> Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> Else
> MsgBox "There were no files found."
> TargetWkbk.Close savechanges:=False
> End If
> End With
>
> Application.ScreenUpdating = True
> Application.EnableEvents = False
>
> End Sub

I would like to take the inverse of a matrix within my VBA code as part
of a sequence of operations. Something like the following:

Dim vMtxA As Variant, vMtxB As Variant, vMtxC() As Variant

vMtxA = Range("B4:E7")
vMtxB = Application.MInverse(vMtxA)

OR

ReDim vMtxC(4,4)

vMtxC = Application.MInverse(vMtxA)

I have not been able to find a way to do this. I would then multiply
the inverse by a RHS vector to get a solution vector that would be used
in subsequent calculations using MMult, etc. Also I have not figgured
out how to add two variant arrays??

vMtxD = vMtxA + vMtxB

I am having trouble figuring out how to write a SUMPRODUCT formula in VBA
that multiples an array of cells which I would like to reference in relative
R1C1 terms by an array of cells that I would like to reference in absolute
terms on another sheet. The first part of my formula is fine, but I want the
cells from the DI_NAM sheet to always refer to rows 5 to 9. The column must
be the same as the first array. If there is a way I could put a variable
into the reference, that would work, but I can't figure out how to do this.
I need to replicate this formula as part of a block of data that gets created
in a do loop.

Selection.FormulaR1C1 = "=SUMPRODUCT(R[-7]C:R[-3]C,DI_NAM!R[-17]C:R[-13]C)"

This formula gets input across 36 columns as I am working with 36 months of
data.

I am a novice at VBA, so if anyone knows how to do this, I would really
appreciate the help!

I am wanting to use the following code to combine worksheets from
multiple files. However I would like to be able to select folder which

contains files in a more automated way that having to change the code
every time, and also copy all worksheets with links and formulas
removed. Any help on this is greatly appreciated as I have limited
code knowledge.

Sub Copy_them()

> Dim TargetWkbk As Workbook
> Dim mrgWkbk As Workbook

> Dim i As Long
> Dim Wks As Worksheet
> Dim fName As String

> Application.ScreenUpdating = False
> Set TargetWkbk = Workbooks.Add(1)
> ActiveSheet.Name = "dummy"

> With Application.FileSearch
> .NewSearch
> .LookIn = "c:Temp" 'folder to use
> .SearchSubFolders = False
> .Filename = "*.xls"
> .FileType = msoFileTypeExcelWorkbooks
> If .Execute() > 0 Then
> ' MsgBox "There were " & .FoundFiles.Count & " file(s) found."
> For i = 1 To .FoundFiles.Count
> Set mrgWkbk = Workbooks.Open(.FoundFiles(i))
> For Each Wks In ActiveWorkbook.Worksheets
> With TargetWkbk
> Wks.Copy after:=.Worksheets(.Worksheets.Count)
> End With
> Next Wks
> mrgWkbk.Close False
> Next i

> Application.DisplayAlerts = False
> TargetWkbk.Worksheets("dummy").Delete
> Application.DisplayAlerts = True

> fName = Application.GetSaveAsFilename _
> (fileFilter:="MS Excel Workbook (*.Xls), *.Xls")

> TargetWkbk.SaveAs Filename:=fName, FileFormat:=xlNormal, _
> Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> Else
> MsgBox "There were no files found."
> TargetWkbk.Close savechanges:=False
> End If
> End With

> Application.ScreenUpdating = True
> Application.EnableEvents = False

> End Sub

Hi

I a total rookie into VBA-programming and hope someone can help me.

In my sheet I have multiple columns that I need to combine into one column:
- In this example (see attachment) I have 4 columns E:H I need to combine into column C.
- There will be data in the columns nearby the range I like to combine.
- I need to do this on different data sets where the number of columns (and rows) will vary, therefore it could be nice to have a field where I can give input on how many columns that should be combined - in this example cell E2.
- I don't want to have a function that can concatenate the columns.

I have not succeded to find a code that I can adapt for that purpose. I will be happy if there is someone who can help me.

Thanks in anvanced!

Br.
Casper The friendly VBA rookie ;-)

how do i average across each position of multiple arrays in vba

say i have two 2x2 arrays (in a spreadsheet populating range A1:D2)

6 3 and 2 3
1 5 1 2

like this in the spreadsheet

6323
1512

the average is

4 3
1 3.5

so the result im trying to display would be like this:

6 3 2 3 4 3
1 5 1 2 1 3.5

The real arrays i work with are usually about 10rows by 11 columns and ive usually got 3 or 4 arrays that need averaging (across each position).