Convert Index Match Array Formula Into VBA

Question

=INDEX(Engine!$AL$12:$AL$17,MATCH($F21,{40;39.9;19.9;14.9;2.9;1},-1))

. I am trying convert the above formula into VBA It is an index match formula to the table below. If the value of $F21 is 21 then the formula gives a result of 2. In VBA if variable wbcL is 21 then the variable wbcLscore should be 2.

I have tried VBA line below but it is obviously wrong and gives the error Invalid character at {

wbcLscore = Application.WorksheetFunction.Index(wseng.Range("AL12:AL17"), Application.WorksheetFunction.Match(wbcL,{40;39.9;19.9;14.9;2.9;1}, -1))

Maybe wbcLscore = Evaluate("=INDEX(Engine!$AL$12:$AL$17,MATCH($F21,{40;39.9;19.9;14.9;2.9;1},-1))")? Not tested — Foxfire And Burns And Burns
@FoxfireAndBurnsAndBurns Thanks that works but returns an incorrect result of 4. But even if it produced a correct result it would not help as the value of my variable, wbcL changes and is not always in cell F21. — Anthony
How can that return 4, the value in the first column would surely be 39.9? I suppose you should clarify what is in AL12:AL17. — SJR

Evil Blue Monkey Evil Blue Monkey · Accepted Answer · 2021-02-18T11:31:22

Try something like this:

Sub SubwbcLscore()
    
    'Stuff i needed to make it work.
    Dim wseng As Worksheet
    Dim wbcL As Double
    Dim wbcLscore As String
    Set wseng = ActiveSheet
    wbcL = 40
    
    'Declaration.
    Dim Arr(1 To 6) As Double
    
    'Setting Arr.
    Arr(1) = 40
    Arr(2) = 39.9
    Arr(3) = 19.9
    Arr(4) = 14.9
    Arr(5) = 2.9
    Arr(6) = 1
    
    'Setting wbcLscore.
    wbcLscore = Application.WorksheetFunction.Index(wseng.Range("AL12:AL17"), Application.WorksheetFunction.Match(wbcL, Arr, -1))
    
End Sub

The formula is the same, it just uses an array instead of your {40;39.9;19.9;14.9;2.9;1}.

If you don't want an extra array, something like this might work as well:

wbcLscore = Application.ExecuteExcel4Macro("INDEX(Engine!R12C38:R17C38,MATCH(Foglio1!R21C6,{40;39.9;19.9;14.9;2.9;1},-1))")

Mind the cell addresses: they have to be in R1C1 format (as specified in the Application.ExecuteExcel4Macro page). Then again: an array would be easier to edit.

Convert Index Match Array Formula Into VBA

1 Answers