How can I vlookup the max value in VBA?

Sarah Whitlam 0
Viewed 43

I am very new to VBA and have to use it to build a form which has an option to pre-populate with your previous submission, the idea being that someone could submit the form (an assessment) every three months and review their progress over time, and by pre-populating with previous submission they don't have to start from scratch each time.

For this to work, I believe I would need to use a vlookup to look through the submission dates and return the values that correspond with the most recent (or max) date.

I have written this so far, but I receive the error: Run-time error '1004': Method 'VLookup' of object 'WorksheetFunction' failed.

    Sub vlookup1()

    Set myrange = Range("data_table")

    Name = Application.WorksheetFunction.Max(Range("date_range"))

    answer.Value = Application.WorksheetFunction.vlookup(Name, myrange, 4, False)

I would really appreciate some help identifying what I am doing wrong or if it's not possible to do this or there might be a better way to do it. Thank you in advance!

excel-formula vlookup vba excel
1 Answers

By dropping the WorksheetFunction when calling Vlookup you can avoid triggering a run-time error in the event of no match being found.

This works fine for me:

Sub Tester()

    Dim maxDt, myrange As Range, result
    
    Set myrange = Range("data_table")

    maxDt = Application.WorksheetFunction.Max(Range("date_range"))
    Debug.Print "Max date:", Format(maxDt, "yyyy-mm-dd")
    
    result = Application.VLookup(maxDt, myrange, 4, False) 'no `WorksheetFunction`
    If Not IsError(result) Then
        Debug.Print result
    Else
        Debug.Print "No match!"
    End If
    
End Sub
useryB020
Related