• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1131
  • Last Modified:

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
0
mike637
Asked:
mike637
  • 2
  • 2
1 Solution
 
tdlewisCommented:
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

0
 
mike637Author Commented:
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
0
 
tdlewisCommented:
OK... I think I understand what you're looking for now.
Private Sub Worksheet_Activate()
Dim dates() As Date
Dim highDate As Date
Dim i As Long
Dim nextDate As Date
Dim Rng As Range
Dim SygmaDate As Double, BunDate As Double
Dim r As Long
     
    On Error GoTo Endit
    Set Rng = Sheet9.Range("AN47:AN77")
    ReDim dates(Rng.Rows.Count)
    highDate = 0
    r = 0
    For i = 1 To Rng.Rows.Count
        dates(i) = Rng.Cells(i, 1).Value
        If highDate < dates(i) Then
            highDate = dates(i)
            r = i
        End If
    Next i
    
    Do While Rng.Cells(r, 3).Value < 500
        nextDate = 0
        For i = 1 To Rng.Rows.Count
            If nextDate < dates(i) And dates(i) < highDate Then
                nextDate = dates(i)
                r = i
            End If
        Next i
        If nextDate = 0 Then
            MsgBox "Cannot find any date in range with value greater than $500"
            Exit Sub
        End If
        highDate = nextDate
    Loop
    With Sheet13
        .Range("E20") = highDate
        .Range("E18") = Rng(r, 2)
        .Range("E22") = Rng(r, 3)
    End With

    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 Rng = Nothing
Endit:
End Sub

Open in new window

0
 
mike637Author Commented:
Thank you TDLewis,

This worked perfectly!

Thanks again,
Michael
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now