PDA

View Full Version : Loop through worksheet and create a new column based on cell values in another column



eriden
04-20-2015, 06:01 AM
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
04-20-2015, 06:50 AM
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
04-21-2015, 12:35 AM
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!! :) :bow: