Easier Way to Macro Total

[Sue]

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

 

[Gary Keramidas]

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

 

[SteveM]

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

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

 

[Gary''s Student]

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

 

[Sue]

Hi you Guys

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

Sue

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

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