Easier Way to Macro Total

S

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
 
G

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
 
S

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
 
G

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
 
S

Sue

Hi you Guys

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

Sue


Gary Keramidas said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top