Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing Cells Above
This should be so simple, but I'm struggling. I want to sum a column of
contiguous numbers and put the total in the first empty cell beneath them. The number of cells summed varies depending on the column. When recording this and reviewing the code, the VB Editor gives me the following: ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" where -15 is the number of cells to be added. Of course, this changes depending on the number of cells to be added. I've tried creating a variable (COUNT) that stores the number of cells to be added and putting it in the formula as follows, but it only gives me an error message: ActiveCell.FormulaR1C1 = "=SUM(R[-COUNT]C:R[-1]C)" I'm sure there's an infinitely easier way to do this. Thanks in advance for any suggestions! -- Steve C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing Cells Above
If the first cell is always in row 1 then
ActiveCell.formulaR1C1 = "=Sum(R1C:R[-1]C)" -- Regards, Tom Ogilvy "Steve C" wrote: This should be so simple, but I'm struggling. I want to sum a column of contiguous numbers and put the total in the first empty cell beneath them. The number of cells summed varies depending on the column. When recording this and reviewing the code, the VB Editor gives me the following: ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" where -15 is the number of cells to be added. Of course, this changes depending on the number of cells to be added. I've tried creating a variable (COUNT) that stores the number of cells to be added and putting it in the formula as follows, but it only gives me an error message: ActiveCell.FormulaR1C1 = "=SUM(R[-COUNT]C:R[-1]C)" I'm sure there's an infinitely easier way to do this. Thanks in advance for any suggestions! -- Steve C |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing Cells Above
I should have clarified: the first cell is not usually in row one. There
are typically empty cells above each summed range. For example, A1:A5 are empty, while A6:A23 contain numbers. The sum would be placed in A24 in this case. The number of summed cells changes from column to column. I know how to write code to find the first empty cell beneath a column of numbers, but it's creating the appropriate SUM formula in that cell that's challenging me. Thanks! -- Steve C "Tom Ogilvy" wrote: If the first cell is always in row 1 then ActiveCell.formulaR1C1 = "=Sum(R1C:R[-1]C)" -- Regards, Tom Ogilvy "Steve C" wrote: This should be so simple, but I'm struggling. I want to sum a column of contiguous numbers and put the total in the first empty cell beneath them. The number of cells summed varies depending on the column. When recording this and reviewing the code, the VB Editor gives me the following: ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" where -15 is the number of cells to be added. Of course, this changes depending on the number of cells to be added. I've tried creating a variable (COUNT) that stores the number of cells to be added and putting it in the formula as follows, but it only gives me an error message: ActiveCell.FormulaR1C1 = "=SUM(R[-COUNT]C:R[-1]C)" I'm sure there's an infinitely easier way to do this. Thanks in advance for any suggestions! -- Steve C |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing Cells Above
R[-1]C
can be used to refer to the last non empty cell. However, it appears the real challenge is determining where the data starts. If I wanted to do it I would do cells(rows.count,activecell.column).End(xlup)(2).S elect set rng = range(activecell.offset(-1,0),activecell.offset(-1,0).End(xlup)) activecell.formula = "=Sum(" & rng.address(0,0), & ")" -- Regards, Tom Ogilvy "Steve C" wrote: I should have clarified: the first cell is not usually in row one. There are typically empty cells above each summed range. For example, A1:A5 are empty, while A6:A23 contain numbers. The sum would be placed in A24 in this case. The number of summed cells changes from column to column. I know how to write code to find the first empty cell beneath a column of numbers, but it's creating the appropriate SUM formula in that cell that's challenging me. Thanks! -- Steve C "Tom Ogilvy" wrote: If the first cell is always in row 1 then ActiveCell.formulaR1C1 = "=Sum(R1C:R[-1]C)" -- Regards, Tom Ogilvy "Steve C" wrote: This should be so simple, but I'm struggling. I want to sum a column of contiguous numbers and put the total in the first empty cell beneath them. The number of cells summed varies depending on the column. When recording this and reviewing the code, the VB Editor gives me the following: ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" where -15 is the number of cells to be added. Of course, this changes depending on the number of cells to be added. I've tried creating a variable (COUNT) that stores the number of cells to be added and putting it in the formula as follows, but it only gives me an error message: ActiveCell.FormulaR1C1 = "=SUM(R[-COUNT]C:R[-1]C)" I'm sure there's an infinitely easier way to do this. Thanks in advance for any suggestions! -- Steve C |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing Cells Above
Thanks, Tom. Your code helped immensely!
-- Steve C "Tom Ogilvy" wrote: R[-1]C can be used to refer to the last non empty cell. However, it appears the real challenge is determining where the data starts. If I wanted to do it I would do cells(rows.count,activecell.column).End(xlup)(2).S elect set rng = range(activecell.offset(-1,0),activecell.offset(-1,0).End(xlup)) activecell.formula = "=Sum(" & rng.address(0,0), & ")" -- Regards, Tom Ogilvy "Steve C" wrote: I should have clarified: the first cell is not usually in row one. There are typically empty cells above each summed range. For example, A1:A5 are empty, while A6:A23 contain numbers. The sum would be placed in A24 in this case. The number of summed cells changes from column to column. I know how to write code to find the first empty cell beneath a column of numbers, but it's creating the appropriate SUM formula in that cell that's challenging me. Thanks! -- Steve C "Tom Ogilvy" wrote: If the first cell is always in row 1 then ActiveCell.formulaR1C1 = "=Sum(R1C:R[-1]C)" -- Regards, Tom Ogilvy "Steve C" wrote: This should be so simple, but I'm struggling. I want to sum a column of contiguous numbers and put the total in the first empty cell beneath them. The number of cells summed varies depending on the column. When recording this and reviewing the code, the VB Editor gives me the following: ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" where -15 is the number of cells to be added. Of course, this changes depending on the number of cells to be added. I've tried creating a variable (COUNT) that stores the number of cells to be added and putting it in the formula as follows, but it only gives me an error message: ActiveCell.FormulaR1C1 = "=SUM(R[-COUNT]C:R[-1]C)" I'm sure there's an infinitely easier way to do this. Thanks in advance for any suggestions! -- Steve C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing 2 columns cells into a 3rd colums cells | Excel Discussion (Misc queries) | |||
not summing all the cells | Excel Discussion (Misc queries) | |||
Summing Cells with #N/A | Excel Worksheet Functions | |||
Summing some cells | Excel Worksheet Functions | |||
Finding cells and summing variable cells | Excel Programming |