VLOOKUP not working in VBA

  • Thread starter Thread starter DogLover
  • Start date Start date
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)
 
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
 
Back
Top