Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing a variable sized range programmatically
Help!! I need to insert a sum formua in first blank cell
to the right of a variable sized range of cells. I need the sum formula inserted so that the amount in that cell will automatically recalculate when a different number is entered. I can select the range including the blank cell to the right, but I can't get a sum formula to work. Any ideas will be greatly appreciated. Norma |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing a variable sized range programmatically
If I have data in A8:D8, I can put this formula in E8:
=SUM($A8:OFFSET(E8,0,-1)) I still used row 8 in this example: Option Explicit Sub testme() Dim myCell As Range With ActiveSheet Set myCell = .Cells(8, "IV").End(xlToLeft).Offset(0, 1) End With myCell.FormulaR1C1 = "=SUM(RC1:OFFSET(RC,0,-1))" End Sub Norma wrote: Help!! I need to insert a sum formua in first blank cell to the right of a variable sized range of cells. I need the sum formula inserted so that the amount in that cell will automatically recalculate when a different number is entered. I can select the range including the blank cell to the right, but I can't get a sum formula to work. Any ideas will be greatly appreciated. Norma -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing a variable sized range programmatically
I am trying to do almost the same thing as Norma, but am
trying to get a variable sized column to sum. I tried different variations of the procedure you sent, any tips on where the changes need to be placed to get this to sum a column not a row would be appreciated. Thanks for the help. Dennis -----Original Message----- If I have data in A8:D8, I can put this formula in E8: =SUM($A8:OFFSET(E8,0,-1)) I still used row 8 in this example: Option Explicit Sub testme() Dim myCell As Range With ActiveSheet Set myCell = .Cells(8, "IV").End(xlToLeft).Offset (0, 1) End With myCell.FormulaR1C1 = "=SUM(RC1:OFFSET(RC,0,-1))" End Sub Norma wrote: Help!! I need to insert a sum formua in first blank cell to the right of a variable sized range of cells. I need the sum formula inserted so that the amount in that cell will automatically recalculate when a different number is entered. I can select the range including the blank cell to the right, but I can't get a sum formula to work. Any ideas will be greatly appreciated. Norma -- Dave Peterson . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing a variable sized range programmatically
Let's clean up the word wrap
Option Explicit Sub testme() Dim myCell As Range With ActiveSheet Set myCell = .Cells(Rows.Count, _ ActiveCell.Column).End(xlUp).Offset(1, 0) End With myCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" End Sub -- regards, Tom Ogilvy Tom Ogilvy wrote in message ... Option Explicit Sub testme() Dim myCell As Range With ActiveSheet Set myCell = .Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(1, 0) End With myCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" End Sub Assumes data starts in Row 1 -- Regards, Tom Ogilvy Dennis wrote in message ... I am trying to do almost the same thing as Norma, but am trying to get a variable sized column to sum. I tried different variations of the procedure you sent, any tips on where the changes need to be placed to get this to sum a column not a row would be appreciated. Thanks for the help. Dennis -----Original Message----- If I have data in A8:D8, I can put this formula in E8: =SUM($A8:OFFSET(E8,0,-1)) I still used row 8 in this example: Option Explicit Sub testme() Dim myCell As Range With ActiveSheet Set myCell = .Cells(8, "IV").End(xlToLeft).Offset (0, 1) End With myCell.FormulaR1C1 = "=SUM(RC1:OFFSET(RC,0,-1))" End Sub Norma wrote: Help!! I need to insert a sum formua in first blank cell to the right of a variable sized range of cells. I need the sum formula inserted so that the amount in that cell will automatically recalculate when a different number is entered. I can select the range including the blank cell to the right, but I can't get a sum formula to work. Any ideas will be greatly appreciated. Norma -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing a variable range | Excel Worksheet Functions | |||
SUMIF/SUMPRODUCT Criteria are Variable Sized | Excel Discussion (Misc queries) | |||
Using variable sized range in CountIf() | Excel Discussion (Misc queries) | |||
Summing a variably-sized range | Excel Worksheet Functions | |||
Summing a variable range of columns | Excel Worksheet Functions |