Array Subtotal Macro

[adegroot]

Hi Everyone,

I have this code that searches for the blank cells that I have created and subtotals the values above.
This is perfect & exactly what I need, EXCEPT, I need it to do this for not just column F but F:V & can't seem to get this worked out to do so. I learned programming in pascal & java back in high school (6+ years) so I have a little background but VBA is an entirely different beast and I can't quite get anything to work.

Is there a way to make this so the subtotal can be arrayed during the procedure or after it's executed?

Any help is much appreciated.



[code]
Dim StartRow As Integer
Dim EndRow As Integer

StartRow = 11
EndRow = Range("F5550").End(xlUp).Offset(1, 0).Row
For i = StartRow To EndRow
If Cells(i, "F") = "" And i > StartRow Then
Cells(i, "F").Formula = "=SUM(F" & StartRow & ":F" & i - 1 & ")"
StartRow = i + 1
End If
Next
[code/]

[Solus Rankin]

You can loop through columns also. Like maybe:

Please Login or Register  to view this content.

Untested.

[adegroot]

Hi XeRo,

Thanks for the response.

I tested it and its throwing back a false value in the column F blanks and nothing in F:V.

[Solus Rankin]

Hmm, so the point of the code is to loop through all cells in F:V and if the cell is blank, total all cells up to last empty (subtotaled) cell?

[adegroot]

Correct.
Would the rest of the code prior to this bit be helpful?

[Solus Rankin]

Lets take a look, just to make sure there aren't any conflicts.

[adegroot]

Please Login or Register  to view this content.

[Solus Rankin]

Okay, it looks like you're inserting the rows for the subtotals. I will assume that there won't be two blank cells in a row. Try:

Please Login or Register  to view this content.

e/ I just realized this errors at the bottom of the columns. Let me rework.

[Roger Govier]

Hi

You could replace all of that with this

Please Login or Register  to view this content.

[Solus Rankin]

or

Please Login or Register  to view this content.

[adegroot]

Roger, yours errors out somewhere.

Xero - it subtotals the first space but then every thing after it subtotals anything and everything that is above.

I've moved the first bit of this macro to the one before this.

Just background on this - our estimating server crashed (no loss, it was old and clunky and written in Apache Tomcat) but there was no warning so I threw something together so I could finish my bid. This has been significantly improved, but there is still the missing link between a bid and it being a project.
This is for our project managers - once we are awarded, this sheet is intended to pull all the data from the estimate tab, let them assign job codes, and subtotal it (which is data our ERP system requires).
So
Macro 1 pretties up the data from the estimate into a manageable form for them to assign job numbers.
Macro 2 sorts and filters the job numbers once they are assigned (and now inserts lines so there is a place for the subtotal to go(this is what moved).
Macro 3 is what we are trying here, and its to insert subtotals from column f to column v only subtotalling to the previous blank - which is what I had for column f and was wondering if I could array. Or is there even a possibility that the original macro I posted here can be arrayed or copy/pasted after the initial column (f) runs through. It would be the same formula (=sum) across the board.

[Solus Rankin]

Please Login or Register  to view this content.

[adegroot]

Perfect!

Out of curiosity though, is there a way so it shows the formula instead of just printing what the subtotal is?

[Roger Govier]

Hi

It worked fine on some test data I created.
It would be helpful if you uploaded a copy of your data with details of what you want before and after.

[adegroot]

Roger, I can't upload the file due to the nature of what the file contains.

[Roger Govier]

Well, can you just create a small sample file with some dummy data.
I would be interested to see where my code failed.

[Solus Rankin]

Yeah I'm sure there is, but I was struggling with converting column numbers into column letters (it can be done with an added Function) that the formula would understand. Therefore, I used application.worksheet function so that I could use the cells property and refer to them with column numbers.

[adegroot]

XeRo, no worries, it is much appreciated. The PMs will likely complain, but hey, this gets them a heck of a lot farther & faster than they would otherwise

Roger -
it errored out here:

Please Login or Register  to view this content.

I can get some dummy info together, lunch first though !