Hey, Scripting Guy! How can I add a bunch of rows to an Excel spreadsheet and then stick a total row at the bottom?
— MW
Hey, MW. Could we ask you a favor: is there any chance that you could write back later with this question? As you may or may not know, the University of Washington men’s basketball team is hosting a four-team, round-robin tournament this week. Sunday the Scripting Guy who writes this column and his Scripting Son went to the tournament’s opening day. Because they like watching both games, including the game that doesn’t involve the Huskies, they went over early and ate dinner beforehand: deli sandwiches (with tons of meat). Yesterday they went over early and had pulled-pork barbecue sandwiches. Tonight is the third and final day of the tournament, which means the Scripting Guy who writes this column is absolutely swamped trying to figure out what they should have for dinner this time around.
Anyway if you could get back to us, say when the college basketball season is over, that would really help a lot. Thanks, man!
Just a moment, MW ….
It seems that the Scripting Editor – who is becoming more and more of a killjoy by the moment (as if that was even possible) – has informed us that we were kidding when we suggested that we weren’t going to answer any more scripting questions until after the college basketball season was over. To tell you the truth, we didn’t think we were kidding; we thought we were serious. (Hey, the season will be over sometime in April.) On the other hand, maybe the Scripting Editor is right. After all, the law of averages suggests that sooner or later she’s bound to be right about something. Maybe today’s the day.
Well, just between us, we don’t think she is, either. But what the heck; we’ll humor the Scripting Editor and answer the question anyway:
arrValues = Array(1,5,7,9,11,13,15,17) Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.Add i = 1 For Each intValue in arrValues objExcel.Cells(i, 1).Value = intValue i = i + 1 Next objExcel.Cells(i, 1).Formula = "=SUM(A1:A" & i - 1 & ")"
Before we go much further we should point out that we’re working with a very simple, one-column spreadsheet here. That might not be especially realistic, but it should make it easier for you to follow what we’re doing. To make a fancier, multi-column spreadsheet all you need to do is take the basic recipe shown here and extend it a little.
OK. As you can see, we start out simply by assigning a bunch of numbers to an array named arrValues:
arrValues = Array(1,5,7,9,11,13,15,17)
Why do we do this? That’s easy: we need a bunch of values to add to the spreadsheet, and this seemed as good a way as any to do that. Do you need to put all of your values into an array before adding them to your spreadsheet? Probably not. We went this route simply because it helps us keep the code as short and sweet and possible.
After adding values to our array we then use the following block of code to create an instance of the Execl.Application object, make that instance visible, and then give that instance a new workbook:
Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.Add
With Excel up and running, and visible onscreen, we then set the value of a counter variable named i – a variable we’ll use to keep track of the current row in the spreadsheet – to 1:
i = 1
As soon as that’s done we’re ready to start adding data to our spreadsheet. To do that we simply set up a For Each loop that loops through each value in the array arrValues. Inside that loop we use this line of code to set the value of the cell in column 1 of the current row to the value we grabbed from the array:
objExcel.Cells(i, 1).Value = intValue
If you look closely at the code you can see that we use the counter variable i to represent the row. (Hey, just like we said we were going to do!) The first time through the loop i is equal to 1; as it turns out, the first value we grab from the array is also equal to 1. What does all that mean? That means, the first time through, cell row 1, column 1 (A1) will end up with a 1 in it. We then increment the value of i by 1, meaning that, the second time through the loop, i will be equal to 2. In turn, that means the next value in the array (3) will be plopped into cell row 2, column 1 (A2).
And you’re right: that part is pretty easy, isn’t it? each time we grab a value from the array we simply drop down another row and assign that value to column 1.
We have 8 values in our array; that means that, by the time we exit the loop, cells A1 through A8 will have values. That also means that we’re ready for the last step in the process: putting a formula into cell A9 that adds all the values in cells A1 through A8. How do we do that? Like this:
objExcel.Cells(i, 1).Formula = "=SUM(A1:A" & i - 1 & ")"
All we’re doing here is assigning a value to the Formula property for cell A9 (row 9, column 1). Why does the code look so weird? Well, the easiest way to sum several rows of cells is to use a formula similar to this:
=SUM(A1:A8)
That’s fine, except that it means we need to construct this formula ourselves. In particular, we need to concatenate the following items:
• |
=SUM(A1:A, which simply represents the beginning of the formula. |
• |
i – 1, which represents the current row minus 1. Why minus 1? Well, remember, we’re currently in row 9; if we used the variable i we’d end up putting a formula in cell A9 that would then to add up all the values in cells A1 through A9. That’s right: the dreaded circular reference. (Not that anything like that happened the first time we tried this script, of course.) The mini-equation i – 1 is enough to prevent a circular reference. |
• |
), which simply closes out the formula. |
In other words:
=SUM(A1:A + 8 + ) __________________ =SUM(A1:A8)
Will that give us the sum total for all the values in cells A1 through A8? Give it a try and see for yourself. And if you’d like to know a little more about working with formulas in Excel (as well as a quick tip for converting a cell address like row 8, column 1 to A8) then take a peek at this Office Space column.
As for us, it’s back to work: so far we’ve narrowed it down to Italian or Chinese. Although there is a diner nearby that has really good pie. We have to tell you the truth: when you’re a Scripting Guy you are under enormous pressure from the time you get up until the time you go back to bed. It’s a tough job, but someone has to do it.
Well, at least until we can get rid of the Scripting Editor. (Editor’s Note: The Scripting Guys need to get much more creative in their attempts to get rid of the Scripting Editor if they hope to succeed. Fortunately [for who, we’re not sure] they’re too lazy to put much effort into it. Hopefully they’ll never think to get tips from the Scripting Editor’s last team….)
0 comments