Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Massachusetts
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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"
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

Posting Permissions

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