Macro to Find the Max ?

[Anandh R]

I need a macro to find a max value for range of cells and also the value of the cell adjacent to max cell.
i should input the range while running the macro.

kindly help me in this ...


Submitted via EggHeadCafe - Software Developer Portal of Choice
EggheadCafe Stock Quote WebService
http://www.eggheadcafe.com/tutorial...a-a9ace43aaf66/eggheadcafe-stock-quote-w.aspx

 

[joel]

You can use the worksheet function max and then search for the valu
using Find like this


Set MyRange = Range("a1:a100")
MaxVal = worksheetfunction.max(MyRange)
Set C = MyRange.find(what:=MaxVal,lookin:=xlvalues,lookat:=xlwhole)
AdjacentValue = C.offset(0,1

 

[Peter T]

=INDEX(B1:B100,MATCH(MAX(A1:A100),A1:A100,0),1)

where the range you want to search Max is A1:A100 and you want to return the
cell in B1:B100.

Regards,
Peter T