Consulting

Results 1 to 7 of 7

Thread: VBA: Summing up a variable length row using ActiveCell.Formula

  1. #1
    VBAX Regular
    Joined
    Oct 2007
    Posts
    6
    Location

    VBA: Summing up a variable length row using ActiveCell.Formula

    hello ppl! I am new to this place. In the shortwhile I've been here, I find this extremely resourceful. Thanks to all!

    At the outset, I'll tell you my purpose. I am taking a spreadsheet sheet modeling course in my grad studies and I am stuck at one place, for hours, while attempting to do this question:

    The question basically asks us print the totals (on a 2 dimensional grid - assume months along the row and amounts along columns). Using VBA code, I am supposed to print sum total at the end of each row and also at the end of each column. The number of rows and col may vary, so the code has to take this into account. I was able to write the code to do the totaling along columns..but unable to do the same with the row. I am pretty much stuck in the place highlited below. the constraints i have are: 1 - need to use ActiveCell.Formula (not FormulaR1C1), 2 - Once I calculate the total for one col/row, I am supposed to use the copy paste method to apply it to the remaining cells (where total needs to be filled in). Any assistance is appreciated!

    Sub Formula1()

    Dim nMonth As Integer, nProd As Integer, i As Variant

    With Range("B4")
    nMonth = Range(.Offset(0, 0), .End(xlToRight)).Columns.Count
    nProd = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
    End With

    Range("A" & nProd + 4).Value = "Total"
    Range("A3").Offset(0, nMonth + 1).Value = "Total"

    Range("B" & nProd + 4).Select
    ActiveCell.Formula = "=SUM(B4:B" & nProd + 3 & ")"
    Selection.Copy
    Range(Cells(nProd + 4, 3), Cells(nProd + 4, nMonth + 1)).Select
    ActiveSheet.Paste

    Cells(4, nMonth + 2).Select
    i = Split(ActiveCell.Address(1, 0), "$")(0) {I am trying to get the column letter thinking that i can use it in the range part of the sum formula below. nut not sure}
    ActiveCell.Formula = "=SUM(B4: "???")" {I am confused as to what to put in this place so that it will be generic and I can use it to copy along down thru the below statements}
    Selection.Copy
    Range(Cells(5, nMonth + 2), Cells(nProd + 3, nMonth + 2)).Select
    ActiveSheet.Paste
    ...

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    [vba]

    Sub Formula1()
    Dim nMonth As Long, nProd As Long, i As Variant

    With Range("B4")
    nMonth = Range(.Offset(0, 0), .End(xlToRight)).Columns.Count
    nProd = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
    End With

    Range("A" & nProd + 4).Value = "Total"
    Range("A3").Offset(0, nMonth + 1).Value = "Total"

    With Range("B" & nProd + 4)
    .Formula = "=SUM(B4:B" & nProd + 3 & ")"
    .AutoFill .Resize(1, nMonth)
    End With

    With Cells(4, nMonth + 2)
    .FormulaR1C1 = "=SUM(RC2:RC" & nMonth + 1 & ")"
    .AutoFill .Resize(nProd, 1)
    End With
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Oct 2007
    Posts
    6
    Location
    Hi xld! Appreciate your quick response. Thank you very much. In my case, I have a constraint that I use ".Formula" and not ".FormulaR1C1". In the case of column summation, it works. But doing it along the row...that's where I am lost. Is there anyway we can modify the code (i've highlited the part below) to use ".Formula" (just like we did for columns)

    Quote Originally Posted by xld
    [vba]

    Sub Formula1()
    Dim nMonth As Long, nProd As Long, i As Variant

    With Range("B4")
    nMonth = Range(.Offset(0, 0), .End(xlToRight)).Columns.Count
    nProd = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
    End With

    Range("A" & nProd + 4).Value = "Total"
    Range("A3").Offset(0, nMonth + 1).Value = "Total"

    With Range("B" & nProd + 4)
    .Formula = "=SUM(B4:B" & nProd + 3 & ")"
    .AutoFill .Resize(1, nMonth)
    End With

    With Cells(4, nMonth + 2)
    .FormulaR1C1 = "=SUM(RC2:RC" & nMonth + 1 & ")"
    .AutoFill .Resize(nProd, 1)
    End With
    [/vba]

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Tell your tutor that this is a really dumb way of doing it when there is a better way, and tell him I said so if you want

    [vba]


    Sub Formula1()
    Dim nMonth As Long, nProd As Long, i As Variant

    With Range("B4")
    nMonth = Range(.Offset(0, 0), .End(xlToRight)).Columns.Count
    nProd = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
    End With

    Range("A" & nProd + 4).Value = "Total"
    Range("A3").Offset(0, nMonth + 1).Value = "Total"

    With Range("B" & nProd + 4)
    .Formula = "=SUM(B4:B" & nProd + 3 & ")"
    .AutoFill .Resize(1, nMonth)
    End With

    With Cells(4, nMonth + 2)
    .Formula = "=SUM(B4:" & Split(Cells(4, 1 + nMonth).Address(, 0), "$")(0) & "4)"
    .AutoFill .Resize(nProd, 1)
    End With
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Quote Originally Posted by hailog
    ActiveCell.Formula = "=SUM(B4: "???")" {I am confused as to what to put in this place so that it will be generic and I can use it to copy along down thru the below statements}
    An alternative
    [VBA]ActiveCell.Formula = "=SUM(B4:" & ActiveCell.Offset(, -1).Address(0, 0) & ")" [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    VBAX Regular
    Joined
    Oct 2007
    Posts
    6
    Location
    Hi xld & mdmackillop, Thank you both a lot!

    xld, yeah...i'll tell my prof! :-)

  7. #7
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    You can skip the Autofill step

    [VBA]Sub Formula2()
    Dim nMonth As Long, nProd As Long

    With Range("B4")
    nMonth = Range(.Offset(0, 0), .End(xlToRight)).Columns.Count
    nProd = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
    End With

    Range("A" & nProd + 4).Value = "Total"
    Range("A3").Offset(0, nMonth + 1).Value = "Total"

    Range("B" & nProd + 4).Resize(1, nMonth).Formula = _
    "=SUM(B4:B" & nProd + 3 & ")"
    Cells(4, nMonth + 2).Resize(nProd).Formula = _
    "=SUM(B4:" & Split(Cells(4, 1 + nMonth).Address(, 0), "$")(0) & "4)"
    End Sub
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

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