Very close. I find that
Selection.Find(What:="Z", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
Selection.Formula = "=SUM(P2:" & Format(ActiveCell.Row - 1, "P#") & ")"
works much better.
Let's walk through the differences.
 I removed SearchFormat, apparently an option in your version of
Excel but not mine. I don't see it should matter in any case.
 Changed Selection.Value to Selection.Formula (you want to modify
the formula, not the value).
 The reference to "ActiveCell" returned the value ("Z") in that
cell. I modified it to compute the previous row & format as a string
so the formula read
in my sample set of data.
Please make a clarification request if you have any problems with this
solution or if the explanation is unclear.
As a side note, there may be some other methods that can help solve
this kind of problem. One method that I regularly use for "growing"
data is a layout like this:
and you can freely add / remove cells between the header & trailer
cells and the formula will be updated automatically.
As another option, if your data is "regular" (e.g., a rectangular
region - each column the same height), you could also refer to
to get the row of the "last cell" in the spreadsheet. To see the "last
cell", you can use the Excel menu
Edit -> Goto... -> Special -> Last Cell
which will select the "last cell" and move the window to that
location. Note - the last cell can be well below the bottom (or to the
right) of the data you've manipulated. That behavior is caused by
making some modification (e.g., formatting) with a range much larger
than the data being manipulated.
Good luck with your work.
Clarification of Answer by
31 Jul 2006 19:20 PDT
There are a few ways to update the code to be independent of the
column. Perhaps the simplest is to set the formula in RC format like
Selection.FormulaR1C1 = "=SUM(R2C:R[-1]C)"
Note that this refers to the second row in absolute terms & the
previous row in relative terms.
In case you are not familiar with this notation, the Row / Column
format works like this:
R (or C) refers to the current row / column
R# (or C#) refers to the specified row #/ column # as an absolute reference
R[#] (or C[#]) refers to the specified row / column relative to the
current row (+ / - #)
With this method, we don't have to do any calculations using the
"Active Cell". This is certainly the simplest method. Using RC
notation is also a method that is quite suited for walking through a
spreadsheet using FOR loops (e.g., for each column, do some series of
I am not quite sure why changing the letter P to M did not work for
you. With the additional change noted above (and changing P:P to M:M),
the macro works just fine for me. If you are still having problems,
can you please post a little more code so I can diagnose the problem