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
– Luuklag, Commented May 1, 2018 at 7: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
– Luuklag, Commented May 1, 2018 at 7: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
– Martin Scott, Commented May 1, 2018 at 8:05
Are you sure the second row in your expected results isn't missing a single character in the last portion? — user4039065
– user4039065, Commented May 1, 2018 at 8:17
@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
– FreeMan, Commented May 1, 2018 at 11:22

Luuklag · Accepted Answer · 2018-05-01 08:27:44Z

1

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

edited May 1, 2018 at 8:27

answered May 1, 2018 at 7:46

Luuklag

3,91412 gold badges40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Scott Over a year ago

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.

Luuklag Over a year ago

I'm happy to help

Collectives™ on Stack Overflow

Excel VBA Text To Columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related