GoToMax doesn't recognize values less than zero

[talt0612]

Hi all- I'm simply trying to find the max value in a range and select that cell. For values >0, the following works well. However, for values < 0, the entire range is selected. Any thoughts on how to remedy this?

Sub GoToMax()
Dim WorkRange As Range
ActiveSheet.Range("F9:F17").Select
If TypeName(Selection) <> "Range" Then Exit Sub
Set WorkRange = Selection

MaxVal = Application.Max(WorkRange)

On Error Resume Next
WorkRange.Find(What:=MaxVal, _
After:=WorkRange.Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False _
).Select

End Sub

 

[Norie]

I think it's actually not deselecting the range you select right at the start.

The reason for that is probably because something else in the code isn't working, probably Application.Max or Find.

 

[talt0612]

Thanks Norie. I'm not sure what to think. If I type in the values 1,2,...9 in F9:F17, and run the sub, it will select F17 (9). However if I change the values in F9:F17 .01, .02....09 it selects the entire range. Any more thoughts?

 

[Norie]

The code selects the entire range right at the start, so it appears that once it's completed that's what's been selected but the selection hasn't changed.

I changed the code slightly and it works fine with 0.1,0.2 etc.

Option Explicit
Sub GoToMax()
Dim MaxVal
Dim WorkRange As Range
Dim rngFnd As Range
    Set WorkRange = Range("F9:F17")
    MaxVal = Application.Max(WorkRange)
    If Not IsError(MaxVal) Then
        Set rngFnd = WorkRange.Find(What:=MaxVal, _
                                    After:=WorkRange.Range("A1"), _
                                    LookIn:=xlValues, _
                                    LookAt:=xlPart, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, MatchCase:=False _
                                                                        )
        If Not rngFnd Is Nothing Then rngFnd.Select
    End If
End Sub

 

[Rick Rothstein]

Thanks Norie. I'm not sure what to think. If I type in the values 1,2,...9 in F9:F17, and run the sub, it will select F17 (9). However if I change the values in F9:F17 .01, .02....09 it selects the entire range. Any more thoughts?

It does not select the entire range for me... it select F17 (the cell with 0.09 in it). I have a question though... did you mistype your subject? It says "less than zero" but the example you gave are positive numbers less than 1.

By the way, you have a flaw in your code. The LookAt argument is set to xlPart... I think it should be xlWhole. Otherwise, if you had 0.01 to 0.09 as your example proposes and then change the value in, say, F14 to 3, which is clearly the max value, cell F11 will be selected because the xlPart would allow the 3 to match the 3 in 0.03 for that cell.

 

[talt0612]

Rick- Good catch. The title should be "numbers less than 1.
"
Thanks for your advice.

 

[talt0612]

Next problem: if I change the values to percentages (i.e. 0.01%, 0.02%, ...0.09%) the sub will not even run. Any idea why this would be?

 

[Norie]

Are you sure it doesn't run?

It's probably running but not doing anything.

Anyway, this works:

Option Explicit
Sub GoToMax()
Dim MaxVal
Dim RowMax
Dim WorkRange As Range
Dim rngFnd As Range
    Set WorkRange = Range("F9:F17")
    MaxVal = Application.Max(WorkRange)
    If Not IsError(MaxVal) Then
        RowMax = Application.Match(MaxVal, WorkRange, 0)
        
        If Not IsError(RowMax) Then
            WorkRange.Cells(RowMax).Select
        End If
    End If
End Sub

Works with currencies too.