Macro that adds all numbers that have formulas

  • Thread starter Thread starter jjnotme
  • Start date Start date
J

jjnotme

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
 
Hi Carol

Sub SumC
FirstRow=2 'First formula row
LastRow=Range("C" & Rows.Count).End(xlup).Row

MyTotal=WorksheetFunction.Sum(Range("C" & FirstRow & ":C" & LastRow))
Cells(LastRow+2, "C")=MyTotal
End Sub

Regards,
Per
 
If you mean to add up ONLY cells with formulas, try this.

Sub addupifformulas()
mc = 3 'col C
lr = Cells(Rows.Count, mc).End(xlUp).Row
For i = 1 To lr
If Cells(i, mc).HasFormula Then
ms = ms + Cells(i, mc)
End If
Next i
'MsgBox ms
Cells(lr + 2, mc) = ms
End Sub
 
Carol,

Try this.

Sub sonic()
Dim LastRow As Long
Dim MyRange As Range
Set sht = Sheets("Sheet1")' Change to suit
LastRow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set MyRange = sht.Range("C1:C" & LastRow)
For Each c In MyRange
subtot = subtot + c.Value
Next
sht.Range("C" & LastRow + 2).Value = subtot
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
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
 
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
 
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
 
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
 
Back
Top