Barcodes and VBA

[JeremySun]

I was wondering if there was a way to scan a barcode into excel, the output is shown like:
]L2NEJ8G2DS121P7SGJeremy Smith AT4ICA00

Can I have a vba to delete the first 18 characters then copy characters 19-38 and place those in sheet1(A2) then take characters 39-62 and copy those into sheet1(b2).And then clear all the empty spaces out...

 

[Smitty]

You could use VBA, or just formulas:


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">]L2NEJ8G2DS121P7SGJeremy Smith AT4ICA00 </td><td style=";">Jeremy Smith AT4ICA00 </td><td style=";"> </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=MID(<font color="Blue">A1,19,38</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=RIGHT(<font color="Blue">A1,LEN(<font color="Red">A1</font>)-39</font>)</td></tr></tbody></table></td></tr></table><br />

You could apply the same Left, Mid & Right methodology in VBA.

HTH,

 

[JeremySun]

what would that be? I'm very new to vba

 

[Smitty]

Sorry, I had to repost because the formulas didn't come through the first time.

This is how you'd mimic that in VBA:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#00007F">Set</SPAN> rng = Range("A1")<br>    <br>    Range("B1").Value = Mid(rng, 19, 38)<br>    Range("C1").Value = Right(rng, Len(rng) - 38)<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[JeremySun]

Could I also use this on a userform.. If I create a textbox named (scan) and two more named (Firstname) and (LastName)?

 

[Smitty]

I don't see why not, although the question would be if you could actually have the barcode input into a UF, which I don't think is likely.

 

[djreiswig]

Can I have a vba to delete the first 18 characters then copy characters 19-38 and place those in sheet1(A2) then take characters 39-62 and copy those into sheet1(b2).And then clear all the empty spaces out...

Your example must not have posted right. I only count 39 characters.

This would be a userform example of what Smitty posted.

Private Sub scan_Change()
    firstname.Text = Mid(scan.Text, 19, 38)
    lastname.Text = Right(scan.Text, Len(scan.Text) - 38)
End Sub

 

[JeremySun]

What I did was made a textbox for the scan to go into then used the code mentioned to break up the scanned information. All done within the UF