Using VLOOKUP in VBA

[ckramer7070]

Just need a quick clarification; I am trying to find a value (Job
Number) in the same row as the DueDate variable shown in the procedure
below. I can't get it to work, I was assuming it would find the
closest date if there were no matches. Is this correct?

dim rngdate as range
dim WS as worksheet

rngDate = WS.Application.WorksheetFunction.VLookup(DueDate, "G1:G500",
5, True)

I would like to search through column G for the DueDate, and return
the corresponding Job Number in column 5.

Thanks for any help,

Chad

 

[Mike Fogleman]

dim rngdate as range
dim WS as worksheet
Dim DueDate as String
Dim FoundRng AsRange

Set WS = Worksheets("Sheet1") 'or whatever sheet
Set rngDate = WS.Range("G1:G500")
FoundRng = Application..VLookup(DueDate, rngDate,5, True)

It is always better to set your ranges to a variable outside of Worksheet
Functions and use the varible within the function.
This is untested, so give it a try. Notice I declared your DueDate as a
String for VLookup to use.

Mike F

 

[Guest]

Hi Chad:

Vlookup needs the key to be on the left of the data.

As is:
'assume that DueDate is already a valid value

dim WS as worksheet
dim v as variant

set WS =activesheet
v = Application.WorksheetFunction.VLookup( _
DueDate, WS.range("G1:K500"), 5, True)


In your case:

dim WS as worksheet
dim v as variant

set WS =activesheet

v = Application.WorksheetFunction.Match( _
DueDate, WS.range("G1:G500"), 1)
if isna(v) then
msgbox "cannot match",vbokonly,"error"
exit sub
end if
v=WS.cells(v, 5)

 

[Mike Fogleman]

Good catch!! I didn't even notice trying to return the 5th column of a one
column range.

Mike F

 

[ckramer7070]

Good catch!! I didn't even notice trying to return the 5th column of a one
column range.

Thanks for the assistance, unfortunately I have not remembered the
golden rule of vlookup, always needs to be from the left. In this
worksheet all of the values that need to be searched through have to
be in the right most column. I was able to test both of your
suggestions and they work very well with the correct ranges. They will
always be useful for future projects.

Thanks again,

Chad