Levitation
New Member
- Joined
- May 18, 2017
- Messages
- 9
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
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