Macro to select max value of range E

  • Thread starter Thread starter ucanalways
  • Start date Start date
U

ucanalways

I am using the following script to select the maximum of the range E.
I get invalid qualifier error.
All I am trying to do is highlight/select the cell that has the max
value in range E. Please help me to accomplish this. Thanks

Private Sub CommandButton1_Click()
WorksheetFunction.Max(Range("E:E")).Select
End Sub
 
I am using the following script to select the maximum of the range E.
I get invalid qualifier error.
All I am trying to do is highlight/select the cell that has the max
value in range E. Please help me to accomplish this. Thanks

Private Sub CommandButton1_Click()
WorksheetFunction.Max(Range("E:E")).Select
End Sub
Click to expand...

Okay, got this working!

Private Sub CommandButton1_Click()
Dim maxValRange As Range

Set maxValRange = Range("D:D")


maxVal = Application.WorksheetFunction.Max(maxValRange)


Cells.Find(What:=maxVal, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate

End Sub
 
I think you would have to do something like this:

Private Sub CommandButton1_Click()
Dim c As Range
x = WorksheetFunction.Max(Range("E:E"))
Set c = Range("E:E").Find(x, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 0).Select
End If
End Sub
 
I think you would have to do something like this:

Private Sub CommandButton1_Click()
Dim c As Range
x = WorksheetFunction.Max(Range("E:E"))
Set c = Range("E:E").Find(x, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 0).Select
End If
End Sub






- Show quoted text -
Click to expand...

Hi.. I used your code and I did not see any selection in the sheet.

I used c.activate and I get an error "With block variable not set".
Any idea on this please?
 
If your button is on a sheet other that the active sheet, then you would have
to add in the reference to the sheet so the code knows where to execute.
Running the code I gave you from the standard module works on the active
sheet, so your problem has to be the sheet identity.
 
Also, the x = WorksheetFunction.Max(Range("E:E"))
Should be: x = Application.WorksheetFunction.Max(Range("E:E"))
To be the correct syntax. My fault.
 
Also, the x = WorksheetFunction.Max(Range("E:E"))
Should be: x = Application.WorksheetFunction.Max(Range("E:E"))
To be the correct syntax. My fault.







- Show quoted text -
Click to expand...

Hi.. I am using the following code in the module but I still get an
error "Object variable or with block variable not set"

Dim c As Range
x = Application.WorksheetFunction.Max(Range("E:E"))
Set c = Range("E:E").Find(x, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 0).Select
Else
c.Select '''' error here

End If

Can you please help me out? Thanks
 
That is why I used the

C.Offset(0, 0),Select

You should have just copied and pasted the code.
 
If you step through the code, you will see that the C variable equals the
value in the range it is evaluating, even though you have declared it a range
with the Dim statement. So you either have to use C.Address to get a range
style value or as I did, use the C.Offset(0, 0) which gives it a range style
value.
 
If you step through the code, you will see that the C variable equals the
value in the range it is evaluating, even though you have declared it a range
with the Dim statement. So you either have to use C.Address to get a range
style value or as I did, use the C.Offset(0, 0) which gives it a range style
value.









- Show quoted text -
Click to expand...


THIS WORKS! THANKS JLGWHIZ

Option Explicit
Sub findmax()

Dim c As Range
Dim x As Double

x = Application.WorksheetFunction.Max(Range("E:E"))
Set c = Range("E:E").Find(x, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 0).Select
End If

End Sub
 
Back
Top