|
WorkSheet Function
I am trying to return a value (RES) using INDEX/MATCH in VBA.
Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3",MATCH(Range("81!F" & Z),Range("LOOKUP81!$A$1:$A$3),0,2))) I trying to find a match and return a value. The statement continues to error out. It works fine as a formula through the application =INDEX (LOOKUP81!$A$1:$B$3,MATCH($F5,LOOKUP81!$A$1:$A$3,0 ),2) Please assist. Thanks |
WorkSheet Function
Hi
there are some missing parenthesis. Try Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3"),MATCH(Range("81!F"& Z),Range("LOOKUP81!$A$1:$A$3"),0),2) -- Regards Frank Kabel Frankfurt, Germany Dthmtlgod wrote: I am trying to return a value (RES) using INDEX/MATCH in VBA. Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3",MATCH(Range("81!F" & Z),Range("LOOKUP81!$A$1:$A$3),0,2))) I trying to find a match and return a value. The statement continues to error out. It works fine as a formula through the application =INDEX (LOOKUP81!$A$1:$B$3,MATCH($F5,LOOKUP81!$A$1:$A$3,0 ),2) Please assist. Thanks |
WorkSheet Function
Thank Frank.
I changed it to the following Res = WorksheetFunction.Index(Range("LOOKUP81!$A$1:$B$3" ), WorksheetFunction.Match(Range("81!F" & Z), Range("LOOKUP81!$A$1:$A$3"), 0), 2) I added the WorksheetFunction.Match, now I am receiving a Run-time error '1004' Unable to get the Match property of the WorksheetFunction class ???? Am I missing something in the Match function? "Frank Kabel" wrote in message ... Hi there are some missing parenthesis. Try Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3"),MATCH(Range("81!F"& Z),Range("LOOKUP81!$A$1:$A$3"),0),2) -- Regards Frank Kabel Frankfurt, Germany Dthmtlgod wrote: I am trying to return a value (RES) using INDEX/MATCH in VBA. Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3",MATCH(Range("81!F" & Z),Range("LOOKUP81!$A$1:$A$3),0,2))) I trying to find a match and return a value. The statement continues to error out. It works fine as a formula through the application =INDEX (LOOKUP81!$A$1:$B$3,MATCH($F5,LOOKUP81!$A$1:$A$3,0 ),2) Please assist. Thanks |
WorkSheet Function
Hi
try with application.worksheetfunction Res = .Index(Range("LOOKUP81!$A$1:$B$3"),.Match(Range("L OOKUP81!F" & Z), _ Range("LOOKUP81!$A$1:$A$3"), 0),2) end with -- Regards Frank Kabel Frankfurt, Germany Dthmtlgod wrote: Thank Frank. I changed it to the following Res = WorksheetFunction.Index(Range("LOOKUP81!$A$1:$B$3" ), WorksheetFunction.Match(Range("81!F" & Z), Range("LOOKUP81!$A$1:$A$3"), 0), 2) I added the WorksheetFunction.Match, now I am receiving a Run-time error '1004' Unable to get the Match property of the WorksheetFunction class ???? Am I missing something in the Match function? "Frank Kabel" wrote in message ... Hi there are some missing parenthesis. Try Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3"),MATCH(Range("81!F"& Z),Range("LOOKUP81!$A$1:$A$3"),0),2) -- Regards Frank Kabel Frankfurt, Germany Dthmtlgod wrote: I am trying to return a value (RES) using INDEX/MATCH in VBA. Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3",MATCH(Range("81!F" & Z),Range("LOOKUP81!$A$1:$A$3),0,2))) I trying to find a match and return a value. The statement continues to error out. It works fine as a formula through the application =INDEX (LOOKUP81!$A$1:$B$3,MATCH($F5,LOOKUP81!$A$1:$A$3,0 ),2) Please assist. Thanks |
WorkSheet Function
Hi Frank.
I am still receiving the same error. I added the With Application.WorksheetFunction, etc. Run-time error '1004' Unable to get the Match property of the WorksheetFunction class Any ideas? "Frank Kabel" wrote in message ... Hi try with application.worksheetfunction Res = .Index(Range("LOOKUP81!$A$1:$B$3"),.Match(Range("L OOKUP81!F" & Z), _ Range("LOOKUP81!$A$1:$A$3"), 0),2) end with -- Regards Frank Kabel Frankfurt, Germany Dthmtlgod wrote: Thank Frank. I changed it to the following Res = WorksheetFunction.Index(Range("LOOKUP81!$A$1:$B$3" ), WorksheetFunction.Match(Range("81!F" & Z), Range("LOOKUP81!$A$1:$A$3"), 0), 2) I added the WorksheetFunction.Match, now I am receiving a Run-time error '1004' Unable to get the Match property of the WorksheetFunction class ???? Am I missing something in the Match function? "Frank Kabel" wrote in message ... Hi there are some missing parenthesis. Try Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3"),MATCH(Range("81!F"& Z),Range("LOOKUP81!$A$1:$A$3"),0),2) -- Regards Frank Kabel Frankfurt, Germany Dthmtlgod wrote: I am trying to return a value (RES) using INDEX/MATCH in VBA. Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3",MATCH(Range("81!F" & Z),Range("LOOKUP81!$A$1:$A$3),0,2))) I trying to find a match and return a value. The statement continues to error out. It works fine as a formula through the application =INDEX (LOOKUP81!$A$1:$B$3,MATCH($F5,LOOKUP81!$A$1:$A$3,0 ),2) Please assist. Thanks |
WorkSheet Function
Hi
are you sure the range names are correct 8e.g. the sheet names exist, etc.) -- Regards Frank Kabel Frankfurt, Germany "Dthmtlgod" schrieb im Newsbeitrag ... Hi Frank. I am still receiving the same error. I added the With Application.WorksheetFunction, etc. Run-time error '1004' Unable to get the Match property of the WorksheetFunction class Any ideas? "Frank Kabel" wrote in message ... Hi try with application.worksheetfunction Res = ..Index(Range("LOOKUP81!$A$1:$B$3"),.Match(Range(" LOOKUP81!F" & Z), _ Range("LOOKUP81!$A$1:$A$3"), 0),2) end with -- Regards Frank Kabel Frankfurt, Germany Dthmtlgod wrote: Thank Frank. I changed it to the following Res = WorksheetFunction.Index(Range("LOOKUP81!$A$1:$B$3" ), WorksheetFunction.Match(Range("81!F" & Z), Range("LOOKUP81!$A$1:$A$3"), 0), 2) I added the WorksheetFunction.Match, now I am receiving a Run-time error '1004' Unable to get the Match property of the WorksheetFunction class ???? Am I missing something in the Match function? "Frank Kabel" wrote in message ... Hi there are some missing parenthesis. Try Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3"),MATCH(Range("81!F"& Z),Range("LOOKUP81!$A$1:$A$3"),0),2) -- Regards Frank Kabel Frankfurt, Germany Dthmtlgod wrote: I am trying to return a value (RES) using INDEX/MATCH in VBA. Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3",MATCH(Range("81!F" & Z),Range("LOOKUP81!$A$1:$A$3),0,2))) I trying to find a match and return a value. The statement continues to error out. It works fine as a formula through the application =INDEX (LOOKUP81!$A$1:$B$3,MATCH($F5,LOOKUP81!$A$1:$A$3,0 ),2) Please assist. Thanks |
| All times are GMT +1. The time now is 06:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com