Link to home
Start Free TrialLog in
Avatar of mike637
mike637Flag for United States of America

asked on

excel vba find maximum value in range

Hello Experts,

I have some code written that works, however I need to add additional process to ensure that the right maximum value in the range is selected.

My range is Range("AN47:AN77") - I need it to verify that Range("AN47:AN77").offset(,2) is a (positive integer) with a value  >$500.00 if so than that is the correct cell, if not - it selects the previous maxium value in range that meets that criteria.

Range("AN47:AN77") contain Date values
Range("AN47:AN77").offset(,2) contain Integer values (both positive and negative)

After is identifies the right cell that meets that criteria then
Sygmadate = that cell And "r" corresponds to that cell

Here is my code so far:

Private Sub Worksheet_Activate()
             
        On Error GoTo Endit

        Dim Rng As Range
        Dim SygmaDate As Double, BunDate As Double
        Dim r As Long
       
        Set Rng = Sheet9.Range("AN47:AN77")
            SygmaDate = Application.WorksheetFunction.Max(Rng)
            r = Application.Match(SygmaDate, Rng, 0)
           
            With Sheet13
                .Range("E20") = SygmaDate
                .Range("E18") = Rng(r, 2)
                .Range("E22") = Rng(r, 3)
            End With

'this range is unaffected with the > $500 issue            
         Set Rng = Sheet9.Range("AN80:AN89")
            BunDate = Application.WorksheetFunction.Max(Rng)
            r = Application.Match(BunDate, Rng, 0)
           
            With Sheet13
                .Range("E29") = BunDate
                .Range("E27") = Rng(r, 2)
                .Range("E31") = Rng(r, 3)
            End With
       
       Set range = Nothing
   
    Exit Sub

Endit:
   
    Exit Sub
   
End Sub
Avatar of tdlewis
tdlewis
Flag of United States of America image

I'm not entirely sure if you want to check the value in the same row as SygmaDate or if you want to find the maximum value two columns over.
Set Rng = Sheet9.Range("AN47:AN77")
SygmaDate = Application.WorksheetFunction.Max(Rng)
r = Application.Match(SygmaDate, Rng, 0)
' Look at the value in the same row as SygmaDate
If Rng.Cells(r, 3).Value < 500 Then
    MsgBox "The value for SygmaDate is too small"
End If
' Look at the entire range two columns over
v = Application.WorksheetFunction.Max(Rng.Offset(, 2))
If r <> Application.Match(v, Rng.Offset(, 2), 0) Then
    MsgBox "The largest value is not in the same row as SygmaDate"
End If

Open in new window

Avatar of mike637

ASKER

Hello Expert,

This is not quite what I need.  What I need is the code to find the highest date in the range and if the cell 2 columns to the right is not greater than 500 then it finds the next highest date and checks if the cell offset is >500.  It keeps going through these 30 dates and finds the highest date that also has a cell.offset(,2)>500.

This cell date = my SygmaDate in my code.  And the row of this SygmaDate is my r value for my application match for the cell.offsets.

I hope this helps - please let me know if you need additional information.
Michael
ASKER CERTIFIED SOLUTION
Avatar of tdlewis
tdlewis
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mike637

ASKER

Thank you TDLewis,

This worked perfectly!

Thanks again,
Michael