Open Another File to Search and Replace Text

[Ricky Pang]

Hello Experts,
I have a workbook that contains a master list of names from Before to
After that I need to update onto other files.

This master list has 3 columns: Column A is the Property Type, Column B
is Search For, Column C is Replace With. All data starts from row 2.
Cell H1 has a dropdown choice of Column A's Property Types for a Vlookup
or an Offset function purpose in step 3 below.

How do you...?
1) Select New file to open
2) Toggle back to the Master List file
3) Vlookup, using H1's preselected choice, all Search For data
4) Loop through New file's Column A. For each "Search For" name found,
replace the name with the data under Column C "Replace With".

So far, the names are written into the code. I need to be able loop the
search and replace referring to the Master List instead. (Segments of
this code is courtesy of Mr. Ogilvy):

'This is supposed to select New file to open
Dim FName As String
Dim wkbk As Workbook
Dim getname As String

Set curWks = ActiveSheet

If Range("R1") = "" Or Range("R1") = "False" Then
getname = Application.GetOpenFilename
Range("R1").Select
If getname = "False" Then
Range("A1").Select
Exit Sub
End If
ActiveCell = getname
Range("R2").Select
ActiveCell.FormulaR1C1 = _

"=MID(R[-1]C,FIND(""#"",SUBSTITUTE(R[-1]C,""\"",""#"",LEN(R[-1]C)-LEN(SU
BSTITUTE(R[-1]C,""\"",""""))))+1,255)"
End If

FName = curWks.Range("R2").Value
varFound = False
For Each w In Workbooks
If w.Name = FName Then
varFound = True
Exit For
End If
Next w
If varFound Then
Set wkbk = Workbooks(FName)
wkbk.Activate
Else
Set wkbk = Workbooks.Open(Range("R1"), UpdateLinks:=0)
End If

'Search and Replace Other Revenue and Expense Titles
Dim ans1 As Long, ans2 As String, s1 As String
Dim s2, s3, s4 As String
Dim r1, r2, r3, r4 As String

'Search Titles
s1 = "Search 1"
s2 = "Search 2"
s3 = "Search 3"
s4 = "Search 4"

'Replacement Titles
r1 = "Replacement 1"
r2 = "Replacement 2"
r3 = "Replacement 3"
r4 = "Replacement 4"

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng

If LCase(cell.Value) = LCase(s1) Then
cell.Select
s = "[" & s1 & "]" & " Will Be Replaced By " & "[" & r1 & "]" &
vbNewLine _
& vbNewLine _
& "Yes: Continue" & vbNewLine _
& "No: Do Not Replace" & vbNewLine _
& "Cancel: Override to Input Alternate Title"

ans1 = MsgBox(s, vbYesNoCancel, "Select an Option")
Select Case ans1
Case vbYes
cell.Value = r1
Case vbCancel
ans2 = InputBox("Enter Alternate Title to Replace",
"Enter Replacement")
If Len(Trim(ans2)) > 0 Then
cell.Value = ans2
End If
End Select
End If

'Repeat repeat
If LCase(cell.Value) = LCase(s2) Then
cell.Select
s = "[" & s2 & "]" & " Will Be Replaced By " & "[" & r2 & "]" &
vbNewLine _
& vbNewLine _
& "Yes: Continue" & vbNewLine _
& "No: Do Not Replace" & vbNewLine _
& "Cancel: Override to Input Alternate Title"

ans1 = MsgBox(s, vbYesNoCancel, "Select an Option")
Select Case ans1
Case vbYes
cell.Value = r2
Case vbCancel
ans2 = InputBox("Enter Alternate Title to Replace",
"Enter Replacement")
If Len(Trim(ans2)) > 0 Then
cell.Value = ans2
End If
End Select
End If

Thanks so much in advance,
Ricky

 

[Ricky Pang]

Hello,
I should condense my question from the previous post. I have a master
list file with titles Property Type, Search For, Replace With data. How
do you loop through to match another file's column A for each name that
matches the Search For column in the master list. Then, replace it with
the master list's Replace With data?

Could this search and replace code be dependant on the master list's H1
selection of the Property Type? I picture this as similar to a Vlookup
function.

Thanks in advance,
Ricky