Solved

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

Posted on 2011-03-02
7
238 Views
Last Modified: 2012-05-11
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
Comment
Question by:robinbernett
7 Comments
 
LVL 6

Expert Comment

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

-Brandon
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 250 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

Open in new window

0
 
LVL 9

Assisted Solution

by:McOz
McOz earned 250 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

Open in new window


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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 19

Expert Comment

by:akoster
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

by:robinbernett
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

by:robinbernett
ID: 35017035
Grateful for your time and expertise.
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 35017117
Both of these solutions are excellent!

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now