goto max

birdman

Board Regular
Joined
Oct 11, 2005
Messages
187
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Assuming you want to search column A:

Code:
Columns("A:A").Find(WorksheetFunction.Max(Columns("A:A"))).Select
 
Upvote 0
Perhaps something like this

Birdman,

Perhaps something like this?

Code:
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
 
Upvote 0
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?
 
Upvote 0
Matt,
Thanks for responding
That code appears to give the max on the entire sheet, instead of just in column D.
 
Upvote 0
birdman said:
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?
 
Upvote 0
If you wanted to check ..

Code:
    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
 
Upvote 0
Von Pookie,
I was referring to your code. I did change the "A:A" references to "D:D" and I received that error.
 
Upvote 0
maybe it is because the numbers i am trying to find the max are not values but formulas that return numbers
 
Upvote 0
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:
Code:
Dim rng As Range
Set rng = Columns("L:L")
With rng
    .Find(WorksheetFunction.Max(rng)).Select
End With
Thanks for all the help everyone
 
Upvote 0

Forum statistics

Threads
1,214,657
Messages
6,120,769
Members
448,991
Latest member
Hanakoro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top