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

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

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

.

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

.