Finding max value

[millwalll]

Hi all,
I have a spreadsheet with laods data in. In coloum b I have numbers, I want
to find the higest number in that coloum. Once it has found this number I
then want it to offset one cross to coloum a then give me msgbox saying the
value of that cell in coloum a.

What best way to do this

many thanks

 

[Dan R.]

Try this:

Sub test()
MaxVal = Application.WorksheetFunction.Max(Range("B:B"))
Set rng = Range("B:B").Find(MaxVal)
MsgBox rng.Offset(0, -1).Value
End Sub

 

[Don Guillett]

One way
Sub findmaxoffset()
msgbox Cells(Columns(2).Find(Application.Max(Columns(2))).Row, 1)
End Sub

 

[millwalll]

thanks is there also way you can do it like loop wher it be sumthing like this
dim maxval as interger
maxval = 0
then loop thought each cell and compaire the value of each sell with the
value of maxval if it is great replace if not leave it.

 

[Don Guillett]

A bit more explanation with examples?