excelvbarangerowcell

Return first row number for max date

How do I write VBA to return the first row number found in column L for the max date in that column?

I'm trying this code and get

"Object variable or With block variable not set"

Sub FindMaxValRow()
Dim Rng As Range
Dim MaxCell As Range
Dim MaxVal As Long

Set Rng = Range("L1:L500")
MaxVal = WorksheetFunction.Max(Rng)
Set MaxCell = Rng.find(what:=MaxVal, LookIn:=xlValues)
MsgBox "Maximum value found at row " & MaxCell.Row
End Sub
Solution

  • Using formula it would be

    =MATCH(MAX(L:L),L:L,0)

    IF you want to calculate it in VBA then you can use the Evaluate method:

    evaluate("=MATCH(MAX(L:L),L:L,0)")