-2

I have a barcode scanner USB plug&play which is giving a string of data in one cell of Excel in this form 4449520450061198001 I want to split this data automatically in different cells everytime my scanner reads the code. Please help.

Regards,

user2439366
  • 7
  • 1
  • 1
  • Have you tried anything? Do you require every digit in a separate cell? Are the barcodes all the same length? – Alistair Weir May 31 '13 at 08:17
  • So you need to wire the code into an event? http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change If the scanner acts as a wedge does it add a new line on the end of the code? – Alex K. May 31 '13 at 10:05
  • Yes. i want macro to be run by an event. but how can i do that? everytime i scan new item, string goes to next cell down automatically. e.g. from A1 and then A2. – user2439366 May 31 '13 at 11:16
  • I have updated my answer to include an `event` – Alistair Weir May 31 '13 at 14:04

1 Answers1

2

UPDATED

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Const ws_range = "A1:A10"

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Integer, k As Integer
    Dim codestr As String

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")

    codestr = Target.Text
    If Target <> "" Then
        If Not Intersect(Target, Me.Range(ws_range)) Is Nothing Then
            With Target
                k = Len(codestr)
                i = 2
                Do Until i = k + 2
                    ws.Cells(Target.Row, i).Value = Mid(codestr, i - 1, 1)
                    i = i + 1
                Loop
            End With
        End If
    End If

End Sub

I haven't fully tested this but now after a value is inserted into column a it will be split into the cells to the right. Obviously modify A1:A10 to match what you need.

Alistair Weir
  • 1,809
  • 6
  • 26
  • 47
  • Hi, Thanks for reply I understand the code its working fine but how can i run this automatically? i want these values to go into cells every time my scanner reads the barcode automatically without pressing any button. Is it possible? Regards, – user2439366 May 31 '13 at 10:26