Home VBA Text To Colums + Loop Structure
Reply: 0

VBA Text To Colums + Loop Structure

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()


    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


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

End Sub
share|improve this question

2 Answers 2

active oldest votes
up vote 3 down vote
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.446233 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO