Text to Columns selection and cell formatting

[TimvMechelen]

Hi all,

Currently I have the code below, which converts text to columns. I paste the text (with multiple rows) in cell A1 and run the macro.

Sub TextToColumns()
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(25, 1), Array(73, 1), Array(85, 1), _
        Array(88, 1), Array(104, 1)), TrailingMinusNumbers:=True
Columns("B:B").Select
    Selection.NumberFormat = "0.00"
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0" 


End Sub

I would like to have a macro that converts text to columns, no matter where I paste the text. Then I select the rows with text which needs to be converted to columns. The macro should convert the text within the selection. The second column should be formatted as standard.


So a practical example:
I paste text in cells B4 to B10. Then I select B4 to B10 and run the macro.
The text should be formatted to columns and cell C4 to C10 should be formatted as standard.

Thanks in advance.

 

[Fluff]

How about

Sub TextToColumns()
    Selection.TextToColumns Destination:=Selection, DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(25, 1), Array(73, 1), Array(85, 1), _
        Array(88, 1), Array(104, 1)), TrailingMinusNumbers:=True
    Selection.Offset(, 1).NumberFormat = "0"
End Sub

 

[TimvMechelen]

How about

Sub TextToColumns()
    Selection.TextToColumns Destination:=Selection, DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(25, 1), Array(73, 1), Array(85, 1), _
        Array(88, 1), Array(104, 1)), TrailingMinusNumbers:=True
    Selection.Offset(, 1).NumberFormat = "0"
End Sub

Thanks! it works!

 

[Fluff]

You're welcome & thanks for the feedback