You could use a formula like:

dim myCell as range
set mycell = whateveryouneedtosetitto
mycell.formular1c1 = "=SUM(R1C:R[-1]C)"

That R1C1 notation is nice for this.

It says to sum all the cell starting in row 1 of the same column through the
cell right above the cell with the formula.



dstiefe wrote:

What if cell A$2 is a variable? how would you write it then? in other words
I don't now where the cell that will contai the formula will exist. So i
need to loop through looking for my mark...then when I see it I have to sum
up all the cells above it...and again the number of cells above it is unknown
as well ( i can see how I could use the offset property)

thanks

"David McRitchie" wrote:

Do you work for an intelligence agency, that we've never heard of
because you are very skimpy on any details of what you want.

Maybe if I just look at the last sentence, I could interpret that as
how to add up all the cells above a cell the current column starting from
row 2.

A36: =SUM(A$2:OFFSET(A36,-1,0)

More on use of offset in
http://www.mvps.org/dmcritchie/excel/offset.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"dstiefe" wrote in message ...
I am trying to insert (through VBA) a formula in certain cells.

the cells in which the formula will go in will vary (i.e. it's a variable)

and the number of cells (directly above it) that it adds up is also a
variable.

how would I do this?

Thanks




--

Dave Peterson