Finding max value

M

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
 
D

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
 
D

Don Guillett

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top