mike637
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.WorksheetFunct ion.Max(Rn g)
r = Application.Match(SygmaDat e, 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.WorksheetFunct ion.Max(Rn g)
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
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(
Range("AN47:AN77") contain Date values
Range("AN47:AN77").offset(
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.WorksheetFunct
r = Application.Match(SygmaDat
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.WorksheetFunct
r = Application.Match(BunDate,
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you TDLewis,
This worked perfectly!
Thanks again,
Michael
This worked perfectly!
Thanks again,
Michael
Open in new window