Q:
Vlookup Worksheet Function
I develop and maintain pricing models that involve a lot of looping through cells and applying assumptions. For some reason I never seem to be able to get the vlookup worksheet function to work when looping through cells. It either says 'application-defined or object-defined error' or more frequently I get something along the lines of 'unable to get the property of the worksheet function class'. I always end up having VBA insert formulas and then copy and paste special values over the formulas. Because I stick to the basics with my code I can usually get away with not defining my data types…is this the problem? Does it matter if I am looking up data from a range vs. an array?
ASKED:
Jul 11 2009 0:21 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
1. In VBA you can't call spreadsheet functions directly. You have to use the Application.Worksheetfunction object to access it.
2. The vlookup function can sometimes simply take values, but on other occasions it definitely expects a range. You use the Range function to apply this.
e.g.
answer = Application.WorksheetFunction.Vlookup(x, Range("A1:A100"), 2 false)
or
answer = Application.WorksheetFunction.Vlookup(x, Range("named_range"), 2 false)
or
txtRange = "named_range"
answer = Application.WorksheetFunction.Vlookup(x, Range(txtRange), 2 false)
which gives you control over shich lookup table you access.
Can't comment further without samples of your code.
