|
find cell location of max value in column
HI,
The following macro finds the max value and writes it to a file. I would also like to know the cell location where the max value was found (a25, for example). I'm stumped. The MS KB article has an solution, which is pasted in below, but I cannot get it to worl. Suggestions on getting that to work or another solution?
Tx, Rob
MY MACRO
Sub Macro1()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A10:A250")
top_max_val = Application.WorksheetFunction.Max(myRange)
MsgBox top_max_val
Open "c:\testfile.txt" For Append As #1
'Write #1, top_max_loc, top_max_val, ActiveWorkbook.FullName ' Write blank line.
Close #1
'ActiveWorkbook.Close
End Sub
The MS KB solution to finding the cell location:
To return the address of a cell in a column (in this example, column A, cells A1:A10), use the appropriate formula in the following table. For this
address Use this formula
------------------------------------------------------------------------
Cell with
largest
value =CELL("address",OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,0)-1,0))
Any help much appreciated. As usual, deadline looms.
Rob
|