替换Excel工作表中的多个单词

我同意sous2817的观点：解决自己的问题越多，您的发展就会越快。但是，我认为一些建议会有所帮助。

如果要使用VBA，则必须学习VBA。找到并尝试使用您不理解的一段代码永远都不会结束。当此代码的作者仅比您了解更多时，尤其如此。

在网上搜索“ Excel VBA教程”。有很多可供选择的内容，因此请尝试一些并完成与您的学习风格相匹配的内容。我更喜欢书。我参观了一个大型图书馆，审阅了他们的Excel VBA入门手册，并借用了我喜欢的那些。在家里试用之后，我买了最适合自己的那一款。

您从哪里获得此代码？它包含典型的初学者错误，即使使用第一个示例也不起作用。

Sheet1中的第一个单词是“ it”。Sheet2指示将“ it”替换为“ that”。该代码将“ it”正确替换为“ that”。不幸的是，它用“ that”替换了所有“ it”，因此“ with”被翻译为“ wthath”而不是“ having”。由于您抱怨第二张纸，所以我想您没有注意到翻译错误。这样的误译在第二对中必须更加明显，并且在“他们”，“那里”，“他们”，“他们”，“然后”和“这些”中出现第一个单词“ the”。

如果您查看Replace Method，您应该会看到此错误的快速更正。注意：Replace Method不能Replace Function。

最后，我将给出一些示例，说明初级程序员可能是如何编写此例程的。

考虑：

s2.Activate

N = Cells(Rows.Count, 1).End(xlUp).Row
ReDim from(1 To N)
ReDim too(1 To N)
For i = 1 To N
    from(i) = Cells(i, 1).Value
    too(i) = Cells(i, 2).Value
Next i

开始学习VBA时要学习的第一件事是“不要激活工作表或选择单元格”。这些命令很慢，即使您使用ScreenUpdating = False，也会进行一些屏幕重写。更重要的是，您的代码可能会变得非常混乱。以下更好：

With s2

  N = .Cells(Rows.Count, 1).End(xlUp).Row
  ReDim from(1 To N)
  ReDim too(1 To N)
  For i = 1 To N
   from(i) = .Cells(i, 1).Value
   too(i) = .Cells(i, 2).Value
  Next i

End With

注1：前三个时期Cells。Cells在活动工作表上运行。.Cells在With语句中指定的工作表上运行。

注2：我还没有放置过一段Rows.Count。Rows.Count返回活动工作表中的行数。.Rows.Count返回指定工作表中的行数。行数取决于Excel的版本，并且在工作表之间不会有所不同，因此大多数程序员都不会担心句点。

我将创建两个工作表参数：

Sub xLator2(s1 As Worksheet, s2 As Worksheet)

这使子例程更加灵活。我可能会走得更远，并将参数Ranges赋予更大的灵活性。

我可以继续，但是我认为这足以开始。祝您好运，欢迎您来享受编程的乐趣。

编辑：教程和完整的解决方案

第1部分-不使用激活的原因之一

请研究以下代码块，这些代码块说明为什么只有大多数初级VBA程序员在Activate没有充分理由的情况下才使用。我不希望您因节省几秒钟或几毫秒而变得过于紧张。有些程序员会花半个小时来优化一次又一次运行的例程。只有每天要执行数百次例程，才能证明该时间是合理的。我希望您Application.ScreenUpdating = False 能节省很多时间，所以请您自动使用它。当Application.ScreenUpdating = False被包括在内，之间的区别Activate，并With就少了很多，但足以证明作出With的默认选择。

  For Count = 1 To 10000             ' This takes 148 seconds
    Worksheets("Sheet1").Activate
    Worksheets("Sheet2").Activate
  Next

  Application.ScreenUpdating = False ' This takes 11 seconds
  For Count = 1 To 10000
    Worksheets("Sheet1").Activate
    Worksheets("Sheet2").Activate
  Next

  Application.ScreenUpdating = False ' This takes .07 seconds
  For Count = 1 To 10000
    With Worksheets("Sheet1")
    End With
    With Worksheets("Sheet2")
    End With
  Next

  Application.ScreenUpdating = False ' This takes 12 seconds
  For Count = 1 To 10000
    Worksheets("Sheet1").Activate
    Cells(23, 1).Value = "A"
    Worksheets("Sheet2").Activate
    Cells(23, 1).Value = "A"
  Next

  Application.ScreenUpdating = False ' This takes 1.16 seconds
  For Count = 1 To 10000
    With Worksheets("Sheet1")
      .Cells(23, 1).Value = "A"
    End With
    With Worksheets("Sheet2")
      .Cells(23, 1).Value = "A"
    End With
  Next

  Application.ScreenUpdating = False ' This takes 0.96 seconds
  Set Wsht1 = Worksheets("Sheet1")
  Set Wsht2 = Worksheets("Sheet2")
  For Count = 1 To 10000
    With Wsht1
      .Cells(23, 1).Value = "A"
    End With
    With Wsht2
      .Cells(23, 1).Value = "A"
    End With
  Next

