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.
If ActiveCell.Offset(0, -21).Value = "" Then
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
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
excel vba excel-vba loops