Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Another File to Search and Replace Text
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 *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Another File to Search and Replace Text
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 *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No text search from File|Open in Excel 2007? | New Users to Excel | |||
How do I search and replace text in a textbox in Excel? | Excel Discussion (Misc queries) | |||
Search and Replace With Formatted Text | Excel Programming | |||
Excel VBA - open text file, replace text, save file? | Excel Programming | |||
Search and Replace With Formatted Text | Excel Programming |