Vlookup in VBA. How can I?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

WannaBeExceller - 19 Jan 2006 18:27 GMT

I have some sections in the sheet where I have named cells to be used for a
Vlookup. I was wondering if there is a way within my VBA code to use vlookup
and access those sections to get the data I want?

Reply to this Message

Dave Peterson - 19 Jan 2006 18:37 GMT

Yep.

dim res as variant
dim lookuprng as range
dim myVal as string 'or whatever

set lookuprng = worksheets("sheet99").range("a:e")
myVal = "Test"
res = application.vlookup(myval, lookuprng,3,false)
if iserror(res) then
'same as #n/a
msgbox "not found"
else
msgbox res
end if

> I have some sections in the sheet where I have named cells to be used for a
> Vlookup. I was wondering if there is a way within my VBA code to use vlookup
> and access those sections to get the data I want?

Signature

Dave Peterson

Reply to this Message

Leith Ross - 19 Jan 2006 18:39 GMT

Hello WannaBeExceller,

myValue = WorksheetFunction.VLookUp(Lookup_Vakue, Table_Array,
Col_Index_Num, [Range_lookup])

Sincerely,
Leith Ross

Signature

Leith Ross

Reply to this Message

WannaBeExceller - 19 Jan 2006 19:28 GMT

I tried this but it is having a problem with assigining values to the
myValue. Could you explain how it is working please. I do not understand the
workings of Table_Array and range_lookup? Thanks in advance.

> Hello WannaBeExceller,
>
[quoted text clipped - 3 lines]
> Sincerely,
> Leith Ross

Reply to this Message

Leith Ross - 19 Jan 2006 19:35 GMT

Hello WannaBeExceller,

Those are the names of the arguments that the function requires. See
Dave's example and you will see what the arguments are and how they are
used.

Sincerely,
Leith Ross

Signature

Leith Ross

Reply to this Message

WannaBeExceller - 19 Jan 2006 19:56 GMT

Thank you very much Leith. Two thumbs way up!

> Hello WannaBeExceller,
>
[quoted text clipped - 4 lines]
> Sincerely,
> Leith Ross

Reply to this Message