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

On Oct 24, 2:05 pm, wrote:
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: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



" wrote:

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

On Oct 24, 2:48 pm, JLGWhiz wrote:
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



" wrote:
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- Hide quoted text -

- 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?

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.

" wrote:

On Oct 24, 2:48 pm, JLGWhiz wrote:
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



" wrote:
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- Hide quoted text -

- 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?

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


" wrote:

On Oct 24, 2:48 pm, JLGWhiz wrote:
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



" wrote:
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- Hide quoted text -

- 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?

On Oct 24, 5:00 pm, JLGWhiz wrote:
Also, the x = WorksheetFunction.Max(Range("E:E"))
Should be: x = Application.WorksheetFunction.Max(Range("E:E"))
To be the correct syntax. My fault.



" wrote:
On Oct 24, 2:48 pm, JLGWhiz wrote:
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

" wrote:
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- Hide quoted text -

- 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?- Hide quoted text -

- 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

That is why I used the

C.Offset(0, 0),Select

You should have just copied and pasted the code.

" wrote:

On Oct 24, 5:00 pm, JLGWhiz wrote:
Also, the x = WorksheetFunction.Max(Range("E:E"))
Should be: x = Application.WorksheetFunction.Max(Range("E:E"))
To be the correct syntax. My fault.



" wrote:
On Oct 24, 2:48 pm, JLGWhiz wrote:
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

" wrote:
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- Hide quoted text -

- 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?- Hide quoted text -

- 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

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.

" wrote:

On Oct 24, 5:00 pm, JLGWhiz wrote:
Also, the x = WorksheetFunction.Max(Range("E:E"))
Should be: x = Application.WorksheetFunction.Max(Range("E:E"))
To be the correct syntax. My fault.



" wrote:
On Oct 24, 2:48 pm, JLGWhiz wrote:
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

" wrote:
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- Hide quoted text -

- 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?- Hide quoted text -

- 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

On Oct 25, 6:55 am, JLGWhiz wrote:
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.



" wrote:
On Oct 24, 5:00 pm, JLGWhiz wrote:
Also, the x = WorksheetFunction.Max(Range("E:E"))
Should be: x = Application.WorksheetFunction.Max(Range("E:E"))
To be the correct syntax. My fault.

" wrote:
On Oct 24, 2:48 pm, JLGWhiz wrote:
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

" wrote:
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- Hide quoted text -

- 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?- Hide quoted text -

- 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- Hide quoted text -

- 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