VLookup to Table with Dates

[rbeaslin]

I am trying to read from a table filled with dates and percentages. The trouble i am having is that when I do the application.vlookup it doesn't look up the value correctly when the range I select is in the table. I copied the values to a separate range on the worksheet (not in a table) and then the vlookup works as expected. Both the date in the table and in the range on the worksheet are formatted as short date.

Relevant items are below:
Dim RRR As Range
Set RRR = Sheets("data").Range("$a$2:$c$13")

iRate = Application.WorksheetFunction.VLookup(passvalue, RRR, 2)

VBA brings the date i'm looking for from a date-picker form as a date variable.

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
passvalue = DateClicked
Me.Hide
End Sub

In declarations for module:
Option Explicit

Public passvalue As date


I have re-Dimed the variables as strings and variant and the table vlookup still doesn't work. I am getting Error 1004 on exiting my error handler and i can see its not finding a match in the vlookup.

Any help you could lend would be greatly appreciated.

 

[rbeaslin]

As i continued to research this, I was able to find this site which explained my issue so well. Hopefully it will help others.

Using WorksheetFunction.VLookUp To Look Up A Date In VBA

VLOOKUP is exposed in VBA via the WorksheetFunction class and works much the same as it does when you use it in a worksheet, but one thing you have to be careful of is when you have a dat…

colinlegg.wordpress.com

Thanks!