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

On 31 Jan., 19:27, 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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjnotme" wrote in message
...
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

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.


"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
.

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

Hi Don
This is exactly what I need
Thank you for your help
Carol

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjnotme" wrote in message
...
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

.

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

.

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

.

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

.

Very good Rick.

Thanks.


Gord

On Sun, 31 Jan 2010 23:01:38 -0500, "Rick Rothstein"
wrote:

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 - Works like a dream!
Thank you so much
Carol

"Rick Rothstein" wrote:

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

.



.