hailog
10-03-2007, 10:52 AM
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
...
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
...