Hi, I'm trying to create a vlookup function in Excel 2010 (32-bit) to retrieve values from a data table named ForeSee. The table's left most column is a range of dates from 12/1/2015 to 12/31/2017 in ascending order. The value of the first cell is 12/1/2015, so it does not include the header row.

As I intend to use the VbaVlookup() results in another function, I've created a subroutine to test the function.

The problem is that I get "Error 2042" (N/A#) as a result when I run the test, even though I debug it, and I can see the range correctly defined.

Public Sub Test()
Dim strRange As String
Dim intOffset As Integer
Dim varValue As Variant

strRange = "ForeSee"
varValue = "07/03/2016"
intOffset = 2

Debug.Print VbaVLookup(varValue, strRange, intOffset)

End Sub

Public Function VbaVLookup(ByVal varValue As Variant, ByVal strRange As String, ByVal intOffset As Integer) As Variant
Dim strSheet As String
Dim rngTable As Range

On Error Resume Next

strSheet = Range(strRange).Worksheet.Name
Set rngTable = Worksheets(strSheet).Range(strRange)

'checks to see if first cell in table is a date and converts the Lookupvalue to a date value
With rngTable
If IsDate(Worksheets(strSheet).Cells(.Row, .Column).Value) And IsDate(varValue) Then
VbaVLookup = Application.VLookup(CDate(varValue), rngTable, intOffset, False)
Else
VbaVLookup = Application.VLookup(varValue, rngTable, intOffset, False)
End If

Debug.Print "Row - " & .Row & " Col - " & .Column & " Rows - " & .Rows.Count & " Cols - " & .Columns.Count


End With

On Error GoTo 0

Exit_Routine:

End Function