AOB
Well-known Member
- Joined
- Dec 15, 2010
- Messages
- 660
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
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.
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)...
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?
VBA Code:
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)...
VBA Code:
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?