VLOOKUP not working in VBA

D

DogLover

I am trying to perform at lookup in a defined range name=DemoEndDate.
Res comes back as blank. Is this the correct format?? Also, does anyone
know if this is the correct date format?

YY = Year(Worksheets("RFJ").Range("N8"))
MM = Month(Worksheets("RFJ").Range("N8"))
SDate = DateSerial(YY, MM, 1)

Dim YY As String
Dim MM As String
Dim SDate As Date

Res = Application.WorksheetFunction.VLookup(SDate, Range("DemoOEndDate"), 2,
False)
 
D

Dave Peterson

If res is coming back as blank ("", right?), then I think your =vlookup() is
working fine. But the first match for that date has a cell that looks empty.

======
You're not sharing all your code--are you masking any problems with an "on error
resume next" line?

ps.

I'd use this version--with two important changes.

I'd use application.vlookup() instead of application.worksheetfunction.vlookup()
because of the way no matches are handled.

And I'd use clng(sdate). Dates can be funny things to find matches with.

dim Res as variant
....
Res = Application.VLookup(clng(SDate), Range("DemoOEndDate"), 2, False)

if iserror(res) then
'not found
else
msgbox res
end if
 
S

smartin

DogLover said:
I am trying to perform at lookup in a defined range name=DemoEndDate.
Res comes back as blank. Is this the correct format?? Also, does anyone
know if this is the correct date format?

YY = Year(Worksheets("RFJ").Range("N8"))
MM = Month(Worksheets("RFJ").Range("N8"))
SDate = DateSerial(YY, MM, 1)

Dim YY As String
Dim MM As String
Dim SDate As Date

Res = Application.WorksheetFunction.VLookup(SDate, Range("DemoOEndDate"), 2,
False)

You could try this modification:

Res = Application.WorksheetFunction.VLookup(CLng(SDate),
Range("DemoOEndDate"), 2, False)

See also my comments in this recent thread:

http://preview.tinyurl.com/y8rlfry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top