Home VBA Text To Colums + Loop Structure

# VBA Text To Colums + Loop Structure

user4809
1#
user4809 Published in September 19, 2018, 11:34 am

I'm working on a macro to do the following: In column "W" I have a description as xxx_yyy_zzz, I want to bring that information, separated by "_ " to different columns.

As per the code below, I've been able to do that for one line.

Step by Step what I'm trying to do is:

1) Copy cell on column W (I'll use cell "W2" for this example)

2) Go to Column B and paste it using TextToColums (delimited, the criteria would be the "_"):

B2 = xxx

C2 = yyy

D2 = zzz

3)Go back to colum W and do the same process for the next cell.(W3)

4) Do that Until the following cell on column W is blank.

5)PS: If cell on column B is not blank (has been previously populated), go back to the following cell on column W and do the same process.

My main problem is with the "Destination" (B6 in this case) that will vary each time I move from one line to another.

Sub Macro4()

Range("W6").Select
Selection.Copy

If ActiveCell.Offset(0, -21).Value = "" Then

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("B6"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1)), TrailingMinusNumbers:=True

Else

ActiveCell.Offset(1, 0).Select
End If

End Sub