writing a VBA macro

Topic

New Reply

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?

Reply | Quote

Replies

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

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.

Reply | Quote

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

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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:

=SUM(B3:B33*(MOD(ROW(B3:B33),3)=0))

Reply | Quote

Chip Pearson has an array formula to add every nth cell on his useful website … you have to scroll down about 80% of the page to get to the “every nth”

Reply | Quote