Home |
Search |
Today's Posts |
#1
|
|||
|
|||
inserting a formula
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 "dstiefe" wrote in message ... 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 "David McRitchie" wrote: 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 "dstiefe" wrote in message ... 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 |
#7
|
|||
|
|||
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 "dstiefe" wrote in message ... 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 "David McRitchie" wrote: 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 "dstiefe" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dragging a formula | Excel Discussion (Misc queries) | |||
Again-revealing the contents of formula | Excel Worksheet Functions | |||
Named SUM Formula with relative refernce(s) | Excel Worksheet Functions | |||
Hidden Columns are unhidden when inserting a Formula | Excel Discussion (Misc queries) | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions |