Finding max value
 

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

Finding max value
 

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

--
Dan

On Jan 3, 3:25*pm, millwalll
wrote:
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

Finding max value
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"millwalll" wrote in message
...
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

Finding max value
 

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"millwalll" wrote in message
...
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

Finding max value
 

A bit more explanation with examples?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"millwalll" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"millwalll" wrote in message
...
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