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?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 11, 2009 12:21 AM
UPDATED:
August 12, 2009 2:28 PM
Answer Wiki:
There are a couple of things possibly going on here.
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.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
