Text To Column VBA

[Lewzerrrr]

Hi,

I need to input some data into Excel, the first column of the table can have leading zeros which I need to keep so I have to paste as text.

I can then manually data > text to columns to spread it into it's seperate fields which works absolutely fine.

I tried to see what record macro would do for this as I'd like to macro this task for my users but the code changes for each row and paste but I don't really understand the array part.

For the most part it's the exact same but some are different, do they need to be different?

    Selection.TextToColumns Destination:=Range("G3"), DataType:=xlFixedWidth, _        FieldInfo:=Array(Array(0, 2), Array(4, 1), Array(18, 1), Array(29, 1), Array(38, 1), _
        Array(49, 1), Array(62, 1), Array(72, 1)), TrailingMinusNumbers:=True


    Selection.TextToColumns Destination:=Range("G16"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(4, 1), Array(16, 1), Array(27, 1), Array(38, 1), _
        Array(49, 1), Array(62, 1), Array(72, 1)), TrailingMinusNumbers:=True


    Selection.TextToColumns Destination:=Range("G32"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(4, 1), Array(16, 1), Array(27, 1), Array(38, 1), _
        Array(49, 1), Array(62, 1), Array(72, 1)), TrailingMinusNumbers:=True


    Selection.TextToColumns Destination:=Range("G36"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(4, 1), Array(16, 1), Array(27, 1), Array(38, 1), _
        Array(49, 1), Array(60, 1), Array(72, 1)), TrailingMinusNumbers:=True


    Selection.TextToColumns Destination:=Range("G41"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(4, 1), Array(16, 1), Array(27, 1), Array(38, 1), _
        Array(49, 1), Array(62, 1), Array(72, 1)), TrailingMinusNumbers:=True


    Selection.TextToColumns Destination:=Range("G57"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(4, 1), Array(16, 1), Array(30, 1), Array(38, 1), _
        Array(49, 1), Array(62, 1), Array(72, 1)), TrailingMinusNumbers:=True

 

[MARK858]

I tried to see what record macro would do for this as I'd like to macro this task for my users but the code changes for each row and paste but I don't really understand the array part.

Have a read of the FieldInfo section under Remarks in the link below...

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-texttocolumns-method-excel