Loop through columns and sum them at bottom

[Sandi]

Hi, first time poster, so be kind.

I've been working on coding a summary sheet that pulls data from
worksheets in the active workbook.

I'm almost done, but am stumped at writing some code that will loop
through the columns that I have copied and total them. I recorded a
macro, but nothing happened when I ran it.

What I have now is this code snippet that I found which works
perfectly when I put my cursor in the active cell.
I am not sure how I should write it into my sub.

code that I found that works:

Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"

I tried this:

For Each col In NewSh
ColNum = ColNum + 1
Range(RwNum, ColNum).Select

Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"

Next col

I get an object doesn't support this property or method error.

Hope you can help!

 

[Dave Peterson]

First, I wouldn't use this, but I think that you'd want to modify your code to
something like:

Option Explicit
Sub testme01()
Dim Col As Range
Dim ColNum As Long
Dim NewSh As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim RwNum As Long

Set NewSh = ActiveSheet

RwNum = 5
ColNum = 0

For Each Col In NewSh.Columns
ColNum = ColNum + 1
Cells(RwNum, ColNum).Select

Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
Next Col

End Sub

But there are a few problems with it. I would only want to only look at the
columns that have data in it--not all the columns in the worksheet.

Second, you may be able to things easier.

I'm gonna guess that you want rows 2 to the last row used in column A summed.
And you want an empty row between the data and that total.

I'm guessing that you want columns A through whatever is the last column used
(based on row 1--headers???).

If that's true:

Option Explicit
Sub testme01A()

Dim LastCol As Long
Dim NewSh As Worksheet
Dim LastRow As Long

Set NewSh = ActiveSheet

With NewSh
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Cells(LastRow + 2, "A").Resize(1, LastCol).FormulaR1C1 _
= "=sum(R2C:R[-2]C)"
End With

End Sub

..cells(lastrow+2,"A")
is the cell that's 2 rows below the last used cell in column A.

..Cells(LastRow + 2, "A").Resize(1, LastCol)
resizes that cell to 1 row by number of the last column used (based on row 1).

In R1C1 reference style:
=sum(R2C:R[-2]C)
is the sum of the cell in R2 of the same column to the cell in the row 2 rows up
from the cell with the formula in the same column.

 

[Sandi]

First, I wouldn't use this, but I think that you'd want to modify your code to
something like:

Option Explicit
Sub testme01()
    Dim Col As Range
    Dim ColNum As Long
    Dim NewSh As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim RwNum As Long

    Set NewSh = ActiveSheet

    RwNum = 5
    ColNum = 0

    For Each Col In NewSh.Columns
        ColNum = ColNum + 1
        Cells(RwNum, ColNum).Select

        Set rng1 = ActiveCell.End(xlDown)
        Set rng2 = rng1.End(xlUp)(2, 1)
        rng1(3, 1).Formula = _
            "=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
    Next Col

End Sub

But there are a few problems with it.  I would only want to only look at the
columns that have data in it--not all the columns in the worksheet.

Second, you may be able to things easier.  

I'm gonna guess that you want rows 2 to the last row used in column A summed.
And you want an empty row between the data and that total.

I'm guessing that you want columns A through whatever is the last column used
(based on row 1--headers???).

If that's true:

Option Explicit
Sub testme01A()

    Dim LastCol As Long
    Dim NewSh As Worksheet
    Dim LastRow As Long

    Set NewSh = ActiveSheet

    With NewSh
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        .Cells(LastRow + 2, "A").Resize(1, LastCol).FormulaR1C1 _
            = "=sum(R2C:R[-2]C)"
    End With

End Sub

.cells(lastrow+2,"A")
is the cell that's 2 rows below the last used cell in column A.

.Cells(LastRow + 2, "A").Resize(1, LastCol)
resizes that cell to 1 row by number of the last column used (based on row 1).

