VBA Vlookup Function

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,421
I am attempting to recreate the following Excel function which returns the expected result
Code:
=VLOOKUP(B8,Rates_Lookup,3,FALSE)
The code I am using to recreate this is
Code:
.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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,153
Office Version
2007
Platform
Windows
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.


Code:
[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:
Code:
        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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,262
Messages
5,485,740
Members
407,511
Latest member
Arunabh

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top