| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF/AND worksheet function | Excel Discussion (Misc queries) | |||
| Need VBA for a worksheet function | Excel Discussion (Misc queries) | |||
| worksheet function | Excel Worksheet Functions | |||
| Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
| Can the offset worksheet function reference another worksheet | Excel Worksheet Functions | |||
![Dthmtlgod[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
