Solved

excel vba find maximum value in range

Posted on 2012-09-11
1,006 Views
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
Question by:mike637

LVL 10

Expert Comment

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 Comment

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

LVL 10

Accepted Solution

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 Closing Comment

Thank you TDLewis,

This worked perfectly!

Thanks again,
Michael
0

Featured Post

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.