Relative cell referencing

[musha26]

Help please!

I have a cell c8 in sheet1 whose value is from the result of the maxnumber in a table in sheet2. How do I insert in c9 in sheet1 the value in columnA in sheet2 corresponding to the same row the maxnumber was found? Basically in sheet2 column A represents dates so I would like to find the date my maxnumber falls on.

 

[SkipVought]

Hi,

This seems like a worksheet lookup function would work for you. VLOOKUP using the MAX value and return the column you want.

Skip,

_[red][/red]

 

[musha26]

Hi skip

I know how to so vlookups in excel, but i'm still new to Vba and have no idea how to do this, the column I want to retain is column A

 

[SkipVought]

WHY are you using VBA, when a LOOKUP will suffice?

Skip,

_[red][/red]

 

[musha26]

I have a button in my work book, that is attached to a VBAmacro that runs a couple of modules and inserts the data in the desired celss, so this is the last part to the project

 

[SkipVought]

Do the LOOKUP first. Make sure that it works.

Once it works, then you can code it relatively simply if we know EXACTLY WHAT you put into your LOOKUP formula.

Skip,

_[red][/red]

 

[musha26]

Thanks Skip you're a star

 

[Bong]

In a general case, let's say you have a worksheet like this:
[tt] A B C D
This 4 84 336
is 2 105 210
a 1 97 97
story 5 115 575
line. 5 108 540
Each 4 69 276
cell 4 99 396
is 2 105 210
one 3 111 333
word. 5 119 595[/tt]

Now, you want to find the value in ColumnA of the row that has the Maximum value in ColumnD:

Sub test()
    Set r = ThisWorkbook.Sheets(1).UsedRange
    m = Application.WorksheetFunction.Max(r.Columns(4))
    Set c = r.Find(what:=m, LookIn:=xlValues)
    Debug.Print c.Offset(0, -3).Value
End Sub

_________________
Bob Rashkin