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

# 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")

With Sheet13
.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
• 2
• 2
1 Solution

Commented:
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")
' 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
``````
0

Author 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

Commented:
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
``````
0

Author Commented:
Thank you TDLewis,

This worked perfectly!

Thanks again,
Michael
0

## Featured Post

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