Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Relative cell referencing

Status
Not open for further replies.

musha26

Technical User
May 21, 2007
8
GB
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.
 





Hi,

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

Skip,

[glasses] [red][/red]
[tongue]
 
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
 
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
 




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,

[glasses] [red][/red]
[tongue]
 
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:
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top