CODEWITHSUNDEEP
vbaexcel
user7389963
user7389963

Reputation:

Run recorded Text to Column code on selected cell

I am trying to run the Text to Column function. I recorded a macro and it appears to work for the first attempt in the cell I recorded the macro for. I need this macro to run for any cells I select so I changed the range to "ActiveCell" but this seems to break the code.

Recorded Macro:

Selection.TextToColumns Destination:=Range("X32"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
End Sub

Upvotes: 1

Views: 1310

Answers (2)

Shai Rado
Shai Rado

Reputation: 33682

In order for the TextToColumns to work, I hope you selected a single column as the source for this funtion to work, i.e selecting cells "E5:E10" will work. Selecting multiple columns as an Input will result with a run-time error.

Documentation link MSDN TextToColumns

Code

Sub TxtToCol()

Dim DestRng     As Range

' setting the destination range using a variable
Set DestRng = Range("X32")

With Selection
    .TextToColumns Destination:=DestRng, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=True, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
End With

End Sub

Upvotes: 0

user3598756
user3598756

Reputation: 29421

Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

works for me when I select a cell whose text have semicolons and it gets split into many columns

Upvotes: 1

Related Questions