Using VLOOKUP in VBA
 

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

Using VLOOKUP in VBA
 

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

wrote in message
oups.com...
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

Using VLOOKUP in VBA
 

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)



--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


" wrote:

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

Using VLOOKUP in VBA
 

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

Mike F
"Martin Fishlock" wrote in message
...
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)



--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


" wrote:

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

Using VLOOKUP in VBA
 

On Jan 29, 7:33 pm, "Mike Fogleman"
wrote:
Good catch!! I didn't even notice trying to return the 5th column of a one
column range.

Mike F"Martin Fishlock" wrote in ...

Hi Chad:

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

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