For Loop to find Maximum value in a column

[iand5]

How would you go about finding the maximum value in a column using a for loop?

I want to be able to store the maximum in a variable.

 

[mrhstn]

You could use the max worksheet function

Dim max As Integer


max = WorksheetFunction.max(Range("A2:A2000"))

 

[Strooprover]

Perhaps a stupid question, but what if I want to select the range that containers the largest value. Is it possible to somehow select a dimension?

 

[Rick Rothstein]

Perhaps a stupid question, but what if I want to select the range that containers the largest value. Is it possible to somehow select a dimension?

Using mrhstn's code line from Messsage #2 as a base...

Columns("A").Find(WorksheetFunction.Max(Range("A2:A2000"))).Select

 

[mrhstn]

If you want to select the first cell with the maximum value, maybe

Range("A1").Offset(WorksheetFunction.Match(WorksheetFunction.max(Range("A1:A2000")), Range("A1:A2000"), 0) - 1, 0).Select

 

[Scott Huish]

Why do you want to select it? It's not necessary to select ranges in order to work with them in VBA.

 

[kalak]

How would you go about finding the maximum value in a column using a for loop?

I want to be able to store the maximum in a variable.

You ask specifically for a for loop ...here's one ...as you request, the maximum is stored in the variable maxval ...

Sub for_loop_for_max()

Dim a, i As Long, maxval, maxcell As Long
a = Range("A1:A2000").Value
maxval = a(2, 1)
For i = 2 To 2000
    If a(i, 1) > maxval Then
        maxval = a(i, 1)
        maxcell = i
    End If
Next i
Range("A" & maxcell).Select
MsgBox maxval & " is the maximum at row " & maxcell

End Sub