Subtotal for each column problem

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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

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

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

 

[Tom Ogilvy]

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

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

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

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