Trying to automatically split data in excel with vba

Question

I have absolutely no experience programming in excel vba other than I wrote a function to add a data stamp to a barcode that was scanned in on our production line a few weeks back, mainly through trial and error.

Anyways, what I need help with right now is inventory is coming up and every item we have has a barcode and is usually scanned into notepad and then manually pulled into excel and "text to columns" is used. I found the excel split function and would like a little bit of help getting it to work with my scanned barcodes.

The data comes in in the format: 11111*A153333*11/30/11 plus a carriage return , where the * would be the delimiter. All the examples I've found don't seem to do anything, at all.

For example here is one I found on splitting at the " ", but nothing happens if I change it to *.

Sub splitText()

'splits Text active cell using * char as separator
Dim splitVals As Variant
Dim totalVals As Long

splitVals = Split(ActiveCell.Value, "*")
totalVals = UBound(splitVals)

Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
End Sub

And this is applied in the Sheet1 code section, if that helps.

It really can't be this complicated, can it?

Edit: Trying to add in Vlookup to the vba.

So as I said below in the comments, I'm now working on getting the vlookup integrated into this, however it just returns N/A.

Here is the sub I wrote based on the link below

Public Sub vlook(ByRef codeCell As Range)
Dim result As String
Dim source As Worksheet
Dim destination As Worksheet
Set destination = ActiveWorkbook.Sheets("Inventory")
Set source = ActiveWorkbook.Sheets("Descriptions")

result = [Vlookup(destination!(codeCell.Row, D), source!A2:B1397, 2, FALSE)]
End Sub

And I was trying to call it right after the For loop in the worksheet change, and just created another for loop, does this/should this be a nested for loop?

Answer 1

If you want to process the barcodes automatically on entering them, you need something like this (goes in the worksheet module).

Private Sub Worksheet_Change(ByVal Target As Range)

Dim splitVals As Variant
Dim c As Range, val As String

    For Each c In Target.Cells

        If c.Column = 1 Then 'optional: only process barcodes if in ColA
            val = Trim(c.Value)
            If InStr(val, "*") > 0 Then
                splitVals = Split(val, "*")
                c.Offset(0, 1).Resize( _
                   1, (UBound(splitVals) - LBound(splitVals)) + 1 _
                                       ).Value = splitVals
            End If
        End If 'in ColA

    Next c
End Sub

Answer 2

Just adding the code to the VBA behind the worksheet won't actually cause it to get called. You need to handle the worksheet_change event. The following should help:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim cell As Range
    For Each cell In Target.Cells
        If cell.Column = 1 Then SplitText cell
    Next
    Application.EnableEvents = True
End Sub

Public Sub SplitText(ByRef codeCell As Range)

    'splits Text active cell using * char as separator
    Dim splitVals As Variant
    Dim totalVals As Long

    splitVals = Split(codeCell.Value, "*")
    totalVals = UBound(splitVals)

    Range(Cells(codeCell.Row, codeCell.Column), Cells(codeCell.Row, codeCell.Column + totalVals)).Value = splitVals

End Sub