06-06-2005, 11:05 AM
|
|
|
|
Re: find cell location of max value in column
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
--
Regards,
Tom Ogilvy
"rroach" <rroach.1q6v6c_1118034303.6312@excelforum-nospam.com> wrote in
message news:rroach.1q6v6c_1118034303.6312@excelforum-nospam.com...
>
> 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
>
>
> --
> rroach
> ------------------------------------------------------------------------
> rroach's Profile:
http://www.excelforum.com/member.php...o&userid=21093
> View this thread: http://www.excelforum.com/showthread...hreadid=376682
>
|