Subtotal for each column problem
 

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

Subtotal for each column problem
 

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

Subtotal for each column problem
 

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

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