WorksheetFunction.VLookup not working

[azizrasul]

I get Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class on the VLookup line. Column H is formatted as date.

    Dim intRow As Integer
    Dim dteMax As Date

    intRow = Worksheets("Customer Product Export").Range("L" & Rows.Count).End(xlUp).Row
    dteMax = Application.WorksheetFunction.Max(Sheets("Customer Product Export").Range("H2:H" & intRow))
    UpdateMembershipPlan = Application.WorksheetFunction.VLookup(dteMax, Sheets("Current").Range("H2:L" & intRow), 5, False)

 

[RoryA]

Try:

UpdateMembershipPlan = Application.WorksheetFunction.VLookup(CDbl(dteMax), Sheets("Current").Range("H2:L" & intRow), 5, False)

 

[azizrasul]

I'm afraid that didn't work. I changed the code to:

Public Function UpdateMembershipPlan() As String
    
    Dim intRow As Integer
    Dim dteMax As Long

    Sheets("Customer Product Export").Select
    intRow = Worksheets("Customer Product Export").Range("L" & Rows.Count).End(xlUp).Row
    Columns("H:H").Select
    Selection.NumberFormat = "0"
    dteMax = Application.WorksheetFunction.Max(Sheets("Customer Product Export").Range("H2:F" & intRow))
    UpdateMembershipPlan = Application.WorksheetFunction.VLookup(CDbl(dteMax), Sheets("Current").Range("H2:L" & intRow), 5, False)
'    UpdateMembershipPlan = "http://hsfsp/sites/hsfs/docs/Plans/Tailored/COOPERVISIONMP-APRIL2018v1.pdf"
    
End Function

 

[RoryA]

Then your lookup value is not being found.

 

[azizrasul]

Silly me, I was using an incorrect sheet name. The CDbl function helped. Many thanks.