mike637
asked on
Excel VB Max & Min value + cell address
Hello Experts,
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(ThisRang e).Value
Sheet4.cells(15, lCol -2) = cell.address.offset(,-2)
'Next r - I pulled this out and defaulted to row 15 just for testing.
Next lCol
Next lRow
End Sub
Function MaxAddress(ByRef ThisRange As Range) As String
Dim cel As Range
For Each cel In ThisRange
If cel = Application.WorksheetFunct ion.Max(Th isRange) Then MaxAddress = cel.Address
Next cel
' I need to add these functions after I get the first one to work properly.
'FirstMax = Application.WorksheetFunct ion.Max(rn g) - this is row 15
'NextMax = Application.WorksheetFunct ion.Large( rng, 2) - this is row 16
'FirstMin = Application.WorksheetFunct ion.Min(rn g) - this is row 17
'NextMin = Application.WorksheetFunct ion.Small( rng, 2) - this is row 18
End Function
Thanks,
Michael
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(ThisRang
Sheet4.cells(15, lCol -2) = cell.address.offset(,-2)
'Next r - I pulled this out and defaulted to row 15 just for testing.
Next lCol
Next lRow
End Sub
Function MaxAddress(ByRef ThisRange As Range) As String
Dim cel As Range
For Each cel In ThisRange
If cel = Application.WorksheetFunct
Next cel
' I need to add these functions after I get the first one to work properly.
'FirstMax = Application.WorksheetFunct
'NextMax = Application.WorksheetFunct
'FirstMin = Application.WorksheetFunct
'NextMin = Application.WorksheetFunct
End Function
Thanks,
Michael
ASKER
Hi Brian,
This still errored out as bad qualifier.
Would it be easier if I sent you the workbook on this? I really need some help or guidance.
Michael
This still errored out as bad qualifier.
Would it be easier if I sent you the workbook on this? I really need some help or guidance.
Michael
Apologies, mike637. I was too quick. There are two further items...
The one actually causing your error...
Sheet1.MaxAddress(ThisRang e).Value
...should be...
Sheet1.MaxAddress(ThisRang e)
Finally...
Cell.Address.Offset(, -2)
...is that an undefined variable?
Regards,
Brian.
The one actually causing your error...
Sheet1.MaxAddress(ThisRang
...should be...
Sheet1.MaxAddress(ThisRang
Finally...
Cell.Address.Offset(, -2)
...is that an undefined variable?
Regards,
Brian.
ASKER
Brian,
The Cell.Address offset is a variable but it is always related to the address of the MaxAddress.
Michael
The Cell.Address offset is a variable but it is always related to the address of the MaxAddress.
Michael
<blush> And...
Set ThisRange = (Range(lRow, lCol))
...should be...
Set ThisRange = (cells(lRow, lCol))
I an issue with...
Cell.Address.Offset(, -2)
...but there's another problem - if Cell is defined (and set) as a range, you'r eprobably looking for...
Cell.Offset(, -2).Address
So, maybe posting the file would be a good idea!
Thanks,
Brian.
Set ThisRange = (Range(lRow, lCol))
...should be...
Set ThisRange = (cells(lRow, lCol))
I an issue with...
Cell.Address.Offset(, -2)
...but there's another problem - if Cell is defined (and set) as a range, you'r eprobably looking for...
Cell.Offset(, -2).Address
So, maybe posting the file would be a good idea!
Thanks,
Brian.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
As requested,
Just tell me where to send the check to - so you receive it.
Michael
MonthyBusinessReview.xlsm
Just tell me where to send the check to - so you receive it.
Michael
MonthyBusinessReview.xlsm
Try
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
Set ThisRange = (Cells(lRow, lCol))
Sheet4.Cells(15, lCol).Value = MaxAddress(ThisRange)
Sheet4.Cells(15, lCol - 2) = Range(MaxAddress(ThisRange)).Offset(, -2)
'Next r - I pulled this out and defaulted to row 15 just for testing.
Next lCol
Next lRow
End Sub
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
Next cel
' 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
End Function
mike637,
Two of us working on this separately is only going to cause confusion, so I'll step aside as long as ssaqibh's on it.
Regards,
Brian.
Two of us working on this separately is only going to cause confusion, so I'll step aside as long as ssaqibh's on it.
Regards,
Brian.
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 Brian and ssaquibh,
Your guidance has helped out a lot. I split the points on this to be fair to both - expecially when both of you were taking out your time to assist.
Thanks again,
Michael
Your guidance has helped out a lot. I split the points on this to be fair to both - expecially when both of you were taking out your time to assist.
Thanks again,
Michael
Thanks, Michael.
This line...
ThisRange = (Range(lRow, lCol))
...should be...
Set ThisRange = (Range(lRow, lCol))
Regards,
Brian.