Lookup date in a table column

[AOB]

I have a generic function that I call throughout a project that allows me to search for a value in a column and return the corresponding value in another column.

Public Function GetValueFromTable(strSheetName As String, strTableName As String, strLookupColumnName As String, varLookupValue As Variant, strReturnColumnName As String) As Variant

    On Error GoTo ErrorHandler
    
    Dim sht As Worksheet
    Dim lso As ListObject
    Dim rngFound As Range
    Dim intLookupColumn As Integer
    Dim intReturnColumn As Integer
    Dim intLookupRow As Integer
    Dim result As Variant

    Set sht = ThisWorkbook.Sheets(strSheetName)
    Set lso = sht.ListObjects(strTableName)
    
    With lso
        ' Find the column to lookup
        On Error Resume Next
        Set rngFound = lso.HeaderRowRange.Find(strLookupColumnName, LookAt:=xlWhole)
        If Not rngFound Is Nothing Then
            intLookupColumn = rngFound.Column
            ' Find the column to return
            Set rngFound = lso.HeaderRowRange.Find(strReturnColumnName, LookAt:=xlWhole)
            If Not rngFound Is Nothing Then
                intReturnColumn = rngFound.Column
                ' Find the value in the lookup column
                Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(varLookupValue, LookAt:=xlWhole)
                If Not rngFound Is Nothing Then
                    intLookupRow = rngFound.Row
                    result = Intersect(lso.ListColumns(intReturnColumn).DataBodyRange, lso.ListRows(intLookupRow - .HeaderRowRange.Row).Range).Value
                End If
            End If
        End If
  
    End With

Exit_GetValueFromTable:
    On Error Resume Next
    GetValueFromTable = result
    Set lso = Nothing
    Set rngFound = Nothing
    Set sht = Nothing
    Exit Function

ErrorHandler:
    result = 0
    Resume Exit_GetValueFromTable
    
End Function

Currently I only use this function throughout the project to look up strings but I now need to adapt it to handle dates as well. I'm testing it with a date that I know exists in a table (by inspection, both visually in the table itself and examining the Item in a watch in the VBE) but the .Find always returns nothing. Both the dates in the table and the date sent as parameter to the function are date-only (there is no time element)

Here are the various ways I've attempted to "hook" onto the qualifying range; all of them fail (i.e. return Nothing)...

Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(varLookupValue, LookAt:=xlWhole)
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(CDate(varLookupValue), LookAt:=xlWhole)
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(CDbl(varLookupValue), LookAt:=xlWhole)
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(CLng(varLookupValue), LookAt:=xlWhole)
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(DateValue(varLookupValue), LookAt:=xlWhole)
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(Format(varLookupValue, "m/d/yyyy"), LookAt:=xlWhole)
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(varLookupValue, LookAt:=xlWhole, LookIn:=xlFormulas)
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(CDate(varLookupValue), LookAt:=xlWhole, LookIn:=xlFormulas)
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(CDbl(varLookupValue), LookAt:=xlWhole, LookIn:=xlFormulas)
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(CLng(varLookupValue), LookAt:=xlWhole, LookIn:=xlFormulas)
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(DateValue(varLookupValue), LookAt:=xlWhole, LookIn:=xlFormulas)
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(Format(varLookupValue, "m/d/yyyy"), LookAt:=xlWhole, LookIn:=xlFormulas)

I know .Find'ing dates is meant to be "tricky" but I can't figure out why none of these methods work / how I need to adjust my function to be able to handle them?

 

[Micron]

Perhaps try putting a break point somewhere and in immediate window, enter ?TypeName(varVariant) and hit return, or after

' Find the value in the lookup column

add the following:
Debug.Print "Data type for " & varLookupValue & " is:" & TypeName(varLookupValue)

The point of that is to see what your data type is in case you're trying to find or coerce values that are not what you think they are.

I'm afraid I don't see the point in an error handler that doesn't report what the error is. If you reported it, you might already know what the issue is without doing the above, so that's another option. If all you want to do is return 0 after an error, you don't need the result variable when you can just set the function return value to 0?

 

[Micron]

Error in post, too late to correct it. ?TypeName(varVariant) should have been ?TypeName(varLookupValue)

 

[AOB]

The error handler is more comprehensive than what I posted, there's a whole logging function that it fires, I just didn't want to put more code than was necessary into the post.

In any case, there is no error to report anyway; error handling is switched off at that point (so if the function can't find either of the columns or the value, it doesn't bug out but just returns the zero) I just have a standard ErrorHandler structure that I apply to all subs and functions.

Added your line and here is the output (I already had the same information by debugging in the Watch window; the variant is, as expected, a Date variable)

Data type for 21/08/2022 is:Date

 

[Micron]

Sorry that didn't help at all. I don't have much else to offer except maybe to say make sure it's not something else in your line statement, such as an object issue (e.g. lso is empty) or a variable isn't valid. My guess is that you've covered that already.

If error handling is turned off, I'm afraid I don't see how you know that there isn't an error that could be reported which might clue you in to what the problem is. Errors that might be raised but don't apply to your issue could easily be ignored in the handler, but allowing one to be raised that doesn't apply might tell you something.

 

[AOB]

Switched the error handling back on (removed the On Error Resume Next line) - no errors thrown.

rngFound is just Nothing each time I try to assign it via lso.ListColumns(intLookupColumn).DataBodyRange.Find(varLookupValue, LookAt:=xlWhole) or any of the variations.

It just can't see the date, even though it is there

 

[AOB]

Sigh - figured it out. Long story short, the way Excel handles dates in VBA versus the way Excel handles dates in worksheets, is such that the Range.Find method is problematic.

Far better discussion on the problem than I could ever provide : Range.Find on a Date That is a Formula

Basically, to get around the problem, you take your date parameter (the date you want to look up), then convert it to a string, formatted it in exactly the same way as the column you want to look it up in.

Then Range.Find works by searching using string comparison on the values :

' Find the value in the lookup column
Dim strLookupValue As String
If TypeName(varLookupValue) = "Date" Then
    strLookupValue = CStr(Format(varLookupValue, lso.ListColumns(intLookupColumn).DataBodyRange.NumberFormat))
Else
    strLookupValue = CStr(varLookupValue)
End If
Set rngFound = lso.ListColumns(intLookupColumn).DataBodyRange.Find(strLookupValue, LookAt:=xlWhole, LookIn:=xlValues)