3

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?

Improve this question
4
  • I've just run your code against the test string you specified and it worked fine - filled the next three cells with the split values. Commented Nov 30, 2011 at 17:29
  • Where did you put the code? Maybe that's my problem? I put the code into the sheet1 code. When I open it up (Alt+F11) I just paste that little Sub in there and when I scan with my barcode scanner, it does nothing but newline down to the next row. Commented Nov 30, 2011 at 17:34
  • You need to either run the code (for example, as a macro), or create some sort of worksheet function based on the logic. Commented Nov 30, 2011 at 17:37
  • Alright so going off of phoog's comment I changed the sub to Private Sub Worksheet_Change(ByVal Target as Range) which didn't work, but if i choose the Worksheet_SelectionChange() it works when I select the column. What the hell. Commented Nov 30, 2011 at 17:52

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Jon, that works just as I wanted, well almost. Any quick edits to not leave the bar code in the first column? So that 15242*A133401*11/30/13 becomes 15242, but the rest carries over to the next columns?
See amended code above. The Statis inProgress is there to stop everything looping (as you're editing column 1 again).
Thank you sir, that will do it!
So logically moving forward, they now want to add a Vlookup description look-up, which i can do any day of the week, but it doesn't want to work at all with the vba enabled. Is there a trick to getting it to work? Right now it just returns #N/A, and I'm assuming it's because it's doing the look-up on the actual bar code and not the stripped down value. I tried searching on here, and did find this but can't get it to play nice with the other function. Thanks again
An alternative to using a Static Boolean to stop the endless loop of updates is to add the line Application.EnableEvents = False at the beginning of the Worksheet_Change procedure and then to turn events back on at the end.
|
2

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
Improve this answer

1 Comment

I think the reference to ActiveCell.Value needs to be changed to val.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.