Thread: writing a VBA macro

1. writing a VBA macro

Hi,

I am trying to write a small macro in Excel that will start from a given cell and will add every third cell in the same column. I have been using the formula, "ActiveCell.FormulaR1C1", in a For..Next loop but I get a syntex error.

Does anyone has a suggestion?

2. Re: writing a VBA macro

I'm sure there are many ways to do this, I tried to make this readable for you:

<pre>Dim iCurValue As Integer
iCurValue = ActiveCell.Value 'Start at the active cell
Do While Not IsEmpty(ActiveCell.Offset(3, 0).Value) 'loop while the cell is not blank

ActiveCell.Offset(3, 0).Activate
iCurValue = iCurValue + ActiveCell.Value
Loop
MsgBox "i:" & iCurValue 'show a message box with the value, you could do anything
'you want with iCurValue now, such as place it in another cell, etc.
</pre>

3. Re: writing a VBA macro

The following VBA code will add up every third cell in column B starting at B3 and going up to B33:

<pre>Dim I As Integer, dblSum As Double
dblSum = 0
For I = 3 To 33 Step 3
dblSum = dblSum + Cells(I, 2)
Next I
</pre>

4. Re: writing a VBA macro

Hello again,

I am just curious, but is there an equation in Excel worksheet that would do the same operation as the VBA macro, ie,
For I = 3 To 33 Step 3
dblSum = dblSum + Cells(I, 2)
Next I

For example, is there an equation such as: =sum(B3:B33);i=3,33,3 ?

Hanan.

5. Re: writing a VBA macro

The only way that I know of is to explicitly specify which cells you wish to sum. For example:

=B3+B6+B9+B12+B15+B18+B21+B24+B27+B30+B33

6. Re: writing a VBA macro

Have you considered SUMIF()? It may not be appropriate but I was trying to get the same result a couple of weeks ago and realised that you can use sumif() as long as you have a column spare to put some indicators in.

The essence of it is as follows:

columns:
a b c
row
1 jan orders 6000
2 revenue 8000
3
4 feb orders 3000
5 revenue 4000
6
7 q1 orders =sumif(b\$1:b\$6,b7,c\$1:c\$6)
8 revenue =sumif(b\$1:b\$6,b8,c\$1:c\$6)

I hope that's understandable. This works for rows as well as columns but as I said, it does require the labels, though if you don't want them cluttering up the worksheet you could then hide the row or column you put them in.

HTH

Brooke

7. Re: writing a VBA macro

Chip Pearson has an array formula to add every nth cell on his <A target="_blank" HREF=http://www.cpearson.com/excel/excelF.htm>useful website</A> ... you have to scroll down about 80% of the page to get to the "every nth"

8. Re: writing a VBA macro

You did trigger a thought of another way to do this with an array formula. The following formula will add every third cell in the range B3:B33:

<pre>=SUM(B3:B33*(MOD(ROW(B3:B33),3)=0))
</pre>

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•