find cell location of max value in column

rroach · #1 06-06-2005, 01:12 AM

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 · #2 06-06-2005, 01:54 AM

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 · #3 06-06-2005, 10:22 AM

'evaluate' is what i was missing.

thanks!

Rob

mangesh_yadav · #4 06-06-2005, 10:27 AM

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

rroach · #5 06-06-2005, 11:41 AM

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

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

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

1. bot_max_loc = Evaluate("CELL(""address"",Range("A1").SpecialCells(xlCellTypeLastCell).Row))--want 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))")--how to substitute named ranges for a10:a250?

Thanks in advance.

Rob

#6 06-06-2005, 12:05 PM

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
>

#7 06-06-2005, 01:05 PM

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
> >
>
>

Welcome to the Excel Forum

Forum Statistics