remplace ta procédure Commandbutton1_Click par celle-ci, ça devrait mieux aller :
Private Sub CommandButton1_Click()
Dim LastCel As Range, Cel As Range, Plage As Range
With Worksheets("Référence")
Set Plage = .Range("A1", .Range("A65536").End(xlUp))
End With
Set Cel = Plage.Find(what:=TextBoxCode.Value, lookat:=xlWhole, _
MatchCase:=False)
If Cel Is Nothing Then
Set LastCel = Plage.End(xlDown).Offset(1, 0)
LastCel.Value = TextBoxCode.Value
LastCel.Offset(0, 1).Value = TextBoxDésignation.Value
End If
End Sub