Capture the first 10 number only VBA in xl07

[CSacles]

Does anyone know a good VBA for capturing the first 10 character in a cell. I'm scanning a barcode that has 16 character, but I only need the first 10 in cell A2 and the other 6 in B2.

Any advice?

 

[VoG]

Try like this

Sub test()
Dim s As String
s = Range("A2").Value
Range("A2").Value = Left(s, 10)
Range("B2") = Right(s, 6)
End Sub

 

[lenze]

Hi and welcome to the board!!!

I'm not really sure, but I believe scanning a bar code will trigger a Change event. If so, you can use this in the Worksheet module. RightClick the sheet tab and choose "View Code"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target = Left(Target, 10)
Cells(Target.Row, 2) = Right(Target, 6)
Application.EnableEvents = True
End Sub

lenze

 

[jasonb75]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
Target.TextToColumns Target, xlFixedWidth, , , , , , , , , Array(Array(0, 1), Array(10, 1))
End Sub

Not tested it extensively but looks like it works ok, just needs restricting to target range as lenze has done with his suggestion.

 

[CSacles]

Man, thank you all for the quick feedback. I will try them out and see which works best....

Thanks again

 

[CSacles]

Ok, here is the code that I am using. I cannot seem to get any of these to work with my preexisting code?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then
    Cells(Target.Row, 13).Value = Now
    
    End If
     If Target.Column = 11 Then
    Cells(Target.Row, 15).Value = Now
    
    End If
    
If Target.Address = "$X$1" Then
 Range("A200").Select
End If
If Target.Address = "$A$200" Then
 Range("B200").Select
End If
If Target.Address = "$B$200" Then
 Range("C200").Select
End If
If Target.Address = "$C$200" Then
 Range("K200").Select
 
 End If
 
If Not Intersect(Target, Range("X:X")) Is Nothing Then
    Range("A" & Target.Row + 1).Select
ElseIf Not Intersect(Target, Range("A:A")) Is Nothing Then
    Target.Offset(0, 1).Select
    ElseIf Not Intersect(Target, Range("B:B")) Is Nothing Then
    Target.Offset(0, 1).Select
ElseIf Not Intersect(Target, Range("C:C")) Is Nothing Then
    Target.Offset(0, 1).Select
ElseIf Not Intersect(Target, Range("D:D")) Is Nothing Then
    Target.Offset(0, 2).Select
    ElseIf Not Intersect(Target, Range("E:E")) Is Nothing Then
    Target.Offset(0, 1).Select
    ElseIf Not Intersect(Target, Range("F:F")) Is Nothing Then
    Target.Offset(0, 2).Select
    ElseIf Not Intersect(Target, Range("H:H")) Is Nothing Then
    Target.Offset(0, 3).Select
End If

End Sub

 

[CSacles]

Okay, I had it in the wrong location.... Thanks guys