Simplifing and Index + match function use

Question

I am using the INDEX + MATCH excel function to look for an specific data on that named range. As my "MATCH" column is the second column (column "B"), I need to use the OFFSET function on that.

An excel example formula is this :

=INDEX(Macro;MATCH($F$29;OFFSET(Macro;0;1;;1);0);MATCH(J$2;Macro!$2:$2;0)))

So, to avoid having that function on all over my spreadsheet, I want to create a function to return such data.

I am trying to create the INDEX + MATCH on VBA and it works, by as soon as a try to use the named range (plus the OFFSET), I get some errors.

This gives me error :

DADOSMACRO = Application.WorksheetFunction.Match(Range("DataIWant"), Range("Macro").Offset(, 1), 0)"

This works :

"DADOSMACRO = Application.WorksheetFunction.Match(Range("DataIWant"), Range("Macro!A8:A37").Offset(, 1), 0)"

Full function:

Public Function DADOSMACRO() As String 
    DADOSMACRO = Application.WorksheetFunction.Match(Range("$F$29"), _
                                      Range("Macro").Offset(, 1), 0) 
End Function

As I sad I am trying to convert an existing excel formula into a VBA code. The code so far is "Public Function DADOSMACRO() As String DADOSMACRO = Application.WorksheetFunction.Match(Range("$F$29"), Range("Macro").Offset(, 1), 0) End Function " — FábioRB
"I get some errors" is not a very useful description of what you're seeing. — Tim Williams
Range("$F$29") without a specific worksheet will always default to the ActiveSheet, so you may get errors or the wrong result if a different sheet is active. — Tim Williams
But named ranges are usable across all workbook. If I run this VBA code, it works perfectly an set my range as Italic (Range("Macro").Font.Italic = TRUE) — FábioRB

FábioRB FábioRB · Accepted Answer · 2020-12-18T03:27:03

Thanks for everybody who tried to help. I finally could solve it with some help from Microsoft support.

The problem is not regarding using named ranges. The problem is that the named range is a range of whole rows, so the offset VBA function does not work.

I finally made it work replacing the offset by Application.WorksheetFunction.Match(Range("CELL_I_WANT_FIND"), Range("NAMEDRANGE").Columns(2), 0)

So the match vba code could find the data on column "B"

Simplifing and Index + match function use

1 Answers