vba - Excel 中的 FOR 循环 - 查找和替换

Question

我有以下代码

Sub CleanCat()
Dim i As Integer

For i = 1 To 50

    Columns("A").Replace What:="Cat" & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="Cat " & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="Category " & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="Category" & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    Columns("A").Replace What:="cat" & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="cat " & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="category " & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="category" & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Next                                                     
End Sub

我想要的是遍历 A 列中的每个单元格并执行所示的替换(我正在遍历推文)，但这并不能替换所有内容。我得到诸如 something cat 13 here 之类的东西剩下

推文示例:

@thisaccount  I nominate @thataccountfor category 12 #somehashtag
Cat 12 I nominate @thisaccount #somehashtag

有任何想法吗？

Answer 1

没有数字循环的另一个选项。 (注意:我建立在@Jeeped 的回答之上)

它还将单词放在一个数组中，以便于更新。

Sub CleanCat()
    Dim i As Long
    Dim srch() As Variant
    Dim srchPart As Variant

    srch = Array("Category ", "Category", "Cat ", "Cat") ' make sure this is in order longest to shortest.

    With Worksheets("Sheet1")
        .Columns("A") = .Evaluate("INDEX("'" & A:A,)")
        .Columns("A").Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart

        For Each srchPart In srch
            .Columns("A").Replace What:=srchPart, Replacement:="}}}}", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Next srchPart

        .Columns("A").Replace What:="}}}}", Replacement:="Category", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End With
End Sub

我还偷了@Jeeped 的公式来测试: