Index & Match

[Forestq]

Team,

I need to use Index & Match function in Excel 2003.
My macro should:
- open two InputBox: first for looking columns, second for looking rows; both value are variables like col_1 and row_1

- I have set up range for:
match_array - where I'm looking for a value
match_1 - where I'm looking for a columns
match_2 - where I'm looking for a rows.

Then I tried to use func (normaly in excel file is working fine) to find a value and display the value.

This is my code:

Dim match_1 As Range
Dim match_2 As Range
Dim match_array As Range
 
Dim row_1 As String
Dim col_1 As String
Dim my_func As Long
 
Set match_array = Worksheets("Sheet1").Range("B2:IV65536")
Set match_1 = Worksheets("Sheet1").Range("1:1")
Set match_2 = Worksheets("Sheet1").Range("A:A")
 
col_1 = InputBox("Set Nr column")
row_1 = InputBox("Set Nr rows")
 
my_func = WorksheetFunction.Index(match_array, WorksheetFunction.match(row_1, match_2, 0), WorksheetFunction.match(col_1, match_1, 0))
 
MsgBox "Looking value is " & my_func & " ."

I have error in line with my_func:
"Run-time error '1004' - Unable to get the Match property of the WorksheetFunction class".

Please help me resole this issue.

regards,
PvK

 

[Sektor]

Try Application.Index

 

[Forestq]

I change to:

my_func = Application.Index(match_array, WorksheetFunction.match(row_1, match_2, 0), WorksheetFunction.match(col_1, match_1, 0))

but still I have this some error.
I tried also:

my_func = Application.Index(match_array, Application.WorksheetFunction.match(row_1, match_2, 0), Application.WorksheetFunction.match(col_1, match_1, 0))

and

my_func = Application.Index(match_array, Application.match(row_1, match_2, 0), Application.match(col_1, match_1, 0))

and still I had error.