When the selected Company Id is not found on the table, the watch window shows an error in CompId.Address because the CompId.Value is nothing and is invalid for the Range data type.
An IF statement should be added to address such cases. In VBA, the keyword NOT is used often since it is usually easier to specify what something isn’t, than what something is. In this case, the result can either be nothing or specific ranges. When it is nothing, you can alert the user with a message box indicating so. The IF statement then becomes:
If Not CompId is Nothing Then
Msgbox “Company not found!”
However, while it displays the message box when the company id is not found, the value in cell C3 retains the result of the previous search. Make sure that the macro deletes the contents of the cell before it runs the rest of the code: