Type a code in a cell and show a Name on the adjacent cell.

[Agnarr]

Hello everybody and you all do an amazing work.
I need your help please and i apologize in advance for any mistakes (English is not my native language).

I have 4-digit codes with corresponding names in one sheet and on another I've made it so you type one code on a cell and on the next cell to show the name of the corresponding code with an IF statement. The problem is that people keep erasing lines or add so i really need to do that with VBA.

So for example one writes a code in B2, presses enter and on cell C2 appears the name Mr. Smith.

Thank you all in advance. Any help will be appreciated.

 

[mumps]

This macro assumes your codes and names are in columns A and B respectively in Sheet1.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet2 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a code in cell B2 and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "B2" Then Exit Sub
    Application.ScreenUpdating = False
    Dim fnd As Range
    Set fnd = Sheets("Sheet1").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Target.Offset(, 1) = fnd.Offset(, 1)
    End If
    Application.ScreenUpdating = True
End Sub

 

[Agnarr]

This macro assumes your codes and names are in columns A and B respectively in Sheet1.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet2 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a code in cell B2 and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "B2" Then Exit Sub
    Application.ScreenUpdating = False
    Dim fnd As Range
    Set fnd = Sheets("Sheet1").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Target.Offset(, 1) = fnd.Offset(, 1)
    End If
    Application.ScreenUpdating = True
End Sub

ok. but how will that work for the whole range?
I need it to be done not only for the b2 but for the whole range of b:b but also allow my to type anything else in there.

 

[mumps]

Replace this line of code:

If Target.Address(0, 0) <> "B2" Then Exit Sub

with this line:

If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub

 

[Agnarr]

Replace this line of code:

If Target.Address(0, 0) <> "B2" Then Exit Sub

with this line:

If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub

Thank you! Your help is greatly appreciated!

 

[mumps]

You are very welcome.