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

[hailog]

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
...

[Bob Phillips]

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

[hailog]

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)




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

[Bob Phillips]

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




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

[mdmackillop]

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
ActiveCell.Formula = "=SUM(B4:" & ActiveCell.Offset(, -1).Address(0, 0) & ")"

[hailog]

Hi xld & mdmackillop, Thank you both a lot!

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

[mdmackillop]

You can skip the Autofill step

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