Excel VBA - 从外部文件中查找和替换

I have a file that I would like to run a Find and Replace on using data from another Excel file.

我有一个文件，我想使用另一个Excel文件中的数据运行查找和替换。

I have this so far, what am I doing wrong?

到目前为止我有这个，我做错了什么？

Sub LegalName()
    Dim NameListWB As Workbook
    Dim NameListWS As Worksheet
    Set NameListWB = Workbooks.Open("File.xlsx")
    Set NameListWS = NameListWB.Worksheets("Sheet1")
    Dim rng As Range

    Set rng = NameListWS.Range("A:B").Select
     Do Until IsEmpty(ActiveCell)
             Worksheets("Sheet1").Columns("F").Replace _
            What:=ActiveCell.Value, Replacement:=ActiveCell.Offset(0, 1).Value, _
            SearchOrder:=xlByColumns, MatchCase:=False
         ActiveCell.Offset(1, 0).Select
     Loop
End Sub

1 个解决方案

#1

I see that you started by declaring your objects but missed out on few. Also, you need to avoid the use of .Select Interesting Read

我看到你开始宣布你的对象，但错过了几个。此外，您需要避免使用.Select Interesting Read

Is this what you are trying (UNTESTED)?

这是你在尝试（未测试）？

Sub Sample()
    Dim NameListWB As Workbook, thisWb As Workbook
    Dim NameListWS As Worksheet, thisWs As Worksheet
    Dim i As Long, lRow As Long

    '~~> This is the workbook from where your code is running
    Set thisWb = ThisWorkbook
    '~~> Change this to the sheet name where you want to replace
    '~~> in Column F
    Set thisWs = thisWb.Sheets("Sheet1")

    '~~> File.xlsx
    Set NameListWB = Workbooks.Open("C:\File.xlsx")
    Set NameListWS = NameListWB.Worksheets("Sheet1")

    With NameListWS
        '~~> Find last row in Col A of File.xlsx
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Loop though Col A
        For i = 1 To lRow
            '~~> Do the replace
            thisWs.Columns(6).Replace What:=.Range("A" & i).Value, _
                                      Replacement:=.Range("B" & i).Value, _
                                      SearchOrder:=xlByColumns, _
                                      MatchCase:=False
        Next i
    End With
End Sub

#1