In R1C1 reference style:
=sum(R2C:R[-2]C)
is the sum of the cell in R2 of the same column to the cell in the row 2 rows up
from the cell with the formula in the same column.

Hi, first time poster, so be kind.

I've been working on coding a summary sheet that pulls data from
worksheets in the active workbook.

I'm almost done, but am stumped at writing some code that will loop
through the columns that I have copied and total them.  I recorded a
macro, but nothing happened when I ran it.

What I have now is this code snippet that I found which works
perfectly when I put my cursor in the active cell.
I am not sure how I should write it into my sub.

code that I found that works:

        Set rng1 = ActiveCell.End(xlDown)
        Set rng2 = rng1.End(xlUp)(2, 1)
        rng1(3, 1).Formula = _
        "=Sum(" & Range(rng2, rng1).Address(False, False) & ")"

I tried this:

        For Each col In NewSh
        ColNum = ColNum + 1
        Range(RwNum, ColNum).Select

        Set rng1 = ActiveCell.End(xlDown)
        Set rng2 = rng1.End(xlUp)(2, 1)
        rng1(3, 1).Formula = _
        "=Sum(" & Range(rng2, rng1).Address(False, False) & ")"

        Next col

I get an object doesn't support this property or method error.

Hope you can help!

Hi Dave

Well, first of thank you for your reply! and you are right I wouldn't
want to use the first code -
I copied it into a test module and ran it. It got stuck here:

