Need VBA code for number of values equal to MAX of a column

[jim101]

Need VBA code for number of values equal to MAX of a column

Can this be done in code, I need to get the max value and count the number of times it is in column B, it would also be nice if I could also get the address of the cell(s) that contain the max values, something like the code below, this is for excel 2003 and the numbers in column B are formulas if that matters.
Thanks Jim

 Sub GetMax()
MsgBox "The Max value in column B is " & maxvalue & " it is there " & numberoftimes & _
" time(s) at cell(s) " & theaddress
End Sub

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG10Jul09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
oMax = Application.Max(Rng)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn = Application.Max(Rng) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
MsgBox "The Max value in column B is " & oMax & " it is there " & nRng.Count & _
" time(s) at cell(s) " & nRng.Address
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jim101]

Mick, thanks that works great!