# Thread: Use vb to Sum value in column (2003)

1. ## Use vb to Sum value in column (2003)

I am seeking a solution to summing the values in one column based on changes in values in another column. I have the first column with a location, when the location changes, I am inserting a blank row (Thanks Hans V!). I have placed a "Total " label in the first column at the new row. Since the number of rows per location varies, I am struggling trying to find a way to insert the "=Sum()" formula and identifying how the actual rows to be included in the sum.

The following is how I have identified the change in value in first column and inserted the blank row:
m = .Range("A65536").End(xlUp).Row
' Loop backwards
For r = m To 3 Step -1
' Compare cell to cell above it
If Not .Range("A" & (r - 1)) = .Range("A" & r) Then
' If not equal, insert blank row
.Range("A" & r).EntireRow.Insert
.Range("A" & ®) = "Total " & .Range("A" & (r - 1))
.Range("A" & ®).Font.Bold = True
'Need to total column L Here ?????
End If
Next r
End If

I am trying to sum column L, but don't know if this is where I would do the sum function, or if I need to complete the above for/next loop and then loop through again to find the blank cells and insert the sum function at that time.

I apologize in advance for my ignorance of Excel and VBA.

Thanks!
Ken

2. ## Re: Use vb to Sum value in column (2003)

Ha! If you had indicated from the start that you wanted to create subtotals, I'd have suggested different code. Excel has a Subtotals feature built in, and this can be manipulated using VBA. For example:
<code>
.Range("A2").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(12)
</code>
This will insert subtotals each time the value of the first column changes (GroupBy:=1) and sum (Function:=xlSum) the values in the 12th column (TotalList:=Array(12)). No tedious counting of rows!

3. ## Re: Use vb to Sum value in column (2003)

Hans,

That is very kewl. What if I want to subtotal more than one column. For example, the 12, 13,14, etc.

Regards,
Ken

4. ## Re: Use vb to Sum value in column (2003)

You'd change

..., TotalList:=Array(12)

to

..., TotalList:=Array(12, 13, 14)

In other words, an array of the column offsets for which you want a subtotal.

5. ## Re: Use vb to Sum value in column (2003)

That would have been next guess. I had tried Array(12,5) as in begin at 12 and go for 5. Needless to say it didn't work. LOL

Thanks again for you help.

6. ## Re: Use vb to Sum value in column (2003)

If you click in the word SubTotal in the Visual Basic Editor and press F1, youi'll get help, with an example.

7. ## Re: Use vb to Sum value in column (2003)

Hans,

Thanks. I tried the F1, and it asks if I want to load help. Unfortunately, I don't have the CD with me, so I can't read up on it.

I'll load it when I get back home.

thanks again.

8. ## Re: Use vb to Sum value in column (2003)

Hans,

Is there a way to sum columns AND count rows at the same time?

Thanks,

Ken

9. ## Re: Use vb to Sum value in column (2003)

Yes, for example like this:
<code>
.Range("A2").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(12, 13, 14), Replace:=True
.Range("A2").Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(12, 13, 14), Replace:=False
</code>
The second line has Replace:=False to indicate that the new subtotals should not replace the already existing ones.

10. ## Re: Use vb to Sum value in column (2003)

that is very kewl. Is there a way to sum a group of columns and at the same time count a different column and have only one line of "totals"?

11. ## Re: Use vb to Sum value in column (2003)

Not with the built-in SubTotal method; this always inserts a new row for each subtotal. You'd either have to move the subtotals to one row after using the SubTotal method, or build it all yourself. In both cases, it's extra work, so you should ask yourself if it's worth the effort.

12. ## Re: Use vb to Sum value in column (2003)

Hans,

THANKS so much. I have learn so much from you in the past in Access. And now Excel. Until now, I didn't know that you could use the data menu option in Excel to add and take away subtotals for the entire spreadsheet. If I had known this before, it would have saved countless hours. WOW

and you are correct, I don't believe it is worth the effort at this point to force the count and totals to the same line.

Thanks again.

Ken

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•