CODEWITHSUNDEEP
vbaexcel
Gring
Gring

Reputation: 318

Text to Columns overwrites columns

I got another Problem with VBA. I used the TexttoColumns Sub to separate every Cell in my Column by semicolon. Now I wanted to insert Columns after that one, which should contain the separated values. It all worked pretty well in the beginning, but now it suddenly won't insert new cells, but overwrite the old ones.

Example (Wish) :

Row1           Row2   Row3   
Tree;PC;House |Data1 |Data2 -->  Tree|PC|House|Data1|Data2

Example(How it is):

Tree;PC;House|Data1|Data2 --> Tree|PC|House

Workbooks(Ziel).Worksheets(Zieltab).Columns(Spalte).TextToColumns Destination:=Columns(Spalte), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 2), Array(2, 2))

Upvotes: 1

Views: 2198

Answers (1)

user4039065
user4039065

Reputation:

A Range.TextToColumns method does not insert columns. It will always overwrite data if allowed to proceed.

With Workbooks(Ziel).Worksheets(Zieltab).Columns(Spalte)
    'insert two columns to the right
    .Cells(1).Resize(1, 2).Offset(0, 1).EntireColumn.Insert
    'split the first column into itself and the two new column
    .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
End With

Upvotes: 2

Related Questions