Excel VBA Text To Columns

Question

Good Morning and thanks in advance, I am trying to run a VBA macro to enable my end users to scan barcodes into excel then using a command button split the barcode into its component types using the text to columns vba macro, which then breaks it down to Item, 1st page and last page in box. Due to the fact that there are 3 different lengths of barcode that have to be split at different places, as a stopgap measure I have created 3 tabs in the workbook so that they can scan and breakdown the item. Ideally I would like to be able to do this all on 1 worksheet and have the coding recognise what needs to be split and where. Below is the coding that I have got to work for one of the items, but it only recognises the 1st set of arrays, not the subsequent 3.Can anyone tell me how I can add extra arrays to get the different barcodes to split, in the correct positions

Sub BarcodeSplit()
On Error GoTo myEnd:

    ' BarcodeSplit Macro
    '
    ' Keyboard Shortcut: Ctrl+b
    '
        ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
        ActiveCell.Select
        ActiveCell.FormulaR1C1 = "Item"
        ActiveCell.Offset(1, -1).Range("A1").Select
        Range("B2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.TextToColumns Destination:=Range("B2"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 2), Array(10, 2), Array(17, 2), Array(28, 2)), _
            FieldInfo:=Array(Array(0, 2), Array(10, 2), Array(17, 2), Array(28, 2)), _
            FieldInfo:=Array(Array(0, 2), Array(14, 2), Array(21, 2), Array(32, 2)), _
            FieldInfo:=Array(Array(0, 2), Array(13, 2), Array(20, 2), Array(31, 2)), _
            TrailingMinusNumbers:=True
    myEnd:
End Sub

Below are an example of the barcodes and then the how the text to column split would look

FP10SS200011915113111022001131110240004 FP10ss2000  1191511 31110220011 31110240004
FP10D400000031256232508001662325120000  FP10D40000  0031256 23250800166 2325120000
FP10MDA-SS050000207496320374001463203745000 FP10MDA-SS05    0020749 63203740014 63203745000
FP10PCDSS050000005566801250501068012510006  FP10PCDSS0500   0000556 68012505010 68012510006

Thanks in advance again guys. Martin

Hi Martin, thanks for your question. Are the lengths of the barcodes always exactly the same? If that is the case you can simply use `IF` logic to determine which code you need to run based on the `Len()` of your input. The simplest solution would be to replicate your code 3 times, and surround that with IF statements. — Luuklag, May 01 '18 at 07:34
Also I see you made this macro with the record a macro. That is a great way to start learning how to write VBA code. There are unfortunately a lot of lines that you don't need. These are most of the `ActiveCell` lines. — Luuklag, May 01 '18 at 07:36
Hi Luuklag, thanks for that. I did try to use the if and len() statements but I kept getting errors as it would not recognise them. Am I right in thinking that at the end of each array I would ujse "If Len()=39 then go to" — Martin Scott, May 01 '18 at 08:05
Are you sure the second row in your expected results isn't missing a single character in the last portion? — , May 01 '18 at 08:17
@MartinScott I see I missed my `Then`'s in the code, Ill add them in. — Luuklag, May 01 '18 at 08:27
@MartinScott - as Luuklag said, the macro recorder is a _great_ way to get started and figure out the general _how_ to make Excel do your dance. However, it writes _terrible_ code! It's functional, but terrible. There are _lots_ of Q&As here on how to avoid all the `.Activate` and `.Select` statements, and they'll make your code much more robust, easier to read, and execute more quickly. Keep reading, studying & learning, and soon you'll only the the macro recorder to figure out exactly what that color combination is that you're trying to apply to some text or cell! (Those are still weird...) — FreeMan, May 01 '18 at 11:22

Luuklag · Accepted Answer · 2018-05-01T08:27:44.223

Your code would look something like this, if you'd implement my comments.

Dim SelectedRange as Range
Dim Cell as Range

Range("B2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Set SelectedRange = Application.Selection
        For each Cell in SelectedRange
            If len(Cell) = 39 Then   'length of first barcode Then
                Cell.TextToColumns Destination:=Range(Cell), DataType:=xlFixedWidth, _
                FieldInfo:=Array(Array(0, 2), Array(10, 2), Array(17, 2), Array(28, 2)), _

                TrailingMinusNumbers:=True

            ElseIf len(Cell) = ?? Then'length second barcode Then
                Cell.TextToColumns Destination:=Range(Cell), DataType:=xlFixedWidth, _
                FieldInfo:=Array(   'Enter array here

                TrailingMinusNumbers:=True

            ElseIf len(Cell) = ?? Then'length third barcode Then
                Cell.TextToColumns Destination:=Range(Cell), DataType:=xlFixedWidth, _
                FieldInfo:=Array(   'Enter array here

                TrailingMinusNumbers:=True
            End If
         Next Cell
    myEnd:
    End Sub

Thank You Luuklag, that works perfectly, i can now see what I was doing wrong and where i need to improve. I am learning all the time, and it is thanks to you guys that I am finding it easier each day. — Martin Scott, May 01 '18 at 08:27

Excel VBA Text To Columns

1 Answers1