CODEWITHSUNDEEP
vbaexcelindexingmatch
frank
frank

Reputation: 3608

How to pass variables to an double match function in VBA

I have a bunch of rows and 25 columns in a worksheet, and need to find the value in the 4th column based on columns B and C using VBA. I am using a combination of index and multiple condition match functions.

I tried to follow along via https://www.mrexcel.com/forum/showthread.php?650832-VBA-Multiple-Criteria-Index-Match and pass an integer variable into vba array formula to no avail.

I made this macro which works:

Sub VariablesInArrayFormula()
SA = "Apples"
C1 = "Oranges"
Range("D27").Select
Selection.FormulaArray = "=index(A2:G27,match(1,(B2:B27=b4)*(C2:C27= c6),0),4)"

Range("E27").Select
Selection.FormulaArray = "=index(A2:G27,match(1,(B2:B27=""Apples"")*(C2:C27= ""Oranges""),0),4)"


f = Evaluate("index(A2:G27,match(1,(B2:B27=""Apples"")*(C2:C27= ""Oranges""),0),4)")

Range("G27").Select
Selection.FormulaArray = "=index(A2:G27,match(1,(B2:B27="" & SA & "")*(C2:C27= "" C1 ""),0),4)"

End Sub

I want to assign the value to a variable for future use.

When I assign it to D27, it works because the I refer to cell references b4 and c6.

Assigning it to cell E27 also works, but then I need to refer directly to Apples and Oranges, where as I would prefer to pass in a variables

assigning it to a variable f works when I pass in the words Apples and Oranges

when I attempt to pass a reference to Apples and Oranges (SA and C1 respectively), I receive a #N/A error.

Can anyone suggest a way that I can pass in the variables to this function.

NB I tried using worksheetfunction.index and worksheetfunction.match and kept receiving errors as well. Specifically, I tried:

gr4 = WorksheetFunction.Index(Range("A2:G27"), WorksheetFunction.Match(1, ((Range("B2:B27") = SA) * (Range("C2:C27") = C1)), 0), 4)

which returned a run time error #13: type mismatch.

Odd that using 2 matches failed, as when I use a single column to check with the match function worked

Sub vfhj()
SA = "Apples"
C1 = "Oranges"
gr3 = WorksheetFunction.Index(Range("C2:C27"), WorksheetFunction.Match(C1, Range("C2:C27"), 0))
End Sub

Upvotes: 1

Views: 1153

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

May be a bit quicker using memory arrays:

Sub VariablesInArrayFormula()
Dim SA As String
SA = "Apples"

Dim C1 As String
C1 = "Oranges"

With Worksheets("Sheet1") 'Change to your worksheet
    Dim DtaArray As Variant
    DtaArray = .Range("B2:D27").Value

    Dim i As Long
    For i = LBound(DtaArray, 1) To UBound(DtaArray, 1)
        Dim ans
        If DtaArray(i, 1) = SA And dtaaray(i, 2) = C1 Then
            ans = DtaArray(i, 3)
            Exit For
        End If
    Next i

    .Range("G1").Value = ans
End With


End Sub

Upvotes: 0

SJR
SJR

Reputation: 23081

If I understand correctly, I think your syntax was just off slightly - you omitted some ampersands and overlooked the rule about doubling up the quotes. Also no need to Select.

Range("G27").FormulaArray = "=index(A2:G27,match(1,(B2:B27=""" & SA & """)*(C2:C27=""" & C1 & """),0),4)"

Upvotes: 1

Related Questions