VBA Vlookup Function

[mikeymay]

I am attempting to recreate the following Excel function which returns the expected result

=VLOOKUP(B8,Rates_Lookup,3,FALSE)

The code I am using to recreate this is

.Offset(lngCount, intLoans * 2) = Application.WorksheetFunction.VLookup(dtStart + lngCount, rngRatesLookup, 3, False)

but I get a 'Unable to get the Vlookup property of the WorksheetFunctionclass'

In the Excel formula B8 = 10 Jun 19 in a date format
In the VBA code dtStart + lngCount returns the same in a date format (dtStart is declared as a date variable)

In the Excel formula 'Rates_Lookup' is the correct range
In the VBA code rngRatesLookup is based on the same range

I have used this VBA function before but I just can't see what is wrong with the VBA version.


TIA

 

[alansidman]

This may be helpful

http://www.cpearson.com/excel/CallingWorksheetFunctionsInVBA.aspx

 

[DanteAmor]

The mistake is because you are not finding the date.
In the excel dates are stored as a number, so you can use the CDbl function to change the date to the excel number.


However, if the date does not exist, send an error.
To prevent the code from stopping by error, you must use On Error.

[COLOR=#008000]        [/COLOR][COLOR=#ff0000]On Error Resume Next[/COLOR][COLOR=#008000]
[/COLOR]        Dim res As Variant

        res = Application.WorksheetFunction.VLookup([COLOR=#0000ff]CDbl[/COLOR](dtStart + lngCount), rngRatesLookup, 3, False)
        If Err.Number = 0 Then
            .Offset(lngCount, intLoans * 2) = res
        Else
            MsgBox "date does not exists"
        End If

--------------------------------------------
Although it is not good practice to use the On Error statement. I recommend using the Find method


Note: The date format on your sheet should be in "dd/mm/yyyy" or "mm/dd/yyyy" depending on the format you use.
Try this:

        Dim f As Range
        Set f = rngRatesLookup.Find(dtStart + lngCount, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            .Offset(lngCount, intLoans * 2) = f.Offset(0, 2).Value
        Else
            MsgBox "Dont exists"
        End If