inserting a formula

[dstiefe]

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

 

[David McRitchie]

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

 

[dstiefe]

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

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

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

 

[David McRitchie]

Can you give an example of what you have, and what is supposed to
be found and anything that is needed for a solution. If we find it hard to
understand what is wanted, it certainly would be pointless to try to start
programming. Not knowing what you are getting into is still a bit different
than having no idea of what is wanted.
---
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

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

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

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]

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.

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

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

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

 

[dstiefe]

An example of the spreadsheet is at http://myanalystteam.com/example.xls

1) the "total" rows will be unknown - so I have to loop throught the cells
looking for them first

2) once I identify the "Total" row loop back up to determine how many cells
are in the range that i will sum

3) for example, I would like to insert a "sum" formula in cell b21 (of
course I won't know before hand that it will be cell b21)

Thank you

Can you give an example of what you have, and what is supposed to
be found and anything that is needed for a solution. If we find it hard to
understand what is wanted, it certainly would be pointless to try to start
programming. Not knowing what you are getting into is still a bit different
than having no idea of what is wanted.
---
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

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

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

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

 

[David McRitchie]

Since you have several of these things to do wouldn't you simply
use the AutoSum icon toolbar icon (Greek Sigma character)

It is a little tricky if you try to think of what it is doing. If you don't
think about it then it simply usually does what you want.

Select cells B3:B20 then hit the AutoSum and the formula
=SUM(B3:B20) will be entered into cell B21.

I say it can be tricky because if B20 were empty then with the
same selection B20 would receive =SUM(B3:B19)

As I indicated previously the better formula would actually
be =SUM(B3:OFFSET(B21,-1,0) but for generating
the sum each time you create it this method seems appropriate
for you.

You can make it a bit easier on yourself by installing some context
menus, one of which might be of interest to you is AutoSum to
invoke the same code as the button, see
Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.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

An example of the spreadsheet is at http://myanalystteam.com/example.xls

1) the "total" rows will be unknown - so I have to loop throught the cells
looking for them first

2) once I identify the "Total" row loop back up to determine how many cells
are in the range that i will sum

3) for example, I would like to insert a "sum" formula in cell b21 (of
course I won't know before hand that it will be cell b21)

Thank you

Can you give an example of what you have, and what is supposed to
be found and anything that is needed for a solution. If we find it hard to
understand what is wanted, it certainly would be pointless to try to start
programming. Not knowing what you are getting into is still a bit different
than having no idea of what is wanted.
---
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

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



:

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

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