06-06-2005, 12:05 PM
|
|
|
|
Re: find cell location of max value in column
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
"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uIzY%23WqaFHA.1456@TK2MSFTNGP15.phx.gbl...
> 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
> >
>
>
|