Macro that adds all numbers that have formulas
 

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

Macro that adds all numbers that have formulas
 

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

Macro that adds all numbers that have formulas
 

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

Macro that adds all numbers that have formulas
 

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
.

Macro that adds all numbers that have formulas
 

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

Macro that adds all numbers that have formulas
 

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

.

Macro that adds all numbers that have formulas
 

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

.

Macro that adds all numbers that have formulas
 

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

.

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

.

Macro that adds all numbers that have formulas
 

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

Macro that adds all numbers that have formulas
 

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

.



.