Excel adding and removing data

jesper1000 

Sub Serial_number()

Dim i As Long
Dim j As Long
Dim k As Long
Dim searchstring As String

i = Sheets("Tabelle1").Cells(Rows.Count, "A").End(xlUp).Row
k = Application.WorksheetFunction.Find(" ", Sheets("Tabelle2").Cells(2, 1))
searchstring = Left(Sheets("Tabelle2").Cells(2, 1), k - 1) & " "

For j = 2 To i
If InStr(Sheets("Tabelle1").Cells(j, "A").Value, searchstring) > 0 Then
        Sheets("Tabelle1").Cells(j, "A").Value = Sheets("Tabelle2").Cells(2, 1).Value
Exit Sub
End If
Next j

End Sub

An alternative could be these lines of code. As an unambiguous identifier i chose column names followed by a space e.g. "A " or "BN ". In sheet "Tabelle2" you can enter a new serial number in cell A2 e.g. "DA 11111111" and then click the button in cell D2. The entry from cell A2 is then entered in cell A106 in sheet1 "Tabelle1" in this example.