+ Reply to Thread
Results 1 to 2 of 2

Open Another File to Search and Replace Text

  1. #1
    Ricky Pang
    Guest

    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. #2
    Ricky Pang
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1