vba code for vlookup

[ganeshm]

Hi excel experts,

Is there a vba code for the following vlookup formula?

IFERROR(VLOOKUP(A2,[Book1.xlsx]Sheet1!$A$2:$C$8,3,FALSE), "").

Regards,
ganeshm

 

[Abhijeet R. Joshi]

Hi Ganesh,

You can use this formula as it is in VBA

Range("A1").formula="=IFERROR(VLOOKUP(A2,[Book1.xlsx]Sheet1!$A$2:$C$8,3,FALSE), "")"

 

[SirJB7]

Hi, Abhijeet R. Joshi!
I updated your formula since embedded quotes should be double:
Range("A1").formula="=IFERROR(VLOOKUP(A2,[Book1.xlsx]Sheet1!$A$2:$C$8,3,FALSE), """")"
Regards!

Hi, gansehm!
Just in case you're looking after how to do that by code you could use the Application.WorksheetFunction.Vlookup method:

Something = Application.WorksheetFunction.VLookup(arg1, arg2, arg3[, arg4])

Same parameters as Excel function. Different notation.
Regards!

 

[ganeshm]

thank you sirJb7.

 

[SirJB7]

Hi, ganeshm!
Hope you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!

 

[Debraj]

Just a quick note..

Most of the Application.Worksheet functions can be directly called from its parent member.. Application.

Something = Application.WorksheetFunction.VLookup(arg1, arg2, arg3[, arg4])

will work approx same as

Something = Application.VLookup(arg1, arg2, arg3[, arg4])

however, error handling mechanism is much better in case of Application.Member

Check here for more detail..
http://www.cpearson.com/Excel/CallingWorksheetFunctionsInVBA.aspx

 

[SirJB7]

@Debraj(ex-Roy)
Hi!
Nice tip, thanks, I didn't know it.
Regards!
PS: So it was because of it that the other day that damned code worked...

 

[Marc L]

Hi SirJB7 !

You must have noticed that I never use WorksheetFunction but only Application
in all my codes like this one, this special one or this more classic one among many others !

Sometimes instead of Application I use Evaluate …

Regards !​

 

[SirJB7]

@Marc L
Hi, buddy!
As you well said, I must have noticed, but I assume that I've always read faster and gone thru that parts without realizing the lack of the .WorksheetFunction chunk.
It's nice learning things every day.
Regards!