Amend an Find Replace macro to offset the results column

[Levitation]

Hi. So, below Ialready have a working macro which when I highlight a cell or column ofdata in one sheet (not in the ClientNames sheet as that’s the sheet beingreferenced), it will reference Table4 (which is Col A to B in theClientNames sheet) and change the name based on what is in Col A towhat is in Col B from Table4.
e.g. say I select ‘Mike’ somewhere a sheet in the workbook, and I then runthe macro. Because in Table4, cell A2 contains 'Mike' and B2 contains'Michael', the macro changes Mike to Michael.

Sub A_MrExcelReplaceRangeSelectedClientNames()
'PURPOSE: Find & Replace a list of text/values in a range
Dim X As Long
' code from Joe at MrExcel
Dim Table4 As ListObject, vFindReplc As Variant
Set Table4 = Sheets("ClientNames").ListObjects("Table4")
vFindReplc = Table4.DataBodyRange.Columns("A:B").Value 'DesignateColumns for Find/Replace data
'Loop through each item in Array lists
For X = LBound(vFindReplc, 1) To UBound(vFindReplc, 1)
Selection.Replace What:=vFindReplc(X, 1), Replacement:=vFindReplc(X, 2), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next X
End Sub

However, what Iwant the macro to do is when I when I select a cell or a column (for exampleCol D), I want it to check for all the entries in Table4 Col A and return thevalue from Col B (as above, Table4, cell A2 contains 'Mike' and B2 contains'Michael'), however the result needs to be offset by one column to the rightfrom the column which was originally selected.
e.g. if I select‘Mike’ in Column D (let’s say ‘Mike’ is in cell D5) of any sheet (apart from theClientNames sheet), I want the macro to interrogate Table4, and paste ‘Michael’in Column E (so in this scenario it’d paste ‘Michael’ in cell E5) etc.

Thanks

Mike

 

[Levitation]

All's quiet on the Western Front - perhaps I should ad something more pictorial to explain how I want to change the macro!?

 

[DanteAmor]

Try this

Select a cell, a range of cells (in a single column) or a column and run the following macro.

Sub Search_Name()
  Dim c As Range, f As Range, rng As Range, sh As Worksheet, table4 As ListObject
  Set sh = Sheets("[COLOR=#0000ff]ClientNames[/COLOR]")
  Set table4 = sh.ListObjects("[COLOR=#0000ff]Table4[/COLOR]")
  If ActiveSheet.Name = sh.Name Then Exit Sub
  Set rng = Selection
  For Each c In rng.SpecialCells(xlCellTypeConstants)
    Set f = table4.DataBodyRange.Columns(1).Find(c, , xlValues, xlWhole)
    If Not f Is Nothing Then
      c.Offset(, 1).Value = f.Offset(, 1).Value
    End If
  Next
End Sub

 

[Levitation]

That's brilliant. Tested and works perfectly. Thank you very much Dante

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.