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

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

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

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