VBA VLOOKUP - "Run-time Error 1004: Unable to get the Vlookup property of the WorksheetFunction class."

Gary Croxford
Gary Croxford used Ask the Experts™
on
Thank you for looking at my question,

I have read through the answers already on EE regarding and not found what I need so, simply, how do I incorporate the statement below in VBA

=IF(ISERROR(VALUE($I$2)),VLOOKUP($I$2,'O:\Materials Data\VZ Interpreter\[VZOptions.xlsx]Material'!$A$1:$C$10,3,FALSE),VLOOKUP(VALUE($I$2),'O:\Materials Data\VZ Interpreter\[VZOptions.xlsx]Material'!$A$1:$C$10,3,FALSE))
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
You can try as below changing activecell:
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VALUE(R2C9)),VLOOKUP(R2C9,'O:\Materials Data\VZ Interpreter\[VZOptions.xlsx]Material'!R1C1:R10C3,3,FALSE),VLOOKUP(VALUE(R2C9),'O:\Materials Data\VZ Interpreter\[VZOptions.xlsx]Material'!R1C1:R10C3,3,FALSE))"

Open in new window

Top Expert 2016
Commented:
HI,

pls try
res = Evaluate("=IF(ISERROR(VALUE($I$2)),VLOOKUP($I$2,'O:\Materials Data\VZ Interpreter\[VZOptions.xlsx]Material'!$A$1:$C$10,3,FALSE),VLOOKUP(VALUE($I$2),'O:\Materials Data\VZ Interpreter\[VZOptions.xlsx]Material'!$A$1:$C$10,3,FALSE)) ")
If Not IsError(res) Then 'yourcode

Open in new window

Regards
If you get runtime error 1004, instead of using
WorksheetFunction.VLookup

Open in new window

use
Application.VLookup

Open in new window

Gary CroxfordOperations Support Analyst

Author

Commented:
Great, thank you for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial