Trying to automatically split data in excel with vba
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?
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
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