在 Excel 2007 中尋找並取代列中的多個單字

Question

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

請幫助我，以便我可以將“Column A Sheet1”中的單字替換為“Column B Sheet2”中的單字

注意：上面的連結已經給出了谷歌電子表格，但是我在 Excel 2007 工作表中遇到了問題。

我請求您提供完整修改後的程式碼來幫助我，因為我不擅長 VBA

Answer 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