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?

Answers


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

Need Your Help

Iphone: save chekmark position in Check List

iphone objective-c ios uitableview nsuserdefaults

Trying to implement settings in my app. What I need is a check list. I used

Uploading a video to youtube from a ruby on rails application

ruby-on-rails youtube-api

Is there any gem to directly upload a youtube video from a Rails Web app? Can you point me out to that gem or is there any good tutorials explaining how to do this?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.