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.

 

[JLGWhiz]

If you post the code you have developed, it will help to offer a suggestion on how to get the sum of only one indvidual at a time. I am pretty sure you can use the End(xlUp) with an offset to return the correct range to sum, but it would be easier to illustrate using your code.

 

[Fazza]

Maybe like the standard SubTotal functionality?

You can see these if you select a cell in the data and then ALT-D-B

 

[JLGWhiz]

Maybe like the standard SubTotal functionality?

You can see these if you select a cell in the data and then ALT-D-B

Could be, but it is difficult to tell from the OP.

 

[jolivanes]

How many columns need totals?
Is your top row a header row?

 

[jolivanes]

In the meantime you could try this on a copy of your workbook (if I understood your requirement right).

Sub Sum_Blocks_Of_Same_Values()
    Dim lr As Long, j As Long, lst As Range, a As String
    lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    j = 2
    Do Until Cells(j, 9).Value = ""
        a = Cells(j, 1).Value
        Set lst = Range("A:A").Find(a, After:=Cells(1, 1), SearchDirection:=xlPrevious, LookAt:=xlWhole)
        Cells(lst.Row + 1, 9).Value = WorksheetFunction.Sum(Range(Cells(j, 9), Cells(lst.Row, 9)))
        j = lst.Row + 2
    Loop
End Sub

 

[FrenchCelt]

If you post the code you have developed, it will help to offer a suggestion on how to get the sum of only one indvidual at a time. I am pretty sure you can use the End(xlUp) with an offset to return the correct range to sum, but it would be easier to illustrate using your code.

Here is a basic mockup of what the data looks like (I don't have the ability to use file sharing sites from work) so you have an idea of what I'm seeing:

Report Title and General Info in Rows 1-3
Headers in Row 4 and data below (user1 in Rows 5-7 and user2 in Rows 8-11, Name is Column A and Qty is Column I, but I put them side by side for simplicity):

Name |Qty|
user1 |28|
user1 |15|
Total |43|
user2 |25|
user2 |34|
user2 |15|
Total |74|

So basically I need to be able to sum the Qty values for user1 and user2 (the number of users will vary each day) in the Total row. But since the number of rows per user will vary depending on how many different entries they have for Labor Activity, I can't use a constant offset from the Total row.

So right now, the only code I have is basically:

Dim fnd As Range
Set fnd = Range("A:A").Find("Total", , , xlWhole, , , False, , False)
fnd.Offset(, 8).FormulaR1C1 = "=sum(unknown)"

I assume even this might not end up being right given the circumstances, but I lifted it from another macro I have that does a formula based on cells offset from an anchor point using specific text in a given cell in a given column.

I hope this helps clarify what I'm trying to do.

 

[jolivanes]

So what happened when you changed the

j = 2

to

j = 5

in the suggestion in Post #6 ?

 

[FrenchCelt]

So what happened when you changed the

j = 2

to

j = 5

in the suggestion in Post #6 ?

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?

 

[jolivanes]

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.