Easier Way to Macro Total
 

Hi All

Is there an easier and shorter way than the macro below to add up every
third cell in column K

Sub Macro3()

Range("K3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K12").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"

End Sub

the next range would be K18 then K21, K24,K27,K30, thru to K144







--
Many Thanks

Sue

Easier Way to Macro Total
 

maybe something like this

Sub test()
Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
For i = 3 To 21 Step 3
With ws.Range("K" & i)
.Formula = "=sum(" & .Offset(-1, -7).Address & ":" & _
.Offset(1, -1).Address & ")"
End With
Next
End Sub


--


Gary


"Sue" wrote in message
...
Hi All

Is there an easier and shorter way than the macro below to add up every
third cell in column K

Sub Macro3()

Range("K3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K12").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"

End Sub

the next range would be K18 then K21, K24,K27,K30, thru to K144







--
Many Thanks

Sue

Easier Way to Macro Total
 

On Jun 13, 11:30 am, Sue wrote:
Hi All

Is there an easier and shorter way than the macro below to add up every
third cell in column K

Sub Macro3()

Range("K3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K12").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"

End Sub

the next range would be K18 then K21, K24,K27,K30, thru to K144

--
Many Thanks

Sue

Try this:

Sub SumBy3()
Dim i As Integer
Dim sumTot As Single

sumTot = Range("K3")
For i = 3 To 144 Step 3
sumTot = sumTot + Range("K3").Offset(i)
Next
Range("K145") = sumTot 'or whatever cell you want.

End Sub

SteveM

Easier Way to Macro Total
 

Sub Macro3()
For i = 3 To 144 Step 3
Range("K" & i).FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Next
End Sub

--
Gary''s Student - gsnu200791

Easier Way to Macro Total
 

Hi you Guys

Having fun trying all these answers and they all work OK -- brilliant all of
you
--
Many Thanks

Sue


"Gary Keramidas" wrote:

maybe something like this

Sub test()
Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
For i = 3 To 21 Step 3
With ws.Range("K" & i)
.Formula = "=sum(" & .Offset(-1, -7).Address & ":" & _
.Offset(1, -1).Address & ")"
End With
Next
End Sub


--


Gary


"Sue" wrote in message
...
Hi All

Is there an easier and shorter way than the macro below to add up every
third cell in column K

Sub Macro3()

Range("K3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"
Range("K12").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-7]:R[1]C[-1])"

End Sub

the next range would be K18 then K21, K24,K27,K30, thru to K144







--
Many Thanks

Sue