Consulting

Results 1 to 9 of 9

Thread: Take subheading and move to front of each row

  1. #1
    VBAX Regular
    Joined
    Sep 2020
    Posts
    27
    Location

    Take subheading and move to front of each row

    Hello,

    I'm looking to take the Branch# (which is listed as a subheading in column B), and apply it to each row in column A. Then when the macro comes across a different Branch# in column B it starts applying it to column A, and so on....

    Attached is a spreadsheet with two tabs, one is Before and the other is After. The Before is how it appears now (until I got stuck). After is what I need it to look like. With the corresponding Branch number next to its account information.

    Thanks so much and hopefully my request makes sense
    Attached Files Attached Files

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    In re After Sheet: Should A18 be CCP1 or CCP10. It appears to conflict with the layout of the last "Holdings" section on the Sheet.

    Branch F11, (Row 1) is a coding issue because it is neither preceded by or followed by a "Holding... " Header/Title. For that matter the second Section, Branch CCP!, (Row 13) has the same issue. Is it OK if the code starts running at the First "Holdings..." line (Row 18 in this example)?

    Another thing that is fairly easy, is to insert an empty Row above (Or below) each "Holdings..." Row.
    Last edited by SamT; 07-21-2021 at 01:28 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,946
    Location
    Something like this maybe

    Option Explicit
    
    
    Sub Branch()
        Dim r As Range, r1 As Range
        Dim s As String
        
        Set r = ActiveSheet.Cells(1, 2)
        Set r = Range(r, r.End(xlDown))
    
    
        For Each r1 In r.Cells
            If Left(r1.Value, 6) = "Branch" Then s = r1.Value
            r1.Offset(0, -1).Value = s
        Next
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    the OP hasn't said, but I think he/she is considering sorting the sheet by Branch number. I would use a combination of your sub (except use Offset(-1, -1)) and inserting an empty Row. Then one could use Find and CurrentRegion to perform all kinds of analysis.

    For example: filter Branch Reports to another sheet by any of: Branch Number, Representative, Account number, Financial Service, or Fund holder
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Sep 2020
    Posts
    27
    Location
    Quote Originally Posted by SamT View Post
    In re After Sheet: Should A18 be CCP1 or CCP10. It appears to conflict with the layout of the last "Holdings" section on the Sheet.

    Branch F11, (Row 1) is a coding issue because it is neither preceded by or followed by a "Holding... " Header/Title. For that matter the second Section, Branch CCP!, (Row 13) has the same issue. Is it OK if the code starts running at the First "Holdings..." line (Row 18 in this example)?

    Another thing that is fairly easy, is to insert an empty Row above (Or below) each "Holdings..." Row.
    As far as A18 being CCP1 or CCP10, it really doesn't matter. Ideally I just need the Branch numbers to appear in front of each corresponding Account number. What's in front of the headings isn't really a big deal. I'll be all for whatever is easier to code.

    Thanks

  6. #6
    VBAX Regular
    Joined
    Sep 2020
    Posts
    27
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Something like this maybe

    Option Explicit
    
    
    Sub Branch()
        Dim r As Range, r1 As Range
        Dim s As String
        
        Set r = ActiveSheet.Cells(1, 2)
        Set r = Range(r, r.End(xlDown))
    
    
        For Each r1 In r.Cells
            If Left(r1.Value, 6) = "Branch" Then s = r1.Value
            r1.Offset(0, -1).Value = s
        Next
    End Sub
    Yes, this would work.

    Much appreciated

    Thanks!!

  7. #7
    VBAX Regular
    Joined
    Sep 2020
    Posts
    27
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Something like this maybe

    Option Explicit
    
    
    Sub Branch()
        Dim r As Range, r1 As Range
        Dim s As String
        
        Set r = ActiveSheet.Cells(1, 2)
        Set r = Range(r, r.End(xlDown))
    
    
        For Each r1 In r.Cells
            If Left(r1.Value, 6) = "Branch" Then s = r1.Value
            r1.Offset(0, -1).Value = s
        Next
    End Sub
    Now, to take this one step further, I also need to pull the Reps in front of each column. My plan was to use the same logic as you posted above, but it's a little more tricky. I see what you did with the "Left" function, looking at the first 6 letters that contains "Branch". The reps are located in cells B2, B14, B20, etc... Basically everywhere in column B that has someone's name needs to go in front of their corresponding account information, much like we did for the Branch number. So we can probably just insert a new blank row (column B), and list the Reps going down.

    This is probably a little trick since the Left function wouldn't work here with each name being different. The only unique identifier - that's common in each Rep name - is in the middle of the text: , Rep #

    I assume there's some code that can look for that unique text within each cell, then just copy the reps down just like we did for the Branch numbers.

    Thanks again.

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,946
    Location
    Not exactly sure what you were asking for, but here's one way


    Option Explicit
    
    
    
    
    Sub BranchRep()
        Dim r As Range, r1 As Range
        Dim sBranch As String, sRep As String
        
        Set r = ActiveSheet.Cells(1, 3)
        Set r = Range(r, r.End(xlDown))
    
    
    
    
        For Each r1 In r.Cells
            If Left(r1.Value, 6) = "Branch" Then sBranch = r1.Value
            r1.Offset(0, -2).Value = sBranch
        Next
    
    
        For Each r1 In r.Cells
            If InStr(r1.Value, ", Rep") > 0 Then sRep = r1.Value
            r1.Offset(0, -1).Value = sRep
        Next
    
    
        For Each r1 In r.Cells
            If Not IsNumeric(r1.Value) Then r1.Offset(0, -2).Resize(1, 2).ClearContents
        Next
    
    
    End Sub
    Attached Images Attached Images
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    VBAX Regular
    Joined
    Sep 2020
    Posts
    27
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Not exactly sure what you were asking for, but here's one way


    Option Explicit
    
    
    
    
    Sub BranchRep()
        Dim r As Range, r1 As Range
        Dim sBranch As String, sRep As String
        
        Set r = ActiveSheet.Cells(1, 3)
        Set r = Range(r, r.End(xlDown))
    
    
    
    
        For Each r1 In r.Cells
            If Left(r1.Value, 6) = "Branch" Then sBranch = r1.Value
            r1.Offset(0, -2).Value = sBranch
        Next
    
    
        For Each r1 In r.Cells
            If InStr(r1.Value, ", Rep") > 0 Then sRep = r1.Value
            r1.Offset(0, -1).Value = sRep
        Next
    
    
        For Each r1 In r.Cells
            If Not IsNumeric(r1.Value) Then r1.Offset(0, -2).Resize(1, 2).ClearContents
        Next
    
    
    End Sub

    Yes! that's perfect.

    Thank you so much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •