Macro that adds all numbers that have formulas
What about modifying your code this way...
Sub sum_formulas_only()
Dim rng As Range, rng1 As Range, rng2 As Range, AddressSum As String
Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp))
Set rng1 = rng.SpecialCells(xlCellTypeFormulas)
For Each rng2 In rng1
AddressSum = AddressSum & "+" & rng2.Address(0, 0)
Next
rng(rng.Count).Offset(2, 0).Formula = "=" & Mid(AddressSum, 2)
End Sub
--
Rick (MVP - Excel)
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
This is the best I can do using SUM function.
Sub sum_formulas_only()
Dim rng As Range
Dim rng1 As Range
Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp))
Set rng1 = rng.SpecialCells(xlCellTypeFormulas)
rng(rng.Count).Offset(2, 0).Formula = "=Sum(" & rng1.Address & ")"
End Sub
Gord
On Sun, 31 Jan 2010 15:35:01 -0800, jjnotme
wrote:
Thank you. This does give me the total, but I should also have said that I
need to see the formula that in the total cell. In other words, if the
result adds rows 1, 2 and 3, when I see the total in the edit bar, I need
the
formula to be =c1,+c2,+c3 (as tough I added the ranges myself). Is that
doable?
Thanks
"Gord Dibben" wrote:
Sub sum_formulas_only()
Dim rng As Range
Dim rng1 As Range
Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp))
Set rng1 = rng.SpecialCells(xlCellTypeFormulas)
rng(rng.Count).Offset(2, 0).Value = WorksheetFunction.Sum(rng1)
End Sub
Gord Dibben MS Excel MVP
On Sun, 31 Jan 2010 13:27:34 -0500, jjnotme wrote:
I have a worksheet that contains formulas in column C. I need a macro
that will add the results of the formaulas. The result should be
entered 2 lines after the data. This will be in liew of doing
subtotals.
I could use some help with this
Thanks, Carol
.
|