Subtotal for each column problem
Dim col as Range, rng as Range
Dim rng1 as Range, lrow as Long
set rng1 = Activesheet.UsedRange
lrow = rng1.Rows(rng1.rows.count).row + 1
for each col in ActiveSheet.UserRange.Columns
set rng = cells(lrow,col.column).
rng.FormulaR1C1 = "=Sum(R1C:R[-1]C)"
Next
--
Regards,
Tom Ogilvy
"d" wrote in message
...
Tom HTanks for the solution. . . . I've been banging my head for . . .
Anyway, how do I force the " rng.FormulaR1C1 " to place itself on the last
row of the range for all columns. Not all columns are the same row length.
Jeff
"Tom Ogilvy" wrote:
Dim col as Range, rng as Range
for each col in ActiveSheet.UserRange.Columns
set rng = cells(rows.count,col.column).End(xlup)(2)
rng.FormulaR1C1 = "=Sum(R1C:R[-1]C)"
Next
--
Regards,
Tom Ogilvy
"ef" wrote in message
...
I need to add subtotals to data which varies in both the number of row
and
the number of columns.
I can determine the last row and the last column using;
xlApp.Range("A1").Select
z = xlApp.ActiveSheet.UsedRange.Column - 1 +
xlApp.ActiveSheet.UsedRange.Columns.Count
y = xlApp.ActiveSheet.UsedRange.Row - 1 +
xlApp.ActiveSheet.UsedRange.Rows.Count
My question is how do insert into a loop counting the coumn numbers a
calculation that subtotals, the data above it.
I have tried everything I can think of in terms of substituting
variables
but no luck
Thanks
|