第2部分-将值从工作表复制到数组

宏LoadFromTo1()是基于其加载从太表宏开幕代码。因为我的测试数据略有不同，所以它略有不同。加载“从”和“太”表需要十分之六秒的时间

Sub LoadFromTo1()

  ' Takes about .594 seconds for 50,000 rows * 2 columns

  Dim s1 As Worksheet
  Dim N As Long, i As Long
  Dim From(), too()

  Dim InxFromTo As Long
  Dim TimeStart As Single

  TimeStart = Timer

  Set s1 = Sheets("Test1") '   contains the data

  s1.Activate

  N = Cells(Rows.Count, 3).End(xlUp).Row
  ReDim From(1 To N - 1)
  ReDim too(1 To N - 1)
  For i = 2 To N
    From(i - 1) = Cells(i, 3).Value
    too(i - 1) = Cells(i, 4).Value
  Next i

  Debug.Print "M1: " & Timer - TimeStart

  For InxFromTo = 1 To 20
    Debug.Print Right("    " & InxFromTo, 5) & " " & From(InxFromTo) & " " & too(InxFromTo)
  Next
  For InxFromTo = UBound(From) - 20 To UBound(From)
    Debug.Print Right("    " & InxFromTo, 5) & " " & From(InxFromTo) & " " & too(InxFromTo)
  Next

End Sub

宏LoadFromTo2()（未显示）With代替Activate。只有一个Activate或一个With，没有明显的性能变化。

宏LoadFromTo3()使用另一种技术来加载表。而不是一次导入一个单元格值，而是在单个语句中导入CellValue = .Range(.Cells(2, 3), .Cells(RowMax, 4)).Value。这句话现在对您可能看起来很奇怪。但是，如果您学习和实践该技术，它将成为第二天性。我发现它LoadFromTo3()比LoadFromTo1()它更容易编码和理解，并且它的速度是它的十倍。我已经读过，虽然我从未达到过那种性能提升的水平，但将范围作为一个单元而不是逐个单元导入的速度可以快五十倍。

Sub LoadFromTo3()

  ' Takes about .0625 seconds for 50,000 rows * 2 columns

  Const ColFrom As Long = 1
  Const ColTo As Long = 2

  Dim s1 As Worksheet
  Dim RowMax As Long, RowCrnt As Long

  Dim InxFromTo As Long
  Dim TimeStart As Single

  Dim CellValue As Variant

  TimeStart = Timer

  Set s1 = Sheets("Test1") '   contains the data

  With s1
    RowMax = .Cells(Rows.Count, 3).End(xlUp).Row
    CellValue = .Range(.Cells(2, 3), .Cells(RowMax, 4)).Value

    Debug.Print "M3: " & Timer - TimeStart

  End With

  For InxFromTo = 1 To 20
    Debug.Print Right("    " & InxFromTo, 5) & " " & CellValue(InxFromTo, ColFrom) & _
                                               " " & CellValue(InxFromTo, ColTo)
  Next
  For InxFromTo = UBound(CellValue, 1) - 20 To UBound(CellValue, 1)
    Debug.Print Right("    " & InxFromTo, 5) & " " & CellValue(InxFromTo, ColFrom) & _
                                               " " & CellValue(InxFromTo, ColTo)
  Next

End Sub

第3部分-原始代码分析

首先是一些定义。的目标范围是字的列进行翻译。所述目标表是目标范围加载到存储器中。FromTo范围是From和To列。所述的FromTo表是加载到存储器中的所述的FromTo范围。

