VBA Use of SUM function with variables

[notsobob]

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
	A	B	C	D	E	F	G	H	I	J
1	Department A
2
3	Production Activity Summary
4
5
6	Activity Description	Activity Repeats	Staff						Activity Length Hours	Cost
7			A	B	C
8			Person 1	Person 2	Person 3
9	Activity one	15			15				2.00	30.00
10	Activity two	5	3		2				2.00	10.00
11	Activity three	9	4	5					8.00	72.00
12	Activity four	17	1	4	12				1.00	17.00
13	Activity five	9	1	3	5				16.00	144.00
14	Totals
Dept A

 

[Joe4]

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:

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).

 

[My Aswer Is This]

I don't think your showing us your complete code.
Would you please show us all the code.

 

[notsobob]

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.

 

[notsobob]

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:

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.

 

[Joe4]

Do you really need VBA at all?

You could just use Excel formulas.
For example, if you put this formula in column B:

=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.

 

[Joe4]

Alternatively, if you add rows in the middle of your range, your SUM formula should grow automatically, i.e.
if your formula is:

=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.

 

[notsobob]

Do you really need VBA at all?

You could just use Excel formulas.
For example, if you put this formula in column B:

=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.

 

[Joe4]

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.