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

[Guest]

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

 

[Don Guillett]

in the vbe>type vlookup and touch the enter key. HELP will appear

 

[Guest]

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

 

[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".

 

[Vasant Nanavati]

For example:

WorksheetFunction.VLookup(Range("A1"), Range("B1100"), 4, False)

 

[Don Guillett]

try. The trick is to use the range designations

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

 

[Don Guillett]

TYPO
a2:c22

 

[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.