CODEWITHSUNDEEP
excelvba
Adren
Adren

Reputation: 93

How to find maximum value in a range while certain condition is met

I have two columns, column "№" has numbers 7 and 8 (basically speaking 7 is a layer and 8s are sub layers, 7 forms group of 8s), column "Value" shows critical level for each 8 (these levels range from 1 to 4). I need to find MAX value in a column "Value" corresponding to the certain group of 8s and it needs to be shown against number 7 in the column "Value"

Here is the table itself

Table

I've tried using basic excel functions. What I've come up with so far is 

=MAX(OFFSET(B3;0;0;MATCH($A$2;A3:$A$8000;0);0))

Where A8000 is just upper limit long enough to include entire array Important note: I've many groups and range of 8s varies for each particular 7

Are there any ways of accomplishing this goal with the help of VBA? Thanks!

Upvotes: 0

Views: 890

Answers (2)

Tim Stack
Tim Stack

Reputation: 3248

The sub below does what you want

Don't forget to change references where necessary

Sub maxinrange()

With Workbooks(REF).Sheets(REF)
    'Find the first 7
    Set seven = .Range("A:A").Find("7", lookat:=xlWhole)
    If Not seven Is Nothing Then
        'Save the row of the first 7 to prevent an endless loop in the future
        frow = seven.Row

        Do While Not seven Is Nothing
            'Determine the following 7 to get the range over which the max value should be calculated
            Set nextseven = .Range("A:A").FindNext(seven)
            If Not nextseven Is Nothing Then
                If Not nextseven.Row = frow Then
                    seven.Offset(, 1).Value = Application.WorksheetFunction.Max(.Range(seven.Offset(1, 1).Address & ":" & nextseven.Offset(-1, 1).Address))
                Else
                    'If no following 7 was found, calculate all the way down to the last row
                    seven.Offset(, 1).Value = Application.WorksheetFunction.Max(.Range(seven.Offset(1, 1).Address & ":" & .Cells(.Rows.Count, 1).Address))
                End If
            End If
            Set seven = .Range("A:A").FindNext(seven)
            If Not seven Is Nothing Then
                'If the found 7 is the same as the first found 7, exit the loop
                If seven.Row = frow Then Exit Do
            End If
        Loop
    End If
End With

End Sub

Upvotes: 0

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

The problem in your formula is, the width part in the Offset function cannot be 0, it should be 1.

Please try it like this...

=IFERROR(MAX(OFFSET(B3;;;MATCH(A2;A3:$A$8000;0)-1));"")

enter image description here

Upvotes: 2

Related Questions