VBA to Automate the Delimiter

[skidda420]

I'm attempting to find a solution to automate the delimiter and I've scoured the forums and internet to no resolve and was hoping I may be able to find an answer here. Right now I am having excel generate a barcode in the example format of "11D*122*1", each identifying number is split by the asterisk. Then in the next process an end user will scan that barcode into the B4 field. Each number will change when a scan is entered into cell B4. Right now I am doing text to cell function so that it breaks it down and separates them in cells Z4:AB4 but I am looking for a way to automatically do this as this process will be for end users. Trying to find a way to do this that as soon as the data is entered it separates it without having to use a macro button. Any and all help would be appreciated.

 

[Peter_SSs]

I assume that currently you have a macro to this but you have to run it manually?
If so, could we see your existing code please?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[skidda420]

no I do not have any VBA or macro assigned to this , I am literally having to manually put in the data then going to text to columns and separating it through that.

 

[Peter_SSs]

OK, so when the barcode hits B4, you want it split to Z4:AB4?

Then when the next barcode is scanned - where does it go and where do you want that split to?

 

[skidda420]

so yes, the barcode gets scanned into B4 as 3 data inputs separated by an asterisk (or hyphen, either or) then the first input goes into cell Z4, the second digit goes into AA4 and the third digit goes into AB4

 

[Peter_SSs]

so yes, the barcode gets scanned into B4 as 3 data inputs separated by an asterisk (or hyphen, either or) then the first input goes into cell Z4, the second digit goes into AA4 and the third digit goes into AB4

Thanks, what are the answers to my other two (combined) questions?

Then when the next barcode is scanned - where does it go and where do you want that split to?

From your image, I am guessing that the next barcode also goes to B4 but would like you to confirm that or advise otherwise.

The second part of my question then would be do the new values
- over-ride the old ones in Z4:AB4, or
- go in A4:AB4 but push the old ones down a row, or
- leave the old ones in A4:AB4 and put the new ones in the next available row in Z:AB (ie row 5 then next to row 6 etc), or
- something else?

Also, thanks for updating your profile.

 

[skidda420]

I think I may have found a solution, it replaces the old values every time the cell B4 changes with the new value. I'm going to try this and see if it does the desired effect.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4")) Is Nothing Then
If [B4] = vbNullString Then Exit Sub
[Z4] = Split([B4], "*")(0)
[AA4] = Split([B4], "*")(1)
[AB4] = Split([B4], "*")(2)
End If
End Sub

 

[Peter_SSs]

Here is another possibility

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B4")) Is Nothing Then
    Application.EnableEvents = False
    Range("Z4:AB4").ClearContents
    If Len(Target.Value) > 0 Then Target.TextToColumns Destination:=Range("Z4"), DataType:=xlDelimited, Other:=True, OtherChar:="*"
    Application.EnableEvents = True
  End If
End Sub

If you decide to stick with your code, then at least add the two 'EnableEvents' lines from my code. Otherwise, when you write the value to Z4 you will unnecessarily re-call this event code, same when you write a value to AA4 and then again for AB4.

Also, if staying with your method & syntax, this change would be a lot simpler

[Z4] = Split([B4], "*")(0)
[AA4] = Split([B4], "*")(1)
[AB4] = Split([B4], "*")(2)
[Z4:AB4] = Split([B4], "*")

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA to Automate the Delimiter
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[skidda420]

Here is another possibility

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B4")) Is Nothing Then
    Application.EnableEvents = False
    Range("Z4:AB4").ClearContents
    If Len(Target.Value) > 0 Then Target.TextToColumns Destination:=Range("Z4"), DataType:=xlDelimited, Other:=True, OtherChar:="*"
    Application.EnableEvents = True
  End If
End Sub

If you decide to stick with your code, then at least add the two 'EnableEvents' lines from my code. Otherwise, when you write the value to Z4 you will unnecessarily re-call this event code, same when you write a value to AA4 and then again for AB4.

Also, if staying with your method & syntax, this change would be a lot simpler

[Z4] = Split([B4], "*")(0)
[AA4] = Split([B4], "*")(1)
[AB4] = Split([B4], "*")(2)
[Z4:AB4] = Split([B4], "*")

yeah, this is a nice clean function. Thank you so much for the help and input. =) You guys are life savers.