How do I use Excel Built-In Functions in Code?

S

Steve Haack

I would like to be able to call some of the excel built-in functions
(specfically VLOOKUP) in some code. Can someone point me to an example of how
to do this?

Thanks,
Steve
 
S

Steve Haack

Don,
Thanks for the quick response. I tried that and not help came up. I've
noticed that I don't get the interactive help before on things. Is there a
way to turn that on and off (perhaps I inadvertantly turned it off and didn't
know it). Also, does it matter where the code is being put? I am trying to
put it on a worksheet object using a Change event.

Thanks again,
Steve
 
J

JulieD

Hi Steve

there's a problem with VBA Help and looking up functions directly.

In the "ask a question box" type "worksheet functions" and you'll see a help
entry on "Using Microsoft Excel Worksheet Functions in Visual Basic" if you
click on there it will give you details and also provide another link to
"List of Worksheet Functions Available to Visual Basic".
 
V

Vasant Nanavati

For example:

WorksheetFunction.VLookup(Range("A1"), Range("B1:D100"), 4, False)
 
D

Don Guillett

try. The trick is to use the range designations

x.value=application.vlookup(range("a1"),range("c2:c22"),2,0)
 
D

Dave Peterson

I like this style:

dim res as variant 'could return an error.

res = application.vlookup(worksheets("sheet1").range("a1").value, _
worksheets("sheet2").range("a:b"), 2, false)

if iserror(res) then
'it would have returned an #n/a error on the worksheet
msgbox "N/A"
else
msgbox res
end if

By using the application.vlookup() syntax, I get a result that can be tested
later.
 
Top