Creating sum formula using vba

[QB]

I need to sum every forth row between a start and end row. When I record a
macro, I get

"SUM(R[-60]C,R[-56]C,R[-52]C,R[-48],....,R[-4]C)"

If I know that I need to sum every forth row from row 3 to 59 in column E of
the active worksheet, how would I program this? I don't quite get the R[]C
format created by the macro recording.

Thank you,

QB

 

[Ken]

try this

firstRow = 10
lastRow = 100
incr = 4

total = 0

for i=firstRow to lastRow step incr
total = total + cells(i,col).value 'col = column number
next i

cells(row,col).value = total 'put your answer somewhere

 

[dan dungan]

I found this thread from 2003:

Dan
_______________________________________________
Newsgroups: microsoft.public.excel.programming
From: "Florian Müller" <[email protected]>
Date: Fri, 4 Apr 2003 11:59:52 +0200
Local: Fri, Apr 4 2003 2:59 am
Subject: Sum every fourth row.

Hi!

I've got an Spreadsheet where i'd like to sum every fourth row,
starting
with the cell G5.
so:
SUM(G5+G9+G13+...+Gn)
SUM(H5+H9+H13+...+Hn)
..
..
..
SUM(Col5+Col9+...+Coln)
The problem is, that i have about 1000 records, so it would much work
to
click on every cell i need.
Which VBA code do i need?

Thanx
Flo

Newsgroups: microsoft.public.excel.programming
From: "Monika Weber" <[email protected]>
Date: Fri, 4 Apr 2003 12:17:04 +0200
Local: Fri, Apr 4 2003 3:17 am
Subject: Re: Sum every fourth row.

Hi Flo,

maybe something like this can help you:

Sub test()
Dim i As Integer
Dim dblSum As Double
For i = 1 To ActiveSheet.Range("G1:G20"). _
count Step 4
dblSum = dblSum + Cells(1, i)
Next i
MsgBox dblSum
End Sub

--
Es liebs Grüessli
Monika Weber [Microsoft MVP für Excel]
http://www.jumper.ch / http://excel.codebooks.de

Newsgroups: microsoft.public.excel.programming
From: "Arvi Laanemets" <[email protected]>
Date: Fri, 4 Apr 2003 13:36:39 +0300
Local: Fri, Apr 4 2003 3:36 am
Subject: Re: Sum every fourth row.

Hi/Hallo

Ich denke dass hier ist wofür Du suchst (n=100)!
=SUMPRODUCT((MOD(ROW(G5:G100)-1;4)=0)*G5:G100)

Arvi Laanemets

Newsgroups: microsoft.public.excel.programming
From: Alan Beban <[email protected]>
Date: Fri, 04 Apr 2003 10:44:42 -0800
Local: Fri, Apr 4 2003 11:44 am
Subject: Re: Sum every fourth row.

A different approach for any who use the functions from the file at
http://home.pacbell.net/beban:

=SUM(ArrayAlternates(ArrayAlternates(G5:G100)))

Alan Beban

Newsgroups: microsoft.public.excel.programming
From: "orthnerk" <[email protected]>
Date: Fri, 4 Apr 2003 13:28:11 -0800
Local: Fri, Apr 4 2003 2:28 pm
Subject: Sum every fourth row.

have you looked at maybe the subtotals function. Is the
data the same for each of the four rows you will be
summing? Subtotals work like a charm and they are quickly
removable if your data changes.