find cell location of max value in column

[rroach]

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

 

[mangesh_yadav]

Hi Rob,

=CELL("address",OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,0)-1,0))

works properly.
Just check that range A1:A10 is the range which holds your data, and A1
is the first cell in the range. Replace these with correct values if
they are different from your case.

This is a simple worksheet formula. Do you want a VBA solution
instead.

In VBA you can call the same formula by using the evaluate e.g.
=
EVALUATE("CELL(""address"",OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,0)-1,0))")

Mangesh

 

[rroach]

'evaluate' is what i was missing.

thanks!

Rob

 

[mangesh_yadav]

Thanks for the feedback. You could also do it in a different manner, but
since you had a worksheet function already, you might as well use it in
VBA.

Mangesh

 

[Tom Ogilvy]

Sub Macro1()
Dim top_max_loc as Variant
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A10:A250")
top_max_val = Application.WorksheetFunction.Max(myRange)
top_max_Loc = application.Match(top_max_val,myrange,0)
top_max_Loc= myrange(top_max_loc).Address
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

 

[rroach]

Extending the previous question. I am too dense to see what I am doin
wrong in the following VBA statement. I'd like to create a variabl
that contains the location of the last cell.

1. Then create a range that is from 250 cells before last cell to las
cell.

2. Then repeat what Mangesh helped me figure out form before--find th
max in that range. Having trouble passing range variables to th
statements? Ideas?

1. bot_max_loc
Evaluate("CELL(""address"",Range("A1").SpecialCells(xlCellTypeLastCell).Row))--wan
to get location (a:767) of last cell, then use that in a range?

2. top_max_loc
Evaluate("CELL(""address"",OFFSET(A10,MATCH(MAX(a10:a250),a10:a250,0)-1,0))")--ho
to substitute named ranges for a10:a250?

Thanks in advance.

Ro

 

[Tom Ogilvy]

Sub Macro1()
Dim top_max_loc as Variant
Dim myRange As Range
With Worksheets("Sheet1")
Set myRange = .Range(.Cells(10,1),cells(rows.count,1).End(xlup))
top_max_val = Application.WorksheetFunction.Max(myRange)
top_max_Loc = application.Match(top_max_val,myrange,0)
top_max_Loc= myrange(top_max_loc).Address
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

continue to ignore if you prefer your much slower approach.



--
Regards,
Tom Ogilvy

Sub Macro1()
Dim top_max_loc as Variant
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A10:A250")
top_max_val = Application.WorksheetFunction.Max(myRange)
top_max_Loc = application.Match(top_max_val,myrange,0)
top_max_Loc= myrange(top_max_loc).Address
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