Solved: Find Replace from external file

[rajkumar]

Hi Experts,

I need help in replacing column data frequently. I use a macro that does the job for me. But every time i need to edit the macro if any change in the data to be replaced. i have a list of engineer names and nos located at c: drive
i want to automate like the macro opens the c:\data_analysis\reference table.xls and looks for engineer no and replaces the selection with proper engineer name for it.


file:///C:/DOCUME%7E1/RAJKUM%7E1.R/LOCALS%7E1/Temp/moz-screenshot.jpg Selection.Replace What:="1783", Replacement:="satish", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


This is a repetitive task for me. Kindly help

Raj

[rajkumar]

some more try

i am zero in VBA.Pls help


Sub testing()

Dim fnd As String
Dim rpl As String

fnd = Array("1783", "1790", "1801", "1807", "1856", "1857", "1864", "1874", "1888", _
"1897", "1898", "KA82", "KB75", "KB76", "KB84", "SB01", "SB02", "SB03", "SB04", "SB05", _
"SB06", "SB07", "SB08", "SB09", "SB10", "SB11", "SB12", "SB13", "SB14", "SB15", "SB16", "SB18", _
"SB19", "SB21", "SB22", "SB23", "SB24", "SB25", "SB26", "SB27", "SB28", "SB29", "SB30", "SB33", _
"SB34", "SB35", "SB36", "SB37", "SB38", "SB39", "SB43", "SB44")

rpl = Array("Satish Kumar", "Selvakumar", "Chetan Jadhav", "Sudhir Hase", "Mushtaq", _
"Pradeep Kumar", "Deepak DV", "Rajkumar", "Prashanth", "Lokesh", "Harish", "NKS", _
"Mys TC", "Hubli", "Arun SME", "Sudhir Hase", "Mushtaq", "Satish Kumar", "Mushtaq", _
"Satish Kumar", "Sudhir Hase", "Rajkumar", "Prashant M", "SelvaKumar", "SelvaKumar", _
"Prashant M", "Selvakumar", "Rajkumar", "Sudhir Hase", "Prashant M", "Chetan Jadhav", _
"Chetan Jadhav", "Chetan Jadhav", "Pradeep Kumar", "Satish TC Mys", "Chetan Jadhav", _
"SelvaKumar", "Sudhir Hase", "Sudhir Hase", "Spandana Systems", "NK Services", "Satish TC Mys", _
"Pradeep Kumar", "Sudhir Hase", "Chetan Jadhav", "Rajkumar", "Chetan Jadhav", "Chetan Jadhav", _
"Satish TC Mys", "Rajkumar", "Prashant M", "Satish TC Mys")

For i = 1 To UBound(fnd)

Selection.Replace what:=fnd, replacement:=rpl, lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next i
End Sub


