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.

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

This is a repetitive task for me. Kindly help

Raj

[rajkumar]

some more try

i am zero in VBA.Pls help

[VBA]
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
[/VBA]

[Charlize]

Almost, some little modification to loop your array off numbers and names.
[vba]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[/vba]
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.
[VBA]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 Sub[/VBA]Charlize

[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 ?

[VBA]
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
[/VBA]

Raj

[mdmackillop]

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

[VBA]
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

[/VBA]

[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.

[VBA]
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
[/VBA]

what should i do to correct this?

Raj

[mdmackillop]

wsTarget.Cells.Replace or other identified range

[rajkumar]

Thanks MD & Charlize

I have modified the code and it is working as expected

Please find below is final one . Thanks a lot

[VBA]
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
[/VBA]