Solved: Find Replace Macro for Selection

[rajkumar]

Hi Experts,

I want to convert the serial no to engineer name or customer name.

currently what i do is copy paste the serial no column in another column and run the below macro

Selection.Replace What:="*3527380919*", Replacement:="VALUE POINT", Lookat:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="*3121791799*", Replacement:="SOBANA OFFSET PRINTERS", Lookat:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="*3512494969*", Replacement:="SUBHA XEROX", Lookat:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="*46969*", Replacement:="SESHASAI E FORMS PVT", Lookat:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="*1109347163*", Replacement:="SESHASAI E FORMS PVT. LTD", Lookat:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="*1109380675*", Replacement:="SESHASAI E FORMS PVT. LTD", Lookat:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False


I have file that has a customer list with products and engineer names(reference table.xls).

The reference table.xls is kept in a path C:\Data_Analysis\Reference Table.xls and will be appended with line item of new records when a new printer is installed.

my problem is whenever a new machine is installed i have to create a new macro for find and replace according to the reference table.xls.

Can any body help me with a code that can automate this.

Thanks
Raj

[akanchu]

Hi Raj,
I think, using the VLOOKUP function of excel can help you to achieve what you want.
I just added an extra sheet "Actual_data" to urs and applied the formula.
You can enter the no. there and get the name.
You could hide the no. column to just show only the name.

hope it helps.
you might have to change the upper limit of the row# when the value increases.

[rajkumar]

Thanks for reply,

i require to do it via vba. I have sorted out the same

'***********************************************************************
' This UDF is written for making converters location independent
' XcelKing Xpress Edition X5
' ***********************************************************************

Public Function ColumnLetter(anyCell As Range) As String

ColumnLetter = Left(anyCell.Address(False, False), 1 - CInt(anyCell.Column > 26))

End Function

' Patch Converter Macro

Sub PatchConvert()
Call PGMTR
Application.ScreenUpdating = False
Dim cNum As Integer
Dim nextfreecolumn As Integer
cNum = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
nextfreecolumn = cNum + 1
Selection.Copy
Columns(nextfreecolumn).Select
ActiveSheet.Paste
Call PC1
End Sub

Sub PC1()
Dim cNum As Integer
Dim nextfreecolumn As Integer
cNum = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
nextfreecolumn = cNum + 1
Selection.Copy
Columns(nextfreecolumn).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Call PC2
End Sub

Sub PC2()
ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(0, 1).FormulaR1C1 = "Patch"
ActiveCell.Offset(1, 1).Select
Dim myRng As Range
Dim lastRw As Long
sSheetName = ActiveSheet.Name
lastRw = ActiveCell.End(xlDown).Row
With ActiveCell
.Formula = "=VLOOKUP(RC[-1],'C:\Data_Analysis\[Reference Table.xls]MIF Base'!R4C1:R1000C10,3,0)"
.AutoFill Destination:=Worksheets(sSheetName).Range(ActiveCell, ColumnLetter(ActiveCell) & lastRw&)
End With
Call PC3
End Sub

Sub PC3()
Columns(ColumnLetter(ActiveCell)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns(ColumnLetter(ActiveCell)).AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Thanks again
Raj