Cells Relating to Each Other...

[Nuyorican]

Good Afternoon to All...

I have 2 columns, as the source information. Column # 1 consists of 6 digit numbers. Column # 2 consists of names that are associated to each number in Column # 1.

I would like to key in the info of #1 (the 6 digit number) in any cell and have the name associated with that number in the cell to the right. So my result is A1 is "678905" and B1 is "Jack Brown".

Thank you.
Benito

 

[JLGWhiz]

You can do it with event code. Assuming that the two source columns are A and B and no other data will be entered in those two columns.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Dim fn As Range
Set fn = Range("A:A").Find(Target.Value, , xlValues)
    If Not fn Is Nothing Then
        Target.Offset(, 1) = fn.Offset(, 1)
    End If
Application.EnableEvents = True
End Sub

 

[Nuyorican]

I am not proficient in the VB Editor, but I opened the editor, pasted the code in a module and I can’t run the macro.

 

[JLGWhiz]

I am not proficient in the VB Editor, but I opened the editor, pasted the code in a module and I can’t run the macro.

Delete the code from any numbered module you might have copied it to. Then, right click the name tab of the sheet where your two columns of data reside. Click 'View Code' in the pop up menu. That will open the vb editor to the Sheet code module. You can verify that by looking at the top margin of the editor where it should show something like

WorkbookName [Sheet1 (Code)]

Paste the code into that code window, save your workbook as a macro enabled workbook (.xlsm) and close the vb editor. The code will now run when you make changes on the worksheet. It will only execute the copy and paste when entries are made anywhere but columns A and B.

 

[Nuyorican]

Fantastic...Thank you Whiz !!!

 

[JLGWhiz]

Fantastic...Thank you Whiz !!!

You are welcome,
regards, JLG