Results 1 to 4 of 4

Summing variable number of rows

This is a discussion on Summing variable number of rows within the Excel Questions forums, part of the Question Forums category; How can I set the formula of an active cell using variables? I have a spreadsheet that I want to ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Location
    Akron, OH
    Posts
    17

    Default

    How can I set the formula of an active cell using variables? I have a spreadsheet that I want to do a variety of totals on (inserting a row in the proper place and "Total" and then summing the variable number of prior rows).

    For example, I want to sum the following:
    B3:B7. The column will remain constant, but the startrow and endrow will vary. (I call them, as variables, "startrow" and "rowvar".)

    I've tried things such as
    ActiveCell.Formula="=SUM(Range(startrow & ':' & rowvar))"
    OR
    ActiveCell.FormulaR1C1="=SUM(R[offset]C:R[rowvar]C)"

    I can't seem to get it. Does anyone know how to do this?

    Thanks.


  2. #2
    Board Regular kieran's Avatar
    Join Date
    Oct 2002
    Location
    Perth, Western Australia
    Posts
    396

    Default

    You need to indicate to VBA that you are using the range address.

    Try
    ActiveCell.Formula="=SUM(Range(startrow.address, endrow.address))"

    where startrow and endrow refer to the starting and ending cells in the range that you want to sum.

  3. #3
    Board Regular
    Join Date
    Nov 2002
    Posts
    143

    Default

    hercush,

    For your example the code would be:

    ActiveCell.Formula = "=SUM(B" & startrow & ":B" & rowvar & ")"

    All you want to do is concatenate the various pieces that are required to construct the cell formula.

    have fun

  4. #4
    New Member
    Join Date
    Jul 2002
    Location
    Akron, OH
    Posts
    17

    Default

    That did it! I had tried a little concatenation earlier, but didn't think it through to this extent.

    Thanks so much!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com