excel - 如何使用 Excel 中的搜索宏将 e 和 é 读为同一事物

Question

我不太确定如何表达，但是，我有一个 Excel 宏，可以在我的工作簿中启用搜索功能。我的问题是我需要搜索才能将“é”理解为“e”。因此，如果我搜索“贝伦”，我的结果将是“贝伦”。我该怎么办呢？感谢您的宝贵时间和考虑。

Sub city()
   If ActiveSheet.Name <> "City" Then Exit Sub
   LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
   Sheets("Results").Range("3:10000").Delete
   SearchTerm = Application.InputBox("What are you looking for?")
   Application.ScreenUpdating = False
   Range("W1") = SearchTerm
   Range("W2:W" & LastRow).FormulaR1C1 = _
   "=IF(ISERR(SEARCH(R1C23,RC[-22]&RC[-21]&RC[-20]&RC[-19]&RC[-18]&RC[-17]&RC[-16]&RC[-15]&RC[-15]&RC[-14]&RC[-13]&RC[-12]&RC[-11]&RC[-10]&RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1])),0,1)"
   If WorksheetFunction.CountIf(Columns(23), 1) = 0 Then
      Columns(23).Delete
      Application.ScreenUpdating = True
      MsgBox "None found."
   Else
      For Each Cell In Range("A2:A" & LastRow)
          If Cell.Offset(, 22) = 1 Then
             Cell.Resize(, 51).Copy Sheets("Results").Range("A" & Rows.Count).End(xlUp).Offset(1)
             x = x + 1
          End If
      Next Cell
      Columns(22).Delete
      Application.ScreenUpdating = True
      If x = 1 Then
         MsgBox "1 matching record was copied to Search Results tab."
      Else
         MsgBox x & " matching records were copied to Search Results tab."
      End If
   End If
End Sub

Answer 1

您可以修改搜索参数，然后使用 like 运算符，如下所示:

Sub city()

   Dim rngResult As Range
   Dim searchTerm As String, counter As Integer
   Dim values As Variant, value As Variant

   If ActiveSheet.Name <> "City" Then Exit Sub

   'First Cell with the results
   Set rngResult = <First cell of the result Range>
   'Uses a variant array to get all values from the range. This speeds up the routine
   values = <Area of Search>.Value
   'Converts to lowercase to do a case insensitive search (e.g. Belem = belem)
   searchTerm = LCase(Application.InputBox("What are you looking for?"))
   If searchTerm = "" Then Exit Sub

   ' "§" is just a placeholder
   searchTerm = Replace(searchTerm, "e", "§")
   searchTerm = Replace(searchTerm, "é", "§")
   searchTerm = Replace(searchTerm, "§", "[eé]")
   Application.ScreenUpdating = False

   counter = 0
   For Each value In values
       If LCase(value) Like searchTerm Then
           rngResult = value
           Set rngResult = rngResult.Offset(1, 0) 'Moves to the next line
           counter = counter + 1
       End If
   Next value

   If counter = 0 Then
       MsgBox "None found."
   Else
       MsgBox "Found " & counter & " results"
       'Do what you need to do with the results
   End If

   Application.ScreenUpdating = True

End Sub

所有结果将在rngResult列中。

该代码的工作原理是将“e”和“é”替换为“§”，然后将“§”替换为“[eé]”，(例如“bélem” -> “bél§m” -> “b§l§m"-> "b[eé]l[eé]m")。

like 将匹配该位置上的“e”或“é”。您可以了解更多here或在帮助文件中。这是一个示例:

bélem Like "b[eé]l[eé]m" ' true
belem like "b[eé]l[eé]m" ' true
recife like "b[eé]l[eé]m" ' false

您可以通过添加其他条件来搜索更多图表，例如:

'Like will match "a","á", "à" and "ã"
searchTerm = Replace(searchTerm, "a", "§")
searchTerm = Replace(searchTerm, "á", "§")
searchTerm = Replace(searchTerm, "à", "§")
searchTerm = Replace(searchTerm, "ã", "§")
searchTerm = Replace(searchTerm, "§", "[aáàã]")

此方法的优点是您只需要一次“翻译”即可进行比较。如果您有大型数据集，这可以提高性能