Summing variable number of rows

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Summing variable number of rows

  1. #1
    New Member
    Join Date
    Jul 2002
    Location
    Akron, OH
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    426
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Thanks so much!

User Tag List

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