Vlookup Worksheet Function

5 pts.
Tags:
Excel VBA
Microsoft Excel
VBA
Visual Basic for Applications
VLOOKUP 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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following