Sub another()
ChDir "C:\Data_Analysis"
Workbooks.Open Filename:="C:\Data_Analysis\Reference Table.xls", Origin:= _
xlWindows
Windows("sample.xls").Activate
selection.replace what:=worksheets("sheet1").range("a2"),replacement:=workbooks("sample.xls").Worksheets("engr map").range("B2), _
lookat:=xlpart, searchorder:=xlbyrows, matchcase:=false, searchformat:=false,replaceformat:=false
End Sub

[Charlize]

Almost, some little modification to loop your array off numbers and names.
Sub testing()

Dim fnd As Variant
Dim rpl As Variant
Dim myloop As Long
fnd = Array("1783", "1790", "1801", "1807", "1856", "1857", "1864", "1874", "1888", _
"1897", "1898", "KA82", "KB75", "KB76", "KB84", "SB01", "SB02", "SB03", "SB04", "SB05", _
"SB06", "SB07", "SB08", "SB09", "SB10", "SB11", "SB12", "SB13", "SB14", "SB15", "SB16", "SB18", _
"SB19", "SB21", "SB22", "SB23", "SB24", "SB25", "SB26", "SB27", "SB28", "SB29", "SB30", "SB33", _
"SB34", "SB35", "SB36", "SB37", "SB38", "SB39", "SB43", "SB44")

rpl = Array("Satish Kumar", "Selvakumar", "Chetan Jadhav", "Sudhir Hase", "Mushtaq", _
"Pradeep Kumar", "Deepak DV", "Rajkumar", "Prashanth", "Lokesh", "Harish", "NKS", _
"Mys TC", "Hubli", "Arun SME", "Sudhir Hase", "Mushtaq", "Satish Kumar", "Mushtaq", _
"Satish Kumar", "Sudhir Hase", "Rajkumar", "Prashant M", "SelvaKumar", "SelvaKumar", _
"Prashant M", "Selvakumar", "Rajkumar", "Sudhir Hase", "Prashant M", "Chetan Jadhav", _
"Chetan Jadhav", "Chetan Jadhav", "Pradeep Kumar", "Satish TC Mys", "Chetan Jadhav", _
"SelvaKumar", "Sudhir Hase", "Sudhir Hase", "Spandana Systems", "NK Services", "Satish TC Mys", _
"Pradeep Kumar", "Sudhir Hase", "Chetan Jadhav", "Rajkumar", "Chetan Jadhav", "Chetan Jadhav", _
"Satish TC Mys", "Rajkumar", "Prashant M", "Satish TC Mys")

For myloop = LBound(fnd) To UBound(fnd)

Selection.Replace what:=fnd(myloop), replacement:=rpl(myloop), lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next myloop
End Sub
Charlize

ps. - You must first select the data that you want to be replaced. What about using a certain range on a worksheet so you don't need to select the range manually ?

- Another more suitable solution would be to read the numbers and corresponding names into an array by using a worksheet that holds the data. If someone joins or leaves the team no need to alter the coding. Just alter the data sheet.

[mdmackillop]

- Another more suitable solution would be to read the numbers and corresponding names into an array by using a worksheet that holds the data. If someone joins or leaves the team no need to alter the coding. Just alter the data sheet.
I would certainly go with this route.

[Charlize]

This is the variation with reading the values from a worksheet.
Sub testing()
Dim fnd As Variant
Dim myitems As Long
Dim mysheet As Worksheet
Set mysheet = ActiveWorkbook.Worksheets("Sheet1")
myitems = mysheet.Range("A" & Rows.Count).End(xlUp).Offset(-1).Row
ReDim fnd(myitems, 1)
For myloop = LBound(fnd) To UBound(fnd)
fnd(myloop, 0) = mysheet.Range("A" & myloop + 2)
fnd(myloop, 1) = mysheet.Range("B" & myloop + 2)
Next myloop
For myloop = LBound(fnd) To UBound(fnd)
Selection.Replace what:=fnd(myloop, 0), replacement:=fnd(myloop, 1), lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next myloop
End SubCharlize

[rajkumar]

Thanks Charlize,

First one is working fine. And when going by your suggestion i think the second one i need to modify a little.

This code reads the data from a worksheet range to an array right?

i included open workbook command before your code. But not knowing how to switch back to the same workbook where i have chosen (selection) a column for replacement.(there may be many workbooks open)

can you help ? :think:


Sub testing()
ChDir "C:\Data_Analysis"
Workbooks.Open FileName:="C:\Data_Analysis\Reference Table.xls", Origin:= _
xlWindows
Dim fnd As Variant
Dim myitems As Long
Dim REFsheet As Worksheet
Set REFsheet = ActiveWorkbook.Worksheets("MIF BASE")
myitems = REFsheet.Range("A" & Rows.Count).End(xlUp).Offset(-1).Row
ReDim fnd(myitems, 1)
For myloop = LBound(fnd) To UBound(fnd)
fnd(myloop, 0) = REFsheet.Range("A" & myloop + 2)
fnd(myloop, 1) = REFsheet.Range("C" & myloop + 2)
Next myloop
For myloop = LBound(fnd) To UBound(fnd)
Selection.Replace what:=fnd(myloop, 0), replacement:=fnd(myloop, 1), lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next myloop

End Sub


Raj

[mdmackillop]

Create a variable for the Source and Target. Use them to qualify each range etc.


Sub testing()
Dim wbSource As Workbook
Dim wsTarget As Worksheet

Dim fnd As Variant
Dim myitems As Long
Dim REFsheet As Worksheet

Set wsTarget = ActiveSheet
Set wbSource = Workbooks.Open(Filename:="C:\Data_Analysis\Reference Table.xls", Origin:= _
xlWindows)

Set REFsheet = wbSource.Worksheets("MIF BASE")
myitems = REFsheet.Range("A" & Rows.Count).End(xlUp).Offset(-1).Row
ReDim fnd(myitems, 1)

For myloop = LBound(fnd) To UBound(fnd)
fnd(myloop, 0) = REFsheet.Range("A" & myloop + 2)
fnd(myloop, 1) = REFsheet.Range("C" & myloop + 2)
Next myloop

For myloop = LBound(fnd) To UBound(fnd)
wsTarget.Replace what:=fnd(myloop, 0), replacement:=fnd(myloop, 1), lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next myloop

End Sub

[rajkumar]

Thanks MD,

i am getting an compile error while running the code " Method or Data Member not found" with the below line is highlighted in blue.


For myloop = LBound(fnd) To UBound(fnd)
wsTarget.Replace what:=fnd(myloop, 0), replacement:=fnd(myloop, 1), lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next myloop


what should i do to correct this?

Raj :think:

[mdmackillop]

wsTarget.Cells.Replace or other identified range

[rajkumar]

Thanks MD & Charlize :friends:

I have modified the code and it is working as expected

Please find below is final one . Thanks a lot


Sub testing()
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim wsTarget As Worksheet

Set wbTarget = ActiveWorkbook
Set wsTarget = ActiveSheet

Dim fnd As Variant
Dim myitems As Long
Dim REFsheet As Worksheet


Set wbSource = Workbooks.Open(Filename:="C:\Data_Analysis\Reference Table.xls", Origin:= _
xlWindows)

Set REFsheet = wbSource.Worksheets("ENGR MAP")
myitems = REFsheet.Range("A" & Rows.Count).End(xlUp).Offset(-1).Row
ReDim fnd(myitems, 1)

For myloop = LBound(fnd) To UBound(fnd)
fnd(myloop, 0) = REFsheet.Range("A" & myloop + 2)
fnd(myloop, 1) = REFsheet.Range("B" & myloop + 2)
Next myloop
wbTarget.Activate
For myloop = LBound(fnd) To UBound(fnd)
Selection.Replace what:=fnd(myloop, 0), replacement:=fnd(myloop, 1), lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next myloop
wbSource.Close
End Sub