Results 1 to 3 of 3

Thread: VBA Vlookup Function
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,404
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Vlookup Function

    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

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,938
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Vlookup Function

    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,263
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Vlookup Function

    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:
    On Error Resume Next
            Dim res As Variant
    
            res = Application.WorksheetFunction.VLookup(CDbl(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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •