Finding Maximum value

emmcee · Sep 15, 2011

Hey guys I'm looking for a code where I am trying to find the maximum value of a number in a column but using the "WHILE" loop. This statement works but I don't want to use the End(xIUp).row function I just want a simple loop to find the max value in a column F3 to --- and put it in a cell of B17.

Sub max_value()
Dim LR As Variant

LR = Range("F" & Rows.count).End(xlUp).row
Range("B17").value = WorksheetFunction.Max(Range("F3:F" & LR))

End Sub

Thank You

Weaver · Sep 15, 2011

Why wouldn't you want to use the .end(xlup) function? Is there other data below your range that might interfere with the result? I'm also getting that you don't want to use the .worksheetfunction.max() either, if you're considering a while loop.

emmcee · Sep 15, 2011

Heys, I don't mind using the Worksheetfunction.max(), I don't want to use .End(xIUP) function because I'm using while statements in my code and rather use while for all my codes than changing it.

I looked through the links but I can't find anything that would help me

Weaver · Sep 15, 2011

So you'd rather use a while loop and check each row for a non blank result in order to find the last row of data?

emmcee · Sep 15, 2011

Yes, exactly, I want it to go through each row until it gets to a blank and prints the maximum value at B17

Weaver · Sep 15, 2011

emmcee said:
Yes, exactly, I want it to go through each row until it gets to a blank and prints the maximum value at B17

I've got to be honest here and say that to me, that's a backward step.

Nevertheless:

Code:

r = 3
do while cells(r,"F") <>""
r = r + 1
loop
range("b17").value=worksheetfunction.max("F3:F" & r)

I'm more used to approaching this from the other direction, i.e encouraging people to move away from using loops for this sort of activity.

emmcee · Sep 15, 2011

Heys I tried that code but it somehow didn't work this is what I typed

Sub max_value()
Dim r As Variant

r = 3
Do While Cells(r, "F") <> ""
r = r + 1
Loop
Range("B17").Value = WorksheetFunction.Max("F3:F" & r)
End Sub

Do you see any mistakes?

mirabeau · Sep 15, 2011

Here's another possibility. Uses While, no End(xlUp) and should work even if there's some non-numeric data in your range.

Code:

Sub findmax2()
Dim e As Range, mx As Variant

Set e = Range("F3")
While e.Row < Rows.Count
    If IsNumeric(e.Value) And e.Value > mx Then mx = e.Value
    If e(2) = "" Then Set e = e.End(4) Else Set e = e(2)
Wend

Range("B17") = mx

End Sub

Finding Maximum value

emmcee

New Member

Excel Facts

Weaver

Well-known Member

emmcee

New Member

Weaver

Well-known Member

emmcee

New Member

Weaver

Well-known Member

emmcee

New Member

mirabeau

Banned user

Similar threads

Forum statistics

Share this page

Finding Maximum value

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Banned user

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock