Find/Replace using VBA

[dpchheda]

Hi,

I have around 10 names which I want to replace with their code names. For Example, "Alan Richard" needs to be replaced with AR001. Problem is I don't know cell range and this name might appear more than once in the data.

Please help.

 

[Rick Rothstein]

Something like this maybe...

Sub ReplaceNames()
  Dim N As Variant, NameList As Variant, NameCodes As Variant
  NameList = Array("Alan Richard", "Sam Spade", "Joe Blow", "John Doe")
  NameCodes = Array("AR001", "SS001", "JB001", "JD001")
  For Each N In NameList
    Cells.Replace N, NameCodes, xlPart, , False
  Next
End Sub

where you would use your actual names and codes in place of the example ones I used above. Note that the order of the names in the NameList and the codes in the NameCodes must be in a one-to-one correspondence.

 

[dpchheda]

Hi Rick,

Thanks for your post.

This code replaces all the cell values (all other names) with only AR001 and ignores other namecodes in the code.

 

[Rick Rothstein]

This code replaces all the cell values (all other names) with only AR001 and ignores other namecodes in the code.

Sorry, my fault, try this code instead...

Sub ReplaceNames()
  Dim X As Long, NameList As Variant, NameCodes As Variant
  NameList = Array("Alan Richard", "Sam Spade", "Joe Blow", "John Doe")
  NameCodes = Array("AR001", "SS001", "JB001", "JD001")
  For X = LBound(NameList) To UBound(NameList)
    Cells.Replace NameList(X), NameCodes(X), xlPart, , False
  Next
End Sub

 

[dpchheda]

This works fine. Thanks a lot for your help.