☰

在 Excel 2007 中查找并替换一列中的多个单词

tag-icon • tag-icon microsoft-excel-2007 vba macros
在 Excel 2007 中查找并替换一列中的多个单词 
Sub xLator2()
Dim s1 As Worksheet, s2 As Worksheet
Dim N As Long, i As Long
Dim from(), too()
Set s1 = Sheets("Sheet1") '   contains the data
Set s2 = Sheets("Sheet2") '   contains the translation table

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

s1.Activate

For i = LBound(from) To UBound(from)
    Cells.Replace What:=from(i), Replacement:=too(i)
Next i
End Sub

我正在使用上述代码在下面提到的工作表中查找和替换多个单词（“Column A Sheet1”中的单词以及“Column B Sheet 2”中的单词）：

https://docs.google.com/spreadsheets/d/15TRLccDr_EAR8s78u-WGSkGpAecBf42_lhRkjCev_WE/edit?usp=sharing

但是，当我在另一张工作表中（如下所述）将其应用于另一个数据时，代码失败，即我在 Sheet1 中得到了扭曲的文字：

https://docs.google.com/spreadsheets/d/14ba9pQDjMPWJd4YFpGffhtVcHxml0LdUUVQ0prrOEUY/edit?usp=sharing

请帮助我，以便我可以用“列 B 表 2”中的单词替换“列 A 表 1”中的单词

注意：以上链接是谷歌电子表格的，但我在 Excel 2007 表中遇到了问题。

由于我不擅长 VBA，我请求您帮助我，提供整个修改后的代码

答案1

我假设您想要的是只进行一次替换，并在替换完成后停止进一步的规则。以您的第二张表为例，第 12 行“but”应翻译为“however”，并停止进一步的规则，以便“however”不会翻译为“hoyouever”（因为规则 #17 将“we”翻译为“you”）。

一种解决方法是先将所有内容翻译成某个中间符号，然后在第二轮中将中间符号翻译成所需的替换符号。 稍微修改一下代码即可，如下所示：

Sub xLator2()
Dim s1 As Worksheet, s2 As Worksheet
Dim N As Long, i As Long
Dim from(), too()
Set s1 = Sheets("Sheet1") '   contains the data
Set s2 = Sheets("Sheet2") '   contains the translation table

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

s1.Activate

' -------------- Modification starts here --------------------------
' Replace from from(i) to __MYREPLACEMENTi__  (where i is the counter)
For i = LBound(from) To UBound(from)
    Cells.Replace What:=from(i), Replacement:="__MYREPLACEMENT" + Str(i) + "__"
Next i
' Replace from __MYREPLACEMENTi__ to too(i)  (where i is the counter)
For i = LBound(from) To UBound(from)
    Cells.Replace What:="__MYREPLACEMENT" + Str(i) + "__", Replacement:=too(i)
Next i
' -------------- Modification ends here --------------------------
End Sub

相关内容