VBA to Find Maximum Value in a Range

[ScottyBee]

Hello,

I am typing in the following code and getting an error "run time 1004 unable to find Max". Any ideas? Thanks



Sub ColorMaximumValue()
Dim rng As Range, cell As Range
Set rng = Range("A1:A5")
Debug.Print Application.WorksheetFunction.Max("rng")
End Sub


26931

[Paul_Hossler]

Try




Application.WorksheetFunction.Max(rng)


without the quotes

[ScottyBee]

Thanks Paul, "brain cramp" moment. Forgot that for variable names, you do no need the quotation marks. It worked great :)

[Paul_Hossler]

Can be a little confusing

If had a range named "rng", then it would be sort of similar



Sub ColorMaximumValue()
Dim rng As String
Range("A1:A5").Name = "rng"
Debug.Print Application.WorksheetFunction.Max(range("rng"))End Sub


You can use Tread Tools and mark this one [SOLVED]