Excel VBA - 从外部文件查找和替换 [英] Excel VBA - Find and Replace from External File

问题描述

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

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

  Sub LegalName（）
 Dim NameListWB As Workbook 
 Dim NameListWS As Worksheet 
 Set NameListWB = Workbooks.Open（File.xlsx）
设置NameListWS = NameListWB.Worksheets（选择
直到IsEmpty（ActiveCell）
工作表（ Sheet1）列（F）替换_ 
什么：= ActiveCell.Value，替换：= ActiveCell.Offset（0，1）.Value，_ 
 SearchOrder：= xlByColumns，MatchCase ：= False 
 ActiveCell.Offset（1，0）。选择
循环
 End Sub 
  

解决方案

我看到你开始宣称你的对象，但错过了几个。此外，您需要避免使用。选择 有趣的阅读

这是你正在尝试的（ UNTESTED ）？

  Sub Sample（）
 Dim NameListWB As Workbook，thisWb As Workbook 
 Dim NameListWS As Worksheet ，thisWs As Worksheet 
 Dim i As Long，lRow As Long 
 
'~~>这是从您的代码运行的工作簿
设置thisWb = ThisWorkbook 
'~~>将其更改为要替换的工作表名称
'~~>在列F 
设置thisWs = thisWb.Sheets（Sheet1）
 
'~~> File.xlsx 
设置NameListWB = Workbooks.Open（C：\File.xlsx）
设置NameListWS = NameListWB.Worksheets（Sheet1）
 
使用NameListWS 
'~~>查找File.xlsx的Col A中的最后一行
 lRow = .Range（A& .Rows.Count）.End（xlUp）.Row 
 
'~~>循环通过Col A 
对于i = 1 To lRow 
'~~>替换
 thisWs.Columns（6）。更改什么：=。范围（A& i）.Value，_ 
替换：=。范围（B& i）。值$ _ 
 SearchOrder：= xlByColumns，_ 
 MatchCase：= False 
 Next i 
 End with 
 End Sub 
  

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

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

解决方案

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

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

这篇关于Excel VBA - 从外部文件查找和替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！