VBA Use of SUM function with variables

notsobob

New Member
Joined
Apr 7, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I am working in VBA in Excel from 365. I have a workbook with sheets for several departments, all of which are identical except for the data. The sheet contains a list of items and several columns relating to those items. The data entry always starts at the same row (9) but because of processes elsewhere the lists vary in length so the row containing totals varies from sheet to sheet. I can loop through the sheets and use use a variable, LastRow, to identify the totals row in each sheet. What I want to achieve is a totals cell which automatically recalculates when the data in the column above it changes. When I introduce variables into the SUM function formula the totals stubbornly refuse to recalculate. My code is:

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range(Cells(LastRow, 1), Cells(lRow, 10))

For Each cel In r

y = cel.Column
Cells(LastRow, y) = WorksheetFunction.Sum(Range(Cells(9, y), Cells(LastRow - 1, y)))
If Cells(LastRow, y) = 0 Then Cells(lRow, y) = ""

Next cel


staffy.xlsb
ABCDEFGHIJ
1Department A
2
3Production Activity Summary
4
5
6Activity DescriptionActivity RepeatsStaffActivity Length HoursCost
7ABC
8Person 1Person 2Person 3
9Activity one15152.0030.00
10Activity two5322.0010.00
11Activity three9458.0072.00
12Activity four1714121.0017.00
13Activity five913516.00144.00
14Totals
Dept A
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
When/how is the VBA code being run?

If it is being run manually, simply adding more rows to the data AFTER the code has been run won't change anything.
The VBA code would need to be re-run again manually or automatically be triggered in something like a "Worksheet_Change" event.
If you post your ENTIRE code, we can probably help you fix it.

Also, this doesn't look quite right to me:
Rich (BB code):
Set r = Range(Cells(LastRow, 1), Cells(lRow, 10))
Shouldn't that reference to "IRow" also be "LastRow"?
Otherwise, you are probably creating a multi-row range, depending on what "IRow" is set to (you haven't shown us that calculation).
 
Last edited:
Upvote 0
Solution
I don't think your showing us your complete code.
Would you please show us all the code.
 
Upvote 0
I don't think your showing us your complete code.
Would you please show us all the code.
Thanks for the speedy reply. You're right, the lRow should read LastRow.
I should have said the other processes determine the number of rows at the start and once set, the number of rows stays the same, it is only the data that change resulting in a need to recalculate the totals.
The only other code (which I omitted) are a couple of lines, For each WS.......Next WS, to loop through all the worksheets.
Before I raised the question I had almost concluded the result I hoped for could not be automated and that a re-run of the programme manually would be necessary. Since all the alterations will be made by the personnel within a specified period I can add a simple routine to run it again at that time.
Thanks for the suggestion of a 'Worksheet_Change' Event - I will enjoy investigating that line of thought and hopefully will learn something new in the process.

Thanks once again for looking at my query.
 
Upvote 0
When/how is the VBA code being run?

If it is being run manually, simply adding more rows to the data AFTER the code has been run won't change anything.
The VBA code would need to be re-run again manually or automatically be triggered in something like a "Worksheet_Change" event.
If you post your ENTIRE code, we can probably help you fix it.

Also, this doesn't look quite right to me:
Rich (BB code):
Set r = Range(Cells(LastRow, 1), Cells(lRow, 10))
Shouldn't that reference to "IRow" also be "LastRow"?
Otherwise, you are probably creating a multi-row range, depending on what "IRow" is set to (you haven't shown us that calculation).
Thanks for the speedy reply. You're right, the lRow should read LastRow.
I should have said the other processes determine the number of rows at the start and once set, the number of rows stays the same, it is only the data that change resulting in a need to recalculate the totals.
The only other code (which I omitted) are a couple of lines, For each WS.......Next WS, to loop through all the worksheets.
Before I raised the question I had almost concluded the result I hoped for could not be automated and that a re-run of the programme manually would be necessary. Since all the alterations will be made by the personnel within a specified period I can add a simple routine to run it again at that time.
Thanks for the suggestion of a 'Worksheet_Change' Event - I will enjoy investigating that line of thought and hopefully will learn something new in the process.
 
Upvote 0
Do you really need VBA at all?

You could just use Excel formulas.
For example, if you put this formula in column B:
Excel Formula:
=SUM(INDIRECT("B9:B"&ROW()-1))
it will sum from row 9 in column B down to the row just above where this formula is placed.
So no matter how many rows you insert or delete, it will always sum dynamically between row 9 and the row just above the formula.
 
Upvote 0
Alternatively, if you add rows in the middle of your range, your SUM formula should grow automatically, i.e.
if your formula is:
Excel Formula:
=SUM(B9:B20)
and if you insert the new rows between rows 9 and 20, Excel will increase the range automatically.
It is only if you insert new rows after row 20 that the formula won't automatically increase the row number.
 
Upvote 0
Do you really need VBA at all?

You could just use Excel formulas.
For example, if you put this formula in column B:
Excel Formula:
=SUM(INDIRECT("B9:B"&ROW()-1))
it will sum from row 9 in column B down to the row just above where this formula is placed.
So no matter how many rows you insert or delete, it will always sum dynamically between row 9 and the row just above the formula.
Thanks Joe4. My workbook now contains well over a hundred worksheets and was produced by analysing, copying and pasting data from 2 other very large, differently formatted spreadsheets. To do that manually would have been very tedious which is why I went to VBA to automate the process. I am not expert at Excel and solving problems like this is very much a learning curve for me which I enjoy very much. Most of the time I can find answers I need but obviously there are times when I seek advice from others, like yourself, who are far more knowledgeable.
 
Upvote 0
You are welcome.
Glad I was able to help!

Note: That when you find yourself with over a hundred worksheets, and notice performance lagging, that is usually a sign that Excel might not be the right tool for the project.
It sounds to me like you probably have a Relational Database. If that is the case, Relational Database programs like Microsoft Access and SQL are better suited for that.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top