rng1(3, Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"

I think maybe it encountered empty cells after the last used cell?
But it did sum all of the columns before I got the error although I
need it to start calculating from row number 4.

I tested the second code and it ran perfectly! I changed Count "A" to
Count "B" but it only summed one row.
So I tried this:

Sub testme01A()

Dim LastCol As Long
Dim NewSh As Worksheet
Dim LastRow As Long
Dim col As Range

Set NewSh = ActiveSheet
For Each col In NewSh.Columns

With NewSh

LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Cells(LastRow + 2, "b").Resize(1, LastCol).FormulaR1C1 _
= "=sum(R2C:R[-2]C)"
End With
Next col
End Sub

It ran but didn't loop through the columns like I wanted it looped
through column b.

I thinks we're close.

 

[Dave Peterson]

That line of code:

rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"

is going to have trouble when you run out of columns with data. You're code was
looping through all the columns--no matter how many were used.

That's one reason why I wouldn't have used that first suggestion.

This line of code in the second suggestion:

.Cells(LastRow + 2, "A").Resize(1, LastCol).FormulaR1C1 _
= "=sum(R2C:R[-2]C)"

Doesn't need to loop. After determining the lastrow used in column A, it fills
in all the cells in that row +2 (but just the used columns -- based on row 1).

So maybe you don't have data in row 1 that can be used to determine the last
used column. Or maybe you don't have data in column A that can be used to
determine the last used row.

In either case, if you can pick out a row and pick out a column that can be
used, these two lines are what need to be changed:

LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Sandi wrote:

Hi Dave

Well, first of thank you for your reply! and you are right I wouldn't
want to use the first code -
I copied it into a test module and ran it. It got stuck here:

rng1(3, Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"

I think maybe it encountered empty cells after the last used cell?
But it did sum all of the columns before I got the error although I
need it to start calculating from row number 4.

I tested the second code and it ran perfectly! I changed Count "A" to
Count "B" but it only summed one row.
So I tried this:

Sub testme01A()

Dim LastCol As Long
Dim NewSh As Worksheet
Dim LastRow As Long
Dim col As Range

Set NewSh = ActiveSheet
For Each col In NewSh.Columns

With NewSh

LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Cells(LastRow + 2, "b").Resize(1, LastCol).FormulaR1C1 _
= "=sum(R2C:R[-2]C)"
End With
Next col
End Sub

It ran but didn't loop through the columns like I wanted it looped
through column b.

I thinks we're close.

 

[Sandi]

That line of code:

        rng1(3, 1).Formula = _
            "=Sum(" & Range(rng2, rng1).Address(False, False) & ")"

is going to have trouble when you run out of columns with data.  You'recode was
looping through all the columns--no matter how many were used.

That's one reason why I wouldn't have used that first suggestion.

This line of code in the second suggestion:

        .Cells(LastRow + 2, "A").Resize(1, LastCol).FormulaR1C1 _
            = "=sum(R2C:R[-2]C)"

Doesn't need to loop.  After determining the lastrow used in column A, it fills
in all the cells in that row +2 (but just the used columns -- based on row 1).

So maybe you don't have data in row 1 that can be used to determine the last
used column.  Or maybe you don't have data in column A that can be usedto
determine the last used row.

In either case, if you can pick out a row and pick out a column that can be
used, these two lines are what need to be changed:

        LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Sandi wrote:

<<snipped>>

Hi Dave

Well, first of thank you for your reply!  and you are right I wouldn't
want to use the first code -
I copied it into a test module and ran it. It got stuck here:

 rng1(3, Formula = _
            "=Sum(" & Range(rng2, rng1).Address(False, False) & ")"

I think maybe it encountered empty cells after the last used cell?
But it did sum all of the columns before I got the error although I
need it to start calculating from row number 4.

I tested the second code and it ran perfectly!  I changed Count "A" to
Count "B" but it only summed one row.
So I tried this:

Sub testme01A()

    Dim LastCol As Long
    Dim NewSh As Worksheet
    Dim LastRow As Long
    Dim col As Range

    Set NewSh = ActiveSheet
    For Each col In NewSh.Columns

    With NewSh

        LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        .Cells(LastRow + 2, "b").Resize(1, LastCol).FormulaR1C1_
            = "=sum(R2C:R[-2]C)"
    End With
    Next col
End Sub

It ran but didn't loop through the columns like I wanted it looped
through column b.

I thinks we're close.

Dave:

I believe I understand. It's sort of like what I tried to record a
macro doing, sum at the bottom of Column B and drag to fill across the
rows.

I have data beginning in row 4. I have data in Column A but it is
text.
Numeric data begins in Column B.

So where do I change the code to start it looking for data in Row #4?
Or do I need to insert something else after the "With NewSh"
statement?

Thanks a million!

 

[Dave Peterson]

First, let me make sure I understand what you want.

You're going to end up with a row that has a bunch of totals on them--all the
totals are on this same row.

And you want this row to be two rows under the last used row.

But you don't want the totals in column A.

Are those correct????

Can you pick out a column that can always be used to determine the last used
row?

I used column A in this code:
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If it's not column A, then change that "A" to the letter for that column.

Can you pick out a row that always has data in it if that column is used. I
guessed that you could use row 1 in this line of code:
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
If you can't use row 1, then change that 1 to the row number you can use.

And to avoid column A, the code needs to look like:

.Cells(LastRow + 2, "B").Resize(1, LastCol - 1).FormulaR1C1 _
= "=sum(R2C:R[-2]C)"

This line is more like when you select lots of cells, then type a single value
or formula. But instead of hitting enter to fill the activecell, you hit
ctrl-enter to fill all the cells in the selection.

=======
So here's the code with that single change to avoid column A totals:

Option Explicit
Sub testme01A()

Dim LastCol As Long
Dim NewSh As Worksheet
Dim LastRow As Long

Set NewSh = ActiveSheet

With NewSh
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Cells(LastRow + 2, "B").Resize(1, LastCol - 1).FormulaR1C1 _
= "=sum(R2C:R[-2]C)"
End With

End Sub


Sandi wrote:

 

[Sandi]

First, let me make sure I understand what you want.

You're going to end up with a row that has a bunch of totals on them--allthe
totals are on this same row.

And you want this row to be two rows under the last used row.  

But you don't want the totals in column A.

Are those correct????

Can you pick out a column that can always be used to determine the last used
row?

I used column A in this code:
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If it's not column A, then change that "A" to the letter for that column.

Can you pick out a row that always has data in it if that column is used. I
guessed that you could use row 1 in this line of code:
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
If you can't use row 1, then change that 1 to the row number you can use.

And to avoid column A, the code needs to look like:

        .Cells(LastRow + 2, "B").Resize(1, LastCol - 1).FormulaR1C1 _
            = "=sum(R2C:R[-2]C)"

This line is more like when you select lots of cells, then type a single value
or formula.  But instead of hitting enter to fill the activecell, you hit
ctrl-enter to fill all the cells in the selection.

=======
So here's the code with that single change to avoid column A totals:

Option Explicit
Sub testme01A()

    Dim LastCol As Long
    Dim NewSh As Worksheet
    Dim LastRow As Long

    Set NewSh = ActiveSheet

    With NewSh
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        .Cells(LastRow + 2, "B").Resize(1, LastCol - 1).FormulaR1C1 _
            = "=sum(R2C:R[-2]C)"
    End With

End Sub

Sandi wrote:

Dave:

I believe I understand.  It's sort of like what I tried to record a
macro doing, sum at the bottom of Column B and drag to fill across the
rows.

I have data beginning in row 4.  I have data in Column A but it is
text.
Numeric data begins in Column B.

So where do I change the code to start it looking for data in Row #4?
Or do I need to insert something else after the "With NewSh"
statement?

Thanks a million!

Dave:

EUREKA!!!!!!

That was the problem, it wasn't picking up the data from row 1 except
in A1 where I had some text.
My summary sheet's data begins at row 4.

Here is my working code:

Sub testme01A()

Dim LastCol As Long
Dim NewSh As Worksheet
Dim LastRow As Long

Set NewSh = ActiveSheet

With NewSh
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Column

.Cells(LastRow + 2, "B").Resize(1, LastCol).FormulaR1C1 _
= "=sum(R2C:R[-2]C)"
End With

End Sub

I did notice that I got "0.00" at the end of the totals row after the
last used column.
Do you have any insight about that?

Again Thanks!

 

[Dave Peterson]

You missed a change in the code:

.Cells(LastRow + 2, "B").Resize(1, LastCol).FormulaR1C1 _
= "=sum(R2C:R[-2]C)"

Should be:

.Cells(LastRow + 2, "B").Resize(1, LastCol - 1).FormulaR1C1 _
= "=sum(R2C:R[-2]C)"

Subtract 1 to ignore column A.

And if you wanted to start with row 4, then you should use:

.Cells(LastRow + 2, "B").Resize(1, LastCol - 1).FormulaR1C1 _
= "=sum(R4C:R[-2]C)"




Sandi wrote:

Dave:

EUREKA!!!!!!

That was the problem, it wasn't picking up the data from row 1 except
in A1 where I had some text.
My summary sheet's data begins at row 4.

Here is my working code:

Sub testme01A()

Dim LastCol As Long
Dim NewSh As Worksheet
Dim LastRow As Long

Set NewSh = ActiveSheet

With NewSh
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Column

.Cells(LastRow + 2, "B").Resize(1, LastCol).FormulaR1C1 _
= "=sum(R2C:R[-2]C)"
End With

End Sub

I did notice that I got "0.00" at the end of the totals row after the
last used column.
Do you have any insight about that?

Again Thanks!

 

[Sandi]

You missed a change in the code:

        .Cells(LastRow + 2, "B").Resize(1, LastCol).FormulaR1C1 _
            = "=sum(R2C:R[-2]C)"

Should be:

        .Cells(LastRow + 2, "B").Resize(1, LastCol - 1).FormulaR1C1 _
            = "=sum(R2C:R[-2]C)"

Subtract 1 to ignore column A.

And if you wanted to start with row 4, then you should use:

        .Cells(LastRow + 2, "B").Resize(1, LastCol - 1).FormulaR1C1 _
            = "=sum(R4C:R[-2]C)"

Sandi wrote:

<<snipped>>

Dave:

EUREKA!!!!!!

That was the problem, it wasn't picking up the data from row 1 except
in A1 where I had some text.
My summary sheet's data begins at row 4.

Here is my working code:

Sub testme01A()

    Dim LastCol As Long
    Dim NewSh As Worksheet
    Dim LastRow As Long

    Set NewSh = ActiveSheet

    With NewSh
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Column

        .Cells(LastRow + 2, "B").Resize(1, LastCol).FormulaR1C1_
            = "=sum(R2C:R[-2]C)"
    End With

End Sub

I did notice that I got "0.00" at the end of the totals row after the
last used column.
Do you have any insight about that?

Again Thanks!

Yes, I don't know how I missed that! (I just copied and pasted)

One more question: I'm trying to stick some formating on that last
line and I have this code:

.Cells(LastRow + 2, "B").Font.Bold = True

But it only bolds the first cell with a total.
So, I tried this:

..Range("B" & LastRow + 2).Font(1, LastCol - 1).Bold = True

But that's a total no go! Since I don't know how many rows I'll have
I thought I pickup the cell
from the previous formula. The first code works, but I'd like all the
totals to have the format.

 

[Dave Peterson]

You need everything that defines the range--the starting cell and the .resize()
stuff:

..Cells(LastRow + 2, "B").Resize(1, LastCol-1).font.bold = true



Sandi wrote:

 

[Sandi]

You need everything that defines the range--the starting cell and the .resize()
stuff:

.Cells(LastRow + 2, "B").Resize(1, LastCol-1).font.bold = true

Sandi wrote:

<<snipped>>

Is there a shorter way to refer to that range? I've tried a couple of
variation of this:

..Range(Cells(LastRow + 2, "B").Resize(1, LastCol-1))

but Excel doesn't seem to like it.

Just seems like a lot of repeat typing...but using the whole bit did
the trick.
I've got the summary sheet done thanks to you!

Sandi

 

[Dave Peterson]

You dropped a dot in your code (before the cells() portion). That's enough to
cause an error (sometimes).

But you could use:

with .Cells(.LastRow + 2, "B").Resize(1, LastCol - 1)
.FormulaR1C1 = "=sum(R4C:R[-2]C)"
.font.bold = true
end with

Or you could set a range variable:

dim myTotalRng as range
....
set mytotalrng = .Cells(.LastRow + 2, "B").Resize(1, LastCol - 1)
mytotalrng.formular1c1 = ...
mytotalrng.font.bold = ...




Sandi wrote:

 

[Sandi]

You dropped a dot in your code (before the cells() portion).  That's enough to
cause an error (sometimes).

But you could use:

        with .Cells(.LastRow + 2, "B").Resize(1, LastCol - 1)
          .FormulaR1C1 = "=sum(R4C:R[-2]C)"
          .font.bold = true
        end with

Or you could set a range variable:

  dim myTotalRng as range
  ....
  set mytotalrng = .Cells(.LastRow + 2, "B").Resize(1, LastCol - 1)
  mytotalrng.formular1c1 = ...
  mytotalrng.font.bold = ...

Sandi wrote:

<<snipped>>

Is there a shorter way to refer to that range?  I've tried a couple of
variation of this:

.Range(Cells(LastRow + 2, "B").Resize(1, LastCol-1))

but Excel doesn't seem to like it.

Just seems like a lot of repeat typing...but using the whole bit did
the trick.
I've got the summary sheet done thanks to you!

Sandi

I tried to write that several times, but didn't get it to run right
although I thought the setup was something like that.
Will give that a try tomorrow. Right now since the sheet's working
(albeit a little wordy) I'm going to get the reports run and
distribute the code to our bookkeeper then I'll go back and tinker
with it some more!

Appreciate your patience with my questions and messy code!

Best Regards,
Sandi