Use Max Function in VBA

**maacmaac** · 01-03-2012, 06:09 PM

I am trying to use the following line of code to obtain the highest value in column L and keep getting an error. Tried using "worksheetfunction", "application.worksheetfunction", etc.


newHHNumber = Application.Max(Range("L2:L" & LastRow)) + 1

**Jack in the UK** · 01-03-2012, 06:31 PM

This shoudl do it - edit as required

Option Explicit
 
Sub jiuk_TEST_myMAX_FUNCTION()

Dim myAnswer_MAX As Long
myAnswer_MAX = WorksheetFunction.Max(Range("l1:l5000"))
    
MsgBox myAnswer_MAX
theEND_jiuk_TEST_myMAX_FUNCTION:
'// jiuk - do something N/A
Exit Sub

End Sub