VBA to Automate the Delimiter

skidda420

New Member
Joined
Jun 7, 2018
Messages
26
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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’)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 

Attachments

  • app snip.JPG
    app snip.JPG
    84.1 KB · Views: 19
Upvote 0
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. (y)
 
Upvote 0
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
 
Upvote 0
Here is another possibility

VBA Code:
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
Rich (BB code):
[Z4] = Split([B4], "*")(0)
[AA4] = Split([B4], "*")(1)
[AB4] = Split([B4], "*")(2)
[Z4:AB4] = Split([B4], "*")
 
Last edited:
Upvote 0
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.
 
Upvote 0
Here is another possibility

VBA Code:
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
Rich (BB code):
[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.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top