Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
LPowell911

SUM Multiple Section

Question

Greetings,

 

I have a report ran for me which dumps into a .xls into several different sections.  Each section has a name in column B, a route in the following row in column C, and data that begins to dump in the next row Columns D through F which are time planned, time actual, and location.  Each section goes about 200 +/- rows of these times and locations.  After each section, there are about 5 blank rows before the next name and route section information.  I got a friend to help me with a macro to count each time a certain timed event happens.  These Counts occur in columns H, I , and J.  One count is for early, one count for late, and one count for each stop.

 

But now I want to add up the number of times it happens for each name and route.  I am using SUM function to add up each section.  The total is in the same row as the Route. I have learned about the INDEX to Next Blank, but am not sure how to write the macro for the formula to go instead of the SUM function I am typing in each cell.  I want to continue this report to run and sum up each section (for each driver and route that day).

 

  B C D E F   H I J K
  John Doe           Early Late Stops Pct
    Route 1         1 3 5 20.00%
      10:00 AM 10:01 AM 123 Main Street     1 1  
      10:15 AM 10:14 AM 246 Main Street   1   1  
      10:30 AM 10:30 AM 123 3rd Avenue       1  
      10:45 AM 10:50 AM 247 3rd Avenue     1 1  
      11:00 AM 11:10 AM 395 3rd Avenue     1 1  
      About Five Lines of Blank Rows              
  Jane Done         Early Late Stops Pct
    Route 2         2 1 5 40.00%
      10:00 AM 10:01 AM 100 Cherry St     1 1  
      10:15 AM 10:14 AM 210 Cherry St   1   1  
      10:30 AM 10:30 AM 330 Cherry St       1  
      10:45 AM 10:44 AM 1000 3rd Avenue   1   1  
      11:00 AM 11:00 AM 1100 3rd Avenue       1  
                     

 

Thanks in advance for all help.

 

Edited by LPowell911
  • Like 1

Share this post


Link to post
Share on other sites

8 answers to this question

Recommended Posts

  • 0

Hello, 

 

Thanks for signing up and posting your question! :D

 

If i have understood you correctly, what you wish to do via VBA is:

  • For each Route listed in Column C
  • Sum the total occurences of the Early (Col H), Late (Col I) and Stops (Col J) values in that block
  • Move down the sheet, completing this sum, until all blocks have been summed

If so, this code should do just that. 

Sub Expand_Selection_and_SUM()
    Dim ws As Worksheet
    Dim lr As Long
    Dim r1 As Range, r2 As Range
    Dim Opt As String
    
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    lr = ws.Range("F" & Rows.Count).End(xlUp).Row
    Set r1 = ws.Range("F1").End(xlDown)
    Opt = "Do"
    Do
        If Opt = "Do" Then
            Set r2 = r1.End(xlDown)
            ws.Range("H" & r1.Row).Offset(-1).Value = "=Sum(H" & r1.Row & ":H" & r2.Row & ")"
            ws.Range("I" & r1.Row).Offset(-1).Value = "=Sum(I" & r1.Row & ":I" & r2.Row & ")"
            ws.Range("J" & r1.Row).Offset(-1).Value = "=Sum(J" & r1.Row & ":J" & r2.Row & ")"
            Set r1 = r2
            Opt = "Skip"
        ElseIf Opt = "Skip" Then
            Set r2 = r1.End(xlDown)
            Set r1 = r2
            Opt = "Do"
        End If
        If r2.Row > lr Then Exit Do
    Loop
    Application.ScreenUpdating = True
End Sub

If the code does not achieve the desired result, please let me know.

 

Many Thanks

Caleeco

Share this post


Link to post
Share on other sites
  • 0

I will have about 23-28 sections, depending on the day of the week.  Do I need to set all 23-28 ranges?  Or will this just loop until it finds the complete end?

Share this post


Link to post
Share on other sites
  • 0
1 hour ago, LPowell911 said:

I will have about 23-28 sections, depending on the day of the week.  Do I need to set all 23-28 ranges?  Or will this just loop until it finds the complete end?

 

It should just loop through all the sections of the Activesheet, add the SUM formulas, and end the loop when it reaches the end of the data range!

 

Test it and let me know how you get on :D

 

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Unfortunately, it didn't work :(.  The sheet looks the same.

 

Just an FYI, I referenced the incorrect columns (forgot a couple from memory).  I changed the code to reflect accordingly still with no result.

 

Could I send you the test file so you can see a little more in detail?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×