Expiring Todayâ€”Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2011-03-02
Medium Priority
244 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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

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.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month11 days, 2 hours left to enroll