I'm in the process of writing a macro that prepares inventory data.
However, I'm stuck on the part where the macro inserts a forumla to
sum the range of values in column P.

Tricky part: the length of the range in column P varies each month. 

So far, my work around is to have the macro insert a row at the top of
the data and put a "Z" in column P. I then have the macro sort the
data by column P which will always sends the "Z" to the bottom where I
want the sum formula placed. Lastly, I use the macro to find the "Z"
and make it the active cell.

How do I make the macro insert a sum formula that sum the range of
P2:ActiveCell -1. ??? In other words, sum all the cells above "Z".

Here is a sample of what I think the formula should look like, but
since I'm not a macro guru, I can't make it work.

    Columns("P:P").Select
    Selection.Find(What:="Z", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Select
    Selection.Value = "=SUM(P2:" & ActiveCell - 1 & ")"

Please point me in the right direction. Thanks!

Hello Denelf,

Very close. I find that
    Columns("P:P").Select
    Selection.Find(What:="Z", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
    ActiveCell.Select
    Selection.Formula = "=SUM(P2:" & Format(ActiveCell.Row - 1, "P#") & ")"
works much better.

Let's walk through the differences.
[1] I removed SearchFormat, apparently an option in your version of
Excel but not mine. I don't see it should matter in any case.
[2] Changed Selection.Value to Selection.Formula (you want to modify
the formula, not the value).
[3] 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
  =SUM(P2:P11)
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:

  Header
  Value
   ...
  Value
  Trailer
  =Sum(HeaderCell:TrailerCell)
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
  ActiveSheet.SpecialCells(xlCellTypeLastCell).Row
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.

  --Maniac

---

Request for Answer Clarification by denelf-ga on 31 Jul 2006 16:46 PDT

Thanks for the reply.

It worked great for column "P". Hwr, when I tried to use it for column
"M", it caused an error. I tried the change over a good number of
times. Keeping everying constant except changing the all the P's to
M's in the code you gave me. Which seems really strange that it
doesn't work because it should be just a simple switch.

I would like to be able to use this code on any column just by tweaking it.

---

Clarification of Answer by maniac-ga on 31 Jul 2006 19:20 PDT

Hello Denelf,

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
this:
  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
steps).

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
more completely?

  --Maniac

Okay. I see. Thanks for the help.