Macro to select max value of range E

[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

 

[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

Okay, got this working!

Private Sub CommandButton1_Click()
Dim maxValRange As Range

Set maxValRange = Range("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

 

[Guest]

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

 

[ucanalways]

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 -

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?

 

[Guest]

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.

 

[Guest]

Also, the x = WorksheetFunction.Max(Range("E:E"))
Should be: x = Application.WorksheetFunction.Max(Range("E:E"))
To be the correct syntax. My fault.

 

[ucanalways]

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 -

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

 

[Guest]

That is why I used the

C.Offset(0, 0),Select

You should have just copied and pasted the code.

 

[Guest]

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.

 

[ucanalways]

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 -

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