Foro Excel

Buenas tardes, tengo esta macro en la que ingreso con una maquina para escanear diferentes codigos de barras, resulta que el valor que me ingresa la maquinita puede ser:
12345
04566
q6712345q
q6702345q
q6700345q

entonces tiene que ir con ese numero a buscar en la base de datos el numero exacto, pero en la base de datos lo que yo tengo para cada ingreso respectivamente es:

12345
4566 (sin el 0)
12345 (sin q y sin 67)
2345
345

en las dos ultimas de nuevo sin los 0, por lo que hice la macro que adjunto!! Eso funciona!! Sin embargo el error que tengo es que existen ciertos codigos que NO estan en la base de datos y sin embargo no me envia el mensaje de error que puse al final...

Podrian por favor ayudarme????

Desde ya muchas gracias

Private Sub Z_saisie_AfterUpdate()

Dim Valeur As String
Dim r As Range

Debug.Print Z_saisie.Value 'codigo poitrine de 5 cifras
Valeur = Z_saisie.Value


If Trim(Valeur) <> "" Then
With Feuil1.Range("A:L")
Set r = .Find(What:=Valeur, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not r Is Nothing And ob_entrée.Value = True Then

Feuil6.Select
Range("C3:D3").Select
Selection.Copy

Application.Goto r, True

ActiveCell.Offset(0, 3).Select

ActiveSheet.Paste

End If


If Not r Is Nothing And ob_sortie.Value = True Then

Feuil6.Select
Range("C3:D3").Select
Selection.Copy

Application.Goto r, True

ActiveCell.Offset(0, 7).Select

ActiveSheet.Paste

End If

If r Is Nothing Then

Z_saisie.Value = Mid(Z_saisie, 2, 7) 'codigo poitrine de 4 cifras

Debug.Print Z_saisie.Value
Valeur = Z_saisie.Value


If Trim(Valeur) <> "" Then
With Feuil1.Range("A:L")
Set r = .Find(What:=Valeur, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not r Is Nothing And ob_entrée.Value = True Then

Feuil6.Select
Range("C3:D3").Select
Selection.Copy

Application.Goto r, True

ActiveCell.Offset(0, 3).Select

ActiveSheet.Paste

End If


If Not r Is Nothing And ob_sortie.Value = True Then

Feuil6.Select
Range("C3:D3").Select
Selection.Copy

Application.Goto r, True

ActiveCell.Offset(0, 7).Select

ActiveSheet.Paste

End If

If r Is Nothing Then

Z_saisie.Value = Mid(Z_saisie, 3, 5) 'codigo poignet de 5 cifras

Debug.Print Z_saisie.Value
Valeur = Z_saisie.Value


If Trim(Valeur) <> "" Then
With Feuil1.Range("A:L")
Set r = .Find(What:=Valeur, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not r Is Nothing And ob_entrée.Value = True Then

Feuil6.Select
Range("C3:D3").Select
Selection.Copy

Application.Goto r, True

ActiveCell.Offset(0, 3).Select

ActiveSheet.Paste

End If


If Not r Is Nothing And ob_sortie.Value = True Then

Feuil6.Select
Range("C3:D3").Select
Selection.Copy

Application.Goto r, True

ActiveCell.Offset(0, 7).Select

ActiveSheet.Paste

End If

If r Is Nothing Then

Z_saisie.Value = Mid(Z_saisie, 2, 5) 'codigo poignet 4 cifras 1382

Debug.Print Z_saisie.Value
Valeur = Z_saisie.Value


If Trim(Valeur) <> "" Then
With Feuil1.Range("A:L")
Set r = .Find(What:=Valeur, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not r Is Nothing And ob_entrée.Value = True Then

Feuil6.Select
Range("C3:D3").Select
Selection.Copy

Application.Goto r, True

ActiveCell.Offset(0, 3).Select

ActiveSheet.Paste

End If


If Not r Is Nothing And ob_sortie.Value = True Then

Feuil6.Select
Range("C3:D3").Select
Selection.Copy

Application.Goto r, True

ActiveCell.Offset(0, 7).Select

ActiveSheet.Paste

End If

If r Is Nothing Then

Z_saisie.Value = Mid(Z_saisie, 2, 5) 'codigo poignet de 3 cifras 382
Debug.Print Z_saisie.Value
Valeur = Z_saisie.Value


If Trim(Valeur) <> "" Then
With Feuil1.Range("A:L")
Set r = .Find(What:=Valeur, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not r Is Nothing And ob_entrée.Value = True Then

Feuil6.Select
Range("C3:D3").Select
Selection.Copy

Application.Goto r, True

ActiveCell.Offset(0, 3).Select

ActiveSheet.Paste

End If


If Not r Is Nothing And ob_sortie.Value = True Then

Feuil6.Select
Range("C3:D3").Select
Selection.Copy

Application.Goto r, True

ActiveCell.Offset(0, 7).Select

ActiveSheet.Paste

End If

If r Is Nothing Then

Message = MsgBox("El valor no se ha encontrado", vbInformation)

End If

End With
End If
End If
End With
End If
End If

End With

End If

End If

End With

End If

End If

End With

End If



Z_saisie.Value = ""


Feuil5.Activate


End Sub