本节花费的时间比我最初计划的要长。最初，我只打算对我的代码进行适当的测试。鉴于较早答案中提供的代码中的错误，并且隐含着另一个错误，我特别小心。我编写了一个例程，生成了各种大小的测试数据。我使用该例程生成了用于第2部分中的计时的50,000行FromTo表。我对您发布的转换例程进行了一些细微更改，并针对我的测试数据运行了该例程，以提供以下持续时间：

FromTo  Target  Target  Duration  
  Rows    Rows    Cols   in secs
50,000      20       1       103
50,000  10,000       1       486
10,000   3,000       2        60

我修改后的代码的关键部分是：

With RngTgt
    For RowFromTo = 1 To UBound(FromToTable, 1)
      .Replace What:=FromToTable(RowFromTo, ColFrom), _
               Replacement:="__" + Str(RowFromTo), _
               LookAt:=xlWhole
    Next
    For RowFromTo = 1 To UBound(FromToTable, 1)
      .Replace What:="__" + Str(RowFromTo), _
               Replacement:=FromToTable(RowFromTo, ColTo), _
               LookAt:=xlWhole
    Next
  End With

我使用了一个范围，因此目标范围可以在任何工作表中并且可以是多列。我看不到具有如此大的前缀和后缀会导致时间损失的意义，因此我将前缀减少为两个下划线。我包括LookAt:=xlWhole更正。我使用从范围加载的ToFrom表。我已替换i为有意义的名称，RowFromTo以使代码更易于理解。

没有评论说这段代码做什么以及为什么这样做。必须在宏中包含足够的注释。您还记得此宏在六到十二个月内做什么吗？如果同事需要修改该怎么办？

在您的第一个FromTo表中，“ for”转换为“ on”，“ on”转换为“ upon”。如果没有两遍解决方案，“ for”可能会转换为“ upon”。这是我的猜测，这似乎很合理但是应该有一条评论，这样我就不必猜测了，负责此宏的新程序员可能无法猜测，并可能通过删除“不必要的”第二遍来“改进”代码。

Replace方法背后的代码将达到其作者可以管理的效率，但是仍然必须检查范围中的每个单元格。我通过搜索指定范围而不是整个工作表来改善了这种情况。但是，它仍然必须为FromTo表中的每一行两次搜索该范围。

研究此代码后，我看到了立即的改进。第一遍将目标表中的单词替换为“ __1”，“ __ 2”，“ __ n”，依此类推，其中1、2和n是FromTo表的索引。第二遍搜索“ __1”，“ __ 2”和“ __n”。更好的技术是提取1、2和n并使用它们访问FromTo表中的正确条目。使用这种更好的技术，持续时间为：

                           First    Second
FromTo  Target  Target  duration  duration
  Rows    Rows    Cols   in secs   in secs
50,000      20       1       103        52
50,000  10,000       1       486       257
10,000   3,000       2        60        32

也就是说，我通过将代码更改为以下方式将持续时间减半：

  With RngTgt
    For RowFromTo = 1 To UBound(ToFromTable, 1)
      .Replace What:=ToFromTable(RowFromTo, ColFrom), _
               Replacement:="__" + Str(RowFromTo), _
               LookAt:=xlWhole
    Next
  End With

  For Each Cell In RngTgt
    Test = Mid(Cell.Value, 3)
    If IsNumeric(Test) Then
      Cell.Value = ToFromTable(Val(Test), ColTo)
    End If
  Next

但是，我认为基本方法是错误的。如果在FromTo范围内有FT条目，而在Target范围内有T条目，则：

• 在方法1中，有2个* FT替换需要搜索T细胞。
• 对于方法2，有FT替换，需要搜索T细胞，然后再进行T替换。

由于相同的单词可能会在“目标”范围内重复出现，因此“目标”范围的搜索必须检查每个单元格。但是，如果我们在FromTo范围的From列中搜索Target范围内的每个条目怎么办？除非目标范围中的大量单词从FromTo范围中丢失，否则将在找到匹配项之前检查平均FT / 2条目。同样，也不需要第二遍。我们期望基于这种逻辑的第三种方法，是方法2持续时间的一半。

我重新编码了例程的主要部分并重复了测试

                           First    Second      Third
FromTo  Target  Target  duration  duration   duration
  Rows    Rows    Cols   in secs   in secs    in secs
50,000      20       1       103        52        .13
50,000  10,000       1       486       257      61.51 
10,000   3,000       2        60        32       7.54

