Loop through worksheet and create a new column based on cell values in another column

[eriden]

Hello!
I would like to create a macro in VBA but I'm really struggling. I need the macro to do the following:

• Go through the entire column A and check whether it has a cell that has a length of 4. If this is true then:

• Grab the value from the same row in column B. Also add values from the next rows in column B using " | " as a seperator until either:

* The cell.value in Column B is empty OR
* The length of that row in Column A has 4 numbers.

• Continue checking for length in Column A until it has looped through the entire worksheet.

I know this is a lot to ask, but I would be for ever grateful if someone could help me with this.
I have managed to get the macro to loop through A and check for cell length, but that's kind of where my knowledge stops.
Thanks a lot in advance! :-)


When finished I would like the macro to create column C (Based on Column B) as follows:

A	B	C
1234	Text1	Text1 | Text2 | Text3
	Text2
	Text3
2345	Text1	Text1 | Text2 | Text3 | Text4
	Text2
	Text3
	Text4
3456	Text1	Text1 | Text2
	Text2
4567	Text1	Text1 | Text2 | Text3
	Text2
	Text3

[Paul_Hossler]

I'd try something like this

 
Option Explicit
Sub Join_A_and_B()
    Dim iLastRow As Long, iA As Long, iB As Long
    Dim sTemp As String
    
    With ActiveSheet
    
        iLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For iA = 1 To iLastRow
    
            If Len(.Cells(iA, 1).Value) = 4 Then
                
                sTemp = .Cells(iA, 2).Value
                iB = iA + 1
            
                Do While Len(.Cells(iB, 1).Value) = 0 And Len(.Cells(iB, 2).Value) > 0
                    sTemp = sTemp & " | " & .Cells(iB, 2).Value
                    iB = iB + 1
                Loop
                        
                .Cells(iA, 3).Value = sTemp
            End If
        Next iA
    End With
    
End Sub

[eriden]

I'd try something like this

 
Option Explicit
Sub Join_A_and_B()
    Dim iLastRow As Long, iA As Long, iB As Long
    Dim sTemp As String
    
    With ActiveSheet
    
        iLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For iA = 1 To iLastRow
    
            If Len(.Cells(iA, 1).Value) = 4 Then
                
                sTemp = .Cells(iA, 2).Value
                iB = iA + 1
            
                Do While Len(.Cells(iB, 1).Value) = 0 And Len(.Cells(iB, 2).Value) > 0
                    sTemp = sTemp & " | " & .Cells(iB, 2).Value
                    iB = iB + 1
                Loop
                        
                .Cells(iA, 3).Value = sTemp
            End If
        Next iA
    End With
    
End Sub

Works like a charm. Thank you so much Paul!!