VLOOKUP not working in VBA

[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)

 

[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

 

[smartin]

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

 

[DogLover]

These changes worked! Thank you both!!