Excel VB Max & Min value + cell address
Posted on 2013-01-22
I have hit a wall in trying to get data calculated correctly in my routine. I keep getting [Invalid Qualifer].
I want to find the max range in a column then find the address of that cell and use the offset for another cell.
Here is my code as it stands now that does not work:
Sub FindMaxMin() 'this is a sheet routine
Dim ThisRange As Range
Dim lRow As Long
Dim lCol As Long
For lRow = 3 To 13
For lCol = 3 To 3 '(I cut this to 3 To 3 for testing, but it will be 3 TO 24)
'r = 15 To 19
ThisRange = (Range(lRow, lCol))
Sheet4.Cells(15, lCol).Value = Sheet4.MaxAddress(ThisRange).Value
Sheet4.cells(15, lCol -2) = cell.address.offset(,-2)
'Next r - I pulled this out and defaulted to row 15 just for testing.
Function MaxAddress(ByRef ThisRange As Range) As String
Dim cel As Range
For Each cel In ThisRange
If cel = Application.WorksheetFunction.Max(ThisRange) Then MaxAddress = cel.Address
' I need to add these functions after I get the first one to work properly.
'FirstMax = Application.WorksheetFunction.Max(rng) - this is row 15
'NextMax = Application.WorksheetFunction.Large(rng, 2) - this is row 16
'FirstMin = Application.WorksheetFunction.Min(rng) - this is row 17
'NextMin = Application.WorksheetFunction.Small(rng, 2) - this is row 18