与我预期的相比，持续时间的减少要大得多。我对原因有一些猜测，但我没有进一步调查。我相信最后的期限是可以接受的。我还有一个主意，但我认为不值得花时间进行研究。

上面的主要课程是：在实施之前先考虑一下实施策略。对我来说，技术3显然优于技术1和2，我本来应该从该技术开始的。花一些时间在初始设计上可以使自己获得丰厚的回报。

第4部分-最终解决方案

您发布了两个工作簿，每个工作簿在Sheet1中都有Target范围，在Sheet2中有FromTo范围。我创建了一个工作簿，并将第二个工作簿中的数据复制到Sheet3和Sheet3。

我修改了您的宏以调用我的宏：

Option Explicit
Sub xLator2()

  Dim RngTgt As Range
  Dim RngFromTo As Range
  Dim RowMax As Long
  Dim TimeStart  As Single

  With Worksheets("Sheet1")
    RowMax = .Cells(Rows.Count, "A").End(xlUp).Row
    Set RngTgt = .Range(.Cells(1, "A"), .Cells(RowMax, "A"))
  End With

  With Worksheets("Sheet2")
    RowMax = .Cells(Rows.Count, "A").End(xlUp).Row
    Set RngFromTo = .Range(.Cells(1, "A"), .Cells(RowMax, "B"))
  End With

  TimeStart = Timer
  Call Translate3(RngTgt, RngFromTo)
  Debug.Print "Sheet1 technique 3 duration: " & Timer - TimeStart

  With Worksheets("Sheet3")
    RowMax = .Cells(Rows.Count, "A").End(xlUp).Row
    Set RngTgt = .Range(.Cells(1, "A"), .Cells(RowMax, "A"))
  End With

  With Worksheets("Sheet4")
    RowMax = .Cells(Rows.Count, "A").End(xlUp).Row
    Set RngFromTo = .Range(.Cells(1, "A"), .Cells(RowMax, "B"))
  End With

  TimeStart = Timer
  Call Translate3(RngTgt, RngFromTo)
  Debug.Print "Sheet3 technique 3 duration: " & Timer - TimeStart

End Sub

我针对技术3的宏将两个范围作为其参数，因此可以将多个Target和FromTo范围放置在方便的地方：

Sub Translate3(ByVal RngTgt As Range, ByVal RngFromTo As Range)

  ' RngTgt     A rectangle containing words to be translated
  ' RngFromTo  Two columns with the left column containing the original values
  '            for words and the right column containing the values to replace
  '            the original values.

  ' Constants numbering the From and To columns within RngFromTo.  This makes the
  ' code easier to understand than if 1 and 2 had been used.
  Const ColFrom As Long = 1
  Const ColTo As Long = 2

  Dim ColTgtCrnt As Long
  Dim Test As String
  Dim RngFindStart As Range
  Dim RngFrom As Range
  Dim RngTemp As Range
  Dim RowFromTo As Long
  Dim RowTgtCrnt As Long
  Dim TgtTable As Variant

  ' Check FromTo range has two columns
  If RngFromTo.Columns.Count <> 2 Then
    Call MsgBox("ToFrom table must have two columns", vbOKOnly)
    Exit Sub
  End If

  ' Load Target range to array
  TgtTable = RngTgt.Value

  ' Set RngFrom to the From column of RngFromTo
  Set RngFrom = RngFromTo.Columns(ColFrom)
  ' Set RngFindStart to first cell of RngFrom
  Set RngFindStart = RngFrom.Rows(1)

  ' Loop for every entry in Target table
  For RowTgtCrnt = 1 To UBound(TgtTable, 1)
    For ColTgtCrnt = 1 To UBound(TgtTable, 2)
      Set RngTemp = RngFrom.Find(What:=TgtTable(RowTgtCrnt, ColTgtCrnt), _
                                 After:=RngFindStart, _
                                 LookAt:=xlWhole)
      If Not RngTemp Is Nothing Then
        ' This target cell is to be translated
        ' Replace value in Target table with value for To column of FromTo table
        TgtTable(RowTgtCrnt, ColTgtCrnt) = RngTemp.Offset(0, ColTo - ColFrom).Value
      End If
    Next
  Next

  ' Upload updated array back to Target range
  RngTgt.Value = TgtTable

End Sub

这里有很多东西要学习。慢慢来，查找您不理解的任何陈述。如有必要，请回答一些问题，但是您对自己的了解越多，您的发展就会越快。