CODEWITHSUNDEEP
excelvba
Helbert Gonzalez
Helbert Gonzalez

Reputation: 21

how to translate the function INDEX(MATCH...to VBA code

I am trying to use the index function and match to find a value in a table

In Excel I use the following function:

=INDEX('Mi Guia.xls'!STACKED;MATCH(I12;PUERTO;0);MATCH(I10;TARJETA;0))

but I don't know how to apply it on vba so I would appreciate your help

Upvotes: 2

Views: 146

Answers (2)

Tim Williams
Tim Williams

Reputation: 166126

Something like this (may need some slight adjustments since we can't see the definitions for your named ranges)

Dim m1, m2

m1 = Application.Match(Range("I12").Value,Range("PUERTO"), 0)
m2 = Application.Match(Range("I10").Value,Range("TARJETA"), 0)

if not iserror(m1) and not iserror(m2) then
    debug.print workbooks("Mi Guia.xls").Range("STACKED").cells(m1, m2).Value
end if

Upvotes: 2

Vityata
Vityata

Reputation: 43575

There are a few ways to do it. From the cleanest one:

To the dirtiest one:

  • put the formula in Excel and translate it to a string. Then concatenate the string and change the string, containing variables. This is what could be used. Select the cell with the formula and run this:
Public Sub PrintMeUsefulFormula()

    Dim selectedFormula  As String
    Dim parenthesis  As String

    parenthesis = """"

    selectedFormula = Selection.Formula
    selectedFormula = Replace(selectedFormula, """", """""")

    selectedFormula = parenthesis & selectedFormula & parenthesis
    Debug.Print selectedFormula

End Sub

Then take the printed string in the immediate window and refer it to a cell like this:

Worksheets(1).Range("A5").Formula = printedString

If the string looks like this: "=INDEX(A1:J5,MATCH(1,E1:E5),MATCH(3,A3:J3))" and you need to change the 3 of the second match with a variable a, then something like this works: 

ws.Range("A5").Formula = "=INDEX(A1:J5,MATCH(1,E1:E5),MATCH(" & a & ",A3:J3))"

Upvotes: 0

Related Questions