Link to home
Create AccountLog in
Avatar of Gary Croxford
Gary CroxfordFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

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))
Avatar of Shums Faruk
Shums Faruk
Flag of India image

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

ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
If you get runtime error 1004, instead of using
WorksheetFunction.VLookup

Open in new window

use
Application.VLookup

Open in new window

Avatar of Gary Croxford

ASKER

Great, thank you for your help