VBA Code for Summing Multiple Dynamic Ranges in One Column

[FrenchCelt]

Hello,

I have a spreadsheet with a column which lists the total of daily output in various categories (each one in a different row) for multiple people, and I want to be able to sum that output in a row at the bottom of each person's summary. I have a Total row inserted between the bottom row for one person and the top row of the next person, and I have the VBA code looking for the cell with Total in the appropriate row and offsetting to the correct cell where I want the sum to appear, but I don't know how to get it to sum the numbers above that only include the person in question. The number of rows involved is different for each person and will also vary for the same person day to day, so I can't use any constants. The only other way to identify which numbers should be included is that the person's name appears in Column A in every cell I need to sum in Column I, so if I can get it to look first at the row with Total, offset 7 cells to Column I, then look up every row with, say, userA in Column A that has a corresponding value in Column I, and sum them up, then I would be set.

Any help would be appreciated.

 

[FrenchCelt]

You have not answered the question(s) from Post #5 which asks how many columns need to have a Total. And which Columns are that?
The 5 is the first Row with data.

Columns I, J, and K need sums. Columns L and N need averages.

 

[FrenchCelt]

It summed up all the values in Column K per user instead of Column I (which is fine, since that also has to be done, but once I got the base code for Column I, I was then going to modify as necessary to make it work on all other columns requiring the same treatment. What does the j = 5 signify in the code?

I was mistaken...it did not sum Column K...it was just a coincidence that the first two users' values were matching the sum (there are already values in the Total line that are generated by the system, but the report is broken and giving incorrect numbers, hence my need to have a macro provide accurate total numbers until the system is fixed). It appears the code did not do anything.

 

[JLGWhiz]

See if this works.

Sub t()
Dim rng As Range, ar As Range
    With ActiveSheet
        Set rng = .Range("A5", .Cells(Rows.Count, 1).End(xlUp))
        For Each ar In rng.SpecialCells(xlCellTypeConstants).Areas
            ar.Columns(9).Cells(1).End(xlDown).Offset(1) = Application.Sum(ar.Columns(9))
        Next
    End With '
End Sub

 

[FrenchCelt]

See if this works.

Sub t()
Dim rng As Range, ar As Range
    With ActiveSheet
        Set rng = .Range("A5", .Cells(Rows.Count, 1).End(xlUp))
        For Each ar In rng.SpecialCells(xlCellTypeConstants).Areas
            ar.Columns(9).Cells(1).End(xlDown).Offset(1) = Application.Sum(ar.Columns(9))
        Next
    End With 
End Sub

It ended up throwing some random numbers in some of the blank cells in Column I and the Total cell didn't add up correctly.

I should point out, and I'm surprised I left out this detail, but in the Total row for each user, it does include the user's name the way it does for each row above for that user's productivity data.

So Column A looks a little like this:

|Username|
|user1|
|user1|
|user1|
|user1-Total|
|user2|
|user2|
|user2-Total|
and so on.

 

[JLGWhiz]

How about Column B, is there data between groups in that column?

Sub t()
Dim rng As Range, ar As Range
    With ActiveSheet
        Set rng = .Range("B5", .Cells(Rows.Count, 2).End(xlUp))
        For Each ar In rng.SpecialCells(xlCellTypeConstants).Areas
            ar.Columns(8).Cells(1).End(xlDown).Offset(1) = Application.Sum(ar.Columns(8))
        Next
    End With '
End Sub

Actually, I think you could probably use the Subtotals function as someone mentioned earlier.

Or this

Sub t()
Dim rng As Range, ar As Range
    With ActiveSheet
        Set rng = .Range("I5", .Cells(Rows.Count, "I").End(xlUp))
        For Each ar In rng.SpecialCells(xlCellTypeConstants).Areas
            ar.Cells(1).End(xlDown).Offset(1) = Application.Sum(ar)
        Next
    End With '
End Sub

 

[jolivanes]

There is no reason I can see that it should not work. Do the names have leading or trailing spaces at some?

BTW, JLGWhiz's code should be all you need.

 

[JLGWhiz]

There is no reason I can see that it should not work. Do the names have leading or trailing spaces at some?

BTW, JLGWhiz's code should be all you need.

The problem I had was in the OP it stated that there was an empty row between groups of data. I tried to get clarification on if it was truly empty, which after multiple posts we find it is not. But from what I can glean from all the posts, there is a definite gap between the groups of values in Column I. So the last code I posted should be the one that resolves the issue.

 

[jolivanes]

@JLGWhiz
There sometimes is a difference in what people think it is and reality.
On the small example file I made, both suggestions work but if there are more changes coming, who knows.
Regards and Good Luck

 

[FrenchCelt]

How about Column B, is there data between groups in that column?

Sub t()
Dim rng As Range, ar As Range
    With ActiveSheet
        Set rng = .Range("B5", .Cells(Rows.Count, 2).End(xlUp))
        For Each ar In rng.SpecialCells(xlCellTypeConstants).Areas
            ar.Columns(8).Cells(1).End(xlDown).Offset(1) = Application.Sum(ar.Columns(8))
        Next
    End With '
End Sub

Actually, I think you could probably use the Subtotals function as someone mentioned earlier.

Or this

Sub t()
Dim rng As Range, ar As Range
    With ActiveSheet
        Set rng = .Range("I5", .Cells(Rows.Count, "I").End(xlUp))
        For Each ar In rng.SpecialCells(xlCellTypeConstants).Areas
            ar.Cells(1).End(xlDown).Offset(1) = Application.Sum(ar)
        Next
    End With '
End Sub

That didn't work, either. I wish I could at least find a way to send a screenshot so everyone can see what I'm seeing. I tried to explain as best I could what I'm seeing, but this is the wonkiest Excel-based report I've yet to deal with.

Column A has the username of each user (constant for each row being totaled)
Column B has the employee ID for each user (constant for each row being totaled)
Column C has the hire date (constant for each row being totaled)
Column D has the supervisor (constant for each row being totaled)
Column E has the shift (constant for each row being totaled)
Column F has the department (constant for each row being totaled)
Column G has the labor activity being measured (varies by row per user)
Column H has the unit of measure involved (varies by row per user)
Column I has the quantity of units (this is what I'm trying to sum for each user in that user's Total row)
Column J has gap time measured in minutes (this will also be summed in the Total row)
Column K has the total time spent in the labor activity (also to be summed)
Column L has the units/hr for the labor activity (this will be averaged)
Column M has the minimum goal units/hr for the labor activity (varies by row per labor activity)
Column N has the productivity percentage for the labor activity (this will be averaged)
Column O has the utilization percentage for the employee that day (constant for each row being totaled)

There are no leading or trailing spaces for any data in Column A.

The code seems to be summing values several rows up, but not putting the sum in the Total row, and not for every value for a given user, and often overlapping across two different users. For example, the first sum value appears in I9 (summing values from I5-I8), but the range for that user is from I5 to I13. The sum value should for that range should appear in I14. The next range is from I15-I22 with the Total in I23, but the next actual sum value is in I16 (summing values in I14 and I15).

I wonder if I first need to insert code to clear contents of the Total rows and then re-add text for Total and proceed from there.

 

[JLGWhiz]

this could take a couple of minutes to run, but it should put the totals in the correct places. I suggest that you install the code, then while in the editor mode, use the F8 key to step through the code. You can then see what happens as each line of code executes and get a better idea of where any problems lie. I put a line in to average the values in column L but not sure what your figures look like in that column, so it could give erroneous results. I left out column N averages because I don't know what you a measuring there.

Sub t4()
Dim i As Long, lr As Long, st As Range, fl As Range
With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 1 Step -1
        If LCase(.Cells(i, 1)) = "total" Then
            Set st = .Cells(i, 2).End(xlUp).Offset(, 7)
            Set fl = .Cells(i - 1, 9)
            .Cells(i, 9) = Application.Sum(Range(st, fl))
            .Cells(i, 10) = Application.Sum(Range(st.Offset(, 1), fl.Offset(, 1)))
            .Cells(i, 11) = Application.Sum.Range(st.Offset(, 2), fl.Offset(, 2))
            .Cells(i, 12) = Application.Sum.Range(st.Offset(, 3), fl.Offset(, 3)) / .Cells(i, 11).Value 'This might need work
        End If
        Set st = Nothing
        Set fl = Nothing
    Next
End With
End Sub

This starts at the bottom and works upward.