I need to find the cell location of the max value in a column. If I
paste the following [from =(cell onwards] into a cell I get the right
result. Having a hgard time getting the right VBA code to make this
work in an unattended macro. Probably some bonehead mistake on my part,
but I cannot find it. Suggestions?

GOAL is to make this formula work.

Range("z2").Formula =
"=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100 ,0)-1,0)))"

TIA,

Rob


--
rroach
------------------------------------------------------------------------
rroach's Profile: http://www.excelforum.com/member.php...o&userid=21093
View this thread: http://www.excelforum.com/showthread...hreadid=386908

Since you're only looking at column E, how about:

Range("z2").Formula = "=""E""&MATCH(MAX(E1:E100),E1:E100,0)"

rroach wrote:

I need to find the cell location of the max value in a column. If I
paste the following [from =(cell onwards] into a cell I get the right
result. Having a hgard time getting the right VBA code to make this
work in an unattended macro. Probably some bonehead mistake on my part,
but I cannot find it. Suggestions?

GOAL is to make this formula work.

Range("z2").Formula =
"=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100 ,0)-1,0)))"

TIA,

Rob

--
rroach
------------------------------------------------------------------------
rroach's Profile: http://www.excelforum.com/member.php...o&userid=21093
View this thread: http://www.excelforum.com/showthread...hreadid=386908

--

Dave Peterson

rroach Wrote:
I need to find the cell location of the max value in a column. If I
paste the following [from =(cell onwards] into a cell I get the right
result. Having a hgard time getting the right VBA code to make this
work in an unattended macro. Probably some bonehead mistake on my part,
but I cannot find it. Suggestions?

GOAL is to make this formula work.

Range("z2").Formula =
"=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100 ,0)-1,0)))"

TIA,

Rob

There are more than a couple of ways/formulas *"to find the cell
location of the max value in a column."*

I have a similar situation where I needed to determine what the maximum
value was in a column and all I needed was an Excel formula (with
conditional formatting). Applying this formula to your situation
(Cells E1:E100), you can do this:

1. Highlight E1:E100
2. Go to Format/Conditional Formatting
3. Choose "Formula Is" & "Equal To"
4. Type "=E1=max($E$1:$E$100)" -- without the quotes
5. Click "Format" and then choose a color
6. Click OK
7. Click OK

This is what the conditionally formatted formula will do. The maximum
number in Cells E1:E100 will be shown in color (the color of which you
chose in Step 5 above) -- which is the cell location you are looking
for.

Hope this will help you.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=386908

Dave--works AOK!

Thanks,

Rob


--
rroach
------------------------------------------------------------------------
rroach's Profile: http://www.excelforum.com/member.php...o&userid=21093
View this thread: http://www.excelforum.com/showthread...hreadid=386908