Numbers to names (vba code)

tazlucky

New Member
Joined
Jul 4, 2012
Messages
42
Not sure if this can be done or not. I'm after the vba code for a worksheet so if you enter a persons id number anywhere if will change the entered number to display their name next to it. i.e. 51498 will change to 51498 Mark, 51670 will change to 51670 Ted, etc.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
I assume you have a table of numbers and names in the workbook to look these up in?

Am I right in thinking you want the ID and names to show in the same cell?

When you say anywhere I assume you don't literally mean that. What would the input range be?

Dom
 
Last edited:

jmcdaid

New Member
Joined
Jan 9, 2012
Messages
29
Office Version
  1. 365
Jumped the gun, I just noticed the vba requirement. In order to achieve what you are after you will need to write code that can first identify the persons name. This is probably easiest done using a loop to run through the numbers and obtain the correct name. You then have to implement some way to trigger the code. You could consider using the Worksheet_Change event or attaching the macro to a button.
 
Last edited:

tazlucky

New Member
Joined
Jul 4, 2012
Messages
42

ADVERTISEMENT

For Domski - I have a table which changes daily and the persons ID number could be placed anywhere in the worksheet, when this happens rather than just showing their id number, I would like it to change to their id number with their name next to it.
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
In it's simplest form:


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngFindRange As Range    
    Set rngFindRange = Sheets("Sheet2").Range("A:A").Find(Target.Value, _
        LookIn:=xlValues, lookat:=xlWhole)
    If Not rngFindRange Is Nothing Then
        Application.EnableEvents = False
        Target.Value = Target.Value & " " & rngFindRange.Offset(0, 1)
        Application.EnableEvents = True
    End If
End Sub


Looks for the ID number entered on the sheet in column A of Sheet2 and returns the name from column B.


The code needs to go on your sheet's code page that the ID numbers will be entered on. Right click on the sheet tab and select View Code. Paste it on the page that appears.


Dom
 
Last edited:

tazlucky

New Member
Joined
Jul 4, 2012
Messages
42

ADVERTISEMENT

In it's simplest form:


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngFindRange As Range    
    Set rngFindRange = Sheets("Sheet2").Range("A:A").Find(Target.Value, _
        LookIn:=xlValues, lookat:=xlWhole)
    If Not rngFindRange Is Nothing Then
        Application.EnableEvents = False
        Target.Value = Target.Value & " " & rngFindRange.Offset(0, 1)
        Application.EnableEvents = True
    End If
End Sub


Looks for the ID number entered on the sheet in column A of Sheet2 and returns the name from column B.


The code needs to go on your sheet's code page that the ID numbers will be entered on. Right click on the sheet tab and select View Code. Paste it on the page that appears.


Dom


Thank you very much DOMSKI - just what I was looking for it works great! to expand on this how would I go about coding the below example, so when I enter the id number it places the details in the columns as indicated, then tabs down onto the next line for another entry?

ABC
ID numberForenameSurname
51498Mark Jones

<tbody>
</tbody><colgroup><col span="3"></colgroup>
 
Last edited:

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Something like this maybe:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim rngFindRange As Range
    
    If Intersect(Target, Range("A2:A" & Rows.Count)) Is Nothing Then Exit Sub
    
    If Intersect(Target, Range("A2:A" & Rows.Count)).Count > 1 Then
        MsgBox "One entry only in column A please."
        Application.Undo
    End If
    
    Set rngFindRange = Sheets("Sheet2").Range("A2:A" & Rows.Count).Find(Target.Value, _
        LookIn:=xlValues, lookat:=xlWhole)
    If Not rngFindRange Is Nothing Then
        Application.EnableEvents = False
        Target.Offset(0, 1) = rngFindRange.Offset(0, 1)
        Target.Offset(0, 2) = rngFindRange.Offset(0, 2)
        Application.EnableEvents = True
    Else
        MsgBox "No match for ID Number."
    End If
    
End Sub

Dom
 

Watch MrExcel Video

Forum statistics

Threads
1,122,416
Messages
5,596,010
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top