Solved

# How to use VBA to find Max value for each of several blocks of data

Posted on 2011-03-02
Medium Priority
253 Views
I have figures in col E of a ss, separated into races.  I want to return a value for each race that is the maximum of the values in col E pertaining to that race.  I have tried to code it by allocating variables to the top and bottom cell of each appropriate range, but I can't make that work.  Please could someone show me the proper way to do it, thanks.
file-for-ee.xls
0
Question by:robinbernett

LVL 6

Expert Comment

ID: 35016610
where do you want the value to be?  have you considered the 'Max' formula?

-Brandon
0

LVL 24

Accepted Solution

StephenJR earned 1000 total points
ID: 35016864
This perhaps?
``````Sub x()

Dim r As Range

For Each r In Columns(3).SpecialCells(xlCellTypeConstants).Areas
r(1).Offset(2, -2) = WorksheetFunction.Max(r.Columns(3))
Next r

End Sub
``````
0

LVL 9

Assisted Solution

McOz earned 1000 total points
ID: 35016902
Try a macro like this:

``````Sub GetMaxValues()
r = 2
e = 0
Dim rng As Range
Do While e < 4
If Cells(r, 3).Value = "" Then
e = e + 1
If e = 1 Then
Cells(r, 5).Formula = "=Max(" & rng.Offset(0, 2).Address & ")"
Set rng = Nothing
End If
Else
e = 0
If Not rng Is Nothing Then
Set rng = Union(Cells(r, 3), rng)
Else
Set rng = Cells(r, 3)
End If
End If
r = r + 1
Loop
End Sub
``````

It will go down the list, and insert the MAX function in the first empty cell after each list, for that list.

This code assumes that if we find 4 consecutive blank rows, the end of the data has been reached, and that there are no blank rows within each list.

Good luck!
0

LVL 19

Expert Comment

ID: 35016907
select cell A4
enter the following contents :

=max(E3:E29)

the 'equals' sign indicates that cell A4 contains a formula and not a text or a value
the 'max' formula represents the utmost positive value of the supplied cells
the supplied cells range from E3 up to E29 (being values corresponding to a single race)
0

Author Comment

ID: 35017027
KnutsonBM Thanks.  Yes, I considered the Max formula, couldn't make it work with VBA though.
StephenJR, McOz - great solutions, thank you very much - and for explanatory comments which are always helpful, cheers.  Now I'm going to see if I can work out how it has been done.  EE is my saviour and my tutor!
akoster, thanks for your explanations - I was looking for VBA.

0

Author Closing Comment

ID: 35017035
Grateful for your time and expertise.
0

LVL 11

Expert Comment

ID: 35017117
Both of these solutions are excellent!

This is being filed in my "useful" and must have info.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.