Link to home
Start Free TrialLog in
Avatar of apitech
apitech

asked on

Excel VBA Macro Summing No Matter How Many Rows

Hello:

Attached is a file containing VBA macro scripting embedded in my Excel 2010 .xltm workbook.

The macro is working in that it is successfully summing the amounts in columns H, I, J, P, Q, and R.

But, it is only doing so up through row 2,807.  (That's because, at this moment in time, my spreadsheet only contains 2,807 rows of data.)  You see, I want it to sum the amounts in these six columns no matter what the number of rows is.

You see, next month when I run this macro for the data in my spreadsheet, the number of rows is not necessarily going to be 2,807.  It may be 3,000 or 1,000 or 4,983, or whatever.

So, I want to know what syntax I need to put in the programming to conduct the totals "dynamically" for the amounts in these six columns.

Also, I need the word "Totals" to appear at the bottom of column G, which would be one column to the left of the total amount of column H of course.  But, again, I want the word "Totals" to appear in column G at the "end" of the set of data--no matter how many rows there are.

From what I can tell, then, the following are the three "sets" of syntax in this macro that are going to need to be modified in order to accomplish this:

Range("H2808:J2808").Select
Selection.FormulaR1C1 = "=SUM(R[-2806]C:R[-1]C)"

Range("G2808").Select
ActiveCell.FormulaR1C1 = "Totals"
Range("G2808").Select

Range("P2808:R2808").Select
Selection.FormulaR1C1 = "=SUM(R[-2806]C:R[-1]C)"

Finally, once this syntax is changed to what I need, I plan on copying and pasting the new syntax into my .xltm file and saving it over the existing file.  That is the correct way of saving such files.  Isn't it?

Thanks!  Much appreciated!

Apitech
Dynamics-Summing.txt
Avatar of Norie
Norie

What rows do you want to sum?

If it's from a fixed row, eg 2, down to the row above where the sum formula will be you can use this.
Range("H2808:J2808").FormulaR1C1 = "=SUM(R2C:R[-1]C)"

Range("G2808").Value = "Totals"

Range("P2808:R2808").FormulaR1C1 = "=SUM(R2C:R[-1]C)"

Open in new window

Oh, did you also want to change the code to put the formulas in the right row?
Dim rng As Range

       Set rng = Range("H" & Rows.Count).End(xlUp).Offset(1)

       rng.Resize(,3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

       rng.Offset(,-1).Value = "Totals"

       rng.Offset(,8).Resize(,3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

Open in new window

Avatar of apitech

ASKER

I think that there may be some confusion as to what I want.  Row 2808, again, is irrelevant as are the number of rows.  I want the summing accomplished for those six columns that I mentioned--no matter how many rows there are in those columns.  Likewise, I want the word "Totals" at the bottom of column G--no matter how many records of data are above it.

The 2nd code I posted has no reference to row 2808.

It finds the last row of data and puts the formula in the next row.
Avatar of apitech

ASKER

What about the other columns?  I only see column H, in that code that was posted.

This was an Excel-based macro that I created and not proramming of code from scratch.  I just want to modify the code that I have for each column, so that the macro dyamically sums the data and puts in the word "Totals".
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial