Take subheading and move to front of each row

[twmills]

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

[SamT]

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.

[Paul_Hossler]

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

[SamT]

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

[twmills]

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

[twmills]

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!!

[twmills]

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.

[Paul_Hossler]

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

[twmills]

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