goto max

[birdman]

does anyone know the code to find the largest number in a column and then goto that cell with the largest number? thanks in advance

 

[Von Pookie]

Assuming you want to search column A:

Columns("A:A").Find(WorksheetFunction.Max(Columns("A:A"))).Select

 

[hayati]

Perhaps something like this

Birdman,

Perhaps something like this?

Option Explicit
Sub findmax()
Dim rng As Range
Set rng = [A1].CurrentRegion
With rng
    .Find(WorksheetFunction.Max(rng)).Select
End With
End Sub

Cheers,
Matt

 

[birdman]

Thanks for the response
When running this code, I get:

Run-time Error '91':
Object variable or With block variable not set

Any idea on how to fix this?

 

[birdman]

Matt,
Thanks for responding
That code appears to give the max on the entire sheet, instead of just in column D.

 

[Von Pookie]

Thanks for the response
When running this code, I get:

Run-time Error '91':
Object variable or With block variable not set

Any idea on how to fix this?

Is this referring to my code, or Matt's?

If mine: Did you change the "A:A" references to the correct column you want to search?

 

[Zack Barresse]

If you wanted to check ..

    Dim r as Range
    Set r = Columns("D:D").Find(WorksheetFunction.Max(Columns("D:D")))
    If Not r Is Nothing Then
        r.Select
        Msgbox "Cell found in " & r.Address(0, 0)
    Else
        Msgbox "No value was found."
    End If

 

[birdman]

Von Pookie,
I was referring to your code. I did change the "A:A" references to "D:D" and I received that error.

 

[birdman]

maybe it is because the numbers i am trying to find the max are not values but formulas that return numbers

 

[birdman]

It was because the numbers in column D are the result of a formula. I will have to paste the values of column D in a new column and then run the macro. my finalized code is:

Dim rng As Range
Set rng = Columns("L:L")
With rng
    .Find(WorksheetFunction.Max(rng)).Select
End With

Thanks for all the help everyone