CODEWITHSUNDEEP
vbaexcel
aNich157
aNich157

Reputation: 13

VBA Nested Index Match syntax error

I'm having trouble converting a nested index/match formula to VBA. The formula works in Excel but in VBA I get "Compile Error: Expected: expression"

Context

I'm looking to make a Userform where the user will select a state then they are told which of three suppliers is the primary, secondary, or tertiary choice based on their choice of state. The idea is that the state, cell G2 in the formula below, will be passed to the formula through the Userform. Again, the formula works fine in Excel. 

=INDEX(B1:D1,MATCH(G3,INDEX(B3:D23,MATCH(G2,A3:A23,0),),0))

Sample Data

Below is the code I have in VBA. Below that is a screenshot of the error I'm getting. 

Sub LookUpTest()

Dim output As String

output = Application.WorksheetFunction.Index(Range("B1:D1"), 
Application.WorksheetFunction.Match(Range("G3"), 
Application.WorksheetFunction.Index(Range("B3:D23"), 
Application.WorksheetFunction.Match(Range("G2"), (Range("A3:A23")),0),),0))

Range("G8").Value = output

End Sub

Debug error

I get why the debugger is throwing up an error, as you can see here the Row argument is left blank but again the formula still works in Excel.

What am I missing on this for the VBA side / how can I change my code to get it to work? And can anyone explain to me why this works in Excel in the first place? I found the formula online and changed it to fit my use case but to be honest I don't understand why it works missing the Row argument.

Upvotes: 0

Views: 163

Answers (1)

learnAsWeGo
learnAsWeGo

Reputation: 2282

Think that this will work:

Sub LookUpTest()

    Dim output As String

    With Application.WorksheetFunction
        output = .Index(Range("B1:D1"), .Match(Range("G3"), .Index(Range("B3:D23"), .Match(Range("G2"), Range("A3:A23"), 0)), 0))
    End With

    Range("G8").Value = output
End Sub

Upvotes: 1

Related Questions