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
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