时间戳

首页 > 解决方案 > 如何使用数组查找/替换选定单元格中的多个值？

arrays - 如何使用数组查找/替换选定单元格中的多个值？

问题描述

我正在尝试在 Excel 工作表名称“wsPivotPreCCI”中构建 VBA 宏。

在“C”列中，我想找到多个文本字符串，并将其替换为相同的文本字符串。
例子：

Find: "TIPS TELEPHONE", "TIPS TELEPHONE OTHER", "TIPS, TELEPHONE", or "TIPS,TELEPHONE"
Replace All with: "TIPS, TELEPHONE, OTHER"

经过数小时的研究和多次尝试（如下所述），我发现这些帖子是最有帮助的，但我似乎仍然无法正确使用循环和替换。

VBA使用数组查找/替换单元格范围

使用数组 VBA 查找和替换数据库中的值

我的查找/替换数组是：

FindTips = Array("TIPS TELEPHONE", "TIPS TELEPHONE OTHER", "TIPS, TELEPHONE", "TIPS,TELEPHONE")
RplcTips = Array("TIPS, TELEPHONE, OTHER")
        
FindAuto = Array("AUTO - RENTAL, PARKING & TOLLS", "PARKING AND TOLLS", "PARKING TOLLS", _
"RENTAL PARKING & TOLLS", "RENTAL PARKING TOLLS", "AUTO RENTAL, PARKING & TOLLS")
RplcAuto = "AUTO - RENTAL, PARKING & TOLLS"
        
FindMisc = Array("MISCELLANEOUS EXPENSE", "MISCELLANEOUS")
RplcMisc = "MISCELLANEOUS EXPENSE"
        
FindTraining = Array("TRAINING & SEMINARS", "TRAINING & SEMINARS-OTHERS")
RplcTraining = "TRAINING & SEMINARS"

这是我的当前代码。我只是一个查找/替换数组示例（但我确实需要替换所有数组）：

Options Explicit
Sub Multi_FindReplace()

With wsPivotPreCCI
    Dim Lrow As Long
    Dim Rng As Range
    Dim FindTips As Variant
    Dim RplcTips As Variant
    Dim i As Long
    
    Lrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set Rng = .Range("C2:C" & Lrow)
    
    FindTips = Array("TIPS TELEPHONE", "TIPS TELEPHONE OTHER", "TIPS, TELEPHONE", "TIPS,TELEPHONE")
    RplcTips = Array("TIPS, TELEPHONE, OTHER")
    
    'Loop through each item in array list

End With
End Sub

这些是我尝试使用的不同行，错误出现在发生错误的行上。

    For i = LBound(FindTips) To UBound(FindTips)
        Rng.Cells.Replace What:=FindTips(i), Replacement:=RplcTips(i)  '<-Subscript Out of Range
    Next i

    For i = LBound(FindTips) To UBound(FindTips)
        .Cells.Replace FindTips(i, 1), RplcTips(i, 1), xlWhole, xlByRows   '<-Subscript out of Range
    Next

    Dim arr As Variant
    For i = LBound(FindTips) To UBound(FindTips)
        For Each arr In Rng
            Rng.Cells.Replace What:=FindTips(i), Replacement:=RplcTips(i)  '<-Subscript out of Range
        Next arr
    Next i

    With Rng
        For i = LBound(FindTips) To UBound(FindTips)
            .Cells.Replace FindTips(i), RplcTips(i)  '<-Subscript out of Range
        Next
    End With

'Finally Looped through Column "C", but All the cells changed, and then received Subscript out of range
    For i = LBound(FindTips) To UBound(FindTips)
        For Each FindTips In Rng
            Rng.Cells.Replace What:=FindTips(i), Replacement:=RplcTips(i)
        Next FindTips
    Next i

在最后一次尝试之后，我发现了这个帖子： VBA (Microsoft Excel) replace Array with String

所以，我将 a 调整RplcTips为ArrayaString

Options Explicit
Sub Multi_FindReplace()

With wsPivotPreCCI
    Dim Lrow As Long
    Dim Rng As Range
    Dim FindTips As Variant
    Dim RplcTips As String
    Dim i As Long
    
    Lrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set Rng = .Range("C2:C" & Lrow)
    
    FindTips = Array("TIPS TELEPHONE", "TIPS TELEPHONE OTHER", "TIPS, TELEPHONE", "TIPS,TELEPHONE")
    RplcTips = "TIPS, TELEPHONE, OTHER"
    
    'Loop through each item in array list
    For i = LBound(FindTips) To UBound(FindTips)
        For Each FindTips In Rng
            Rng.Cells.Replace FindTips(i), RplcTips
        Next FindTips
    Next i

End With
End Sub

此代码仍然将每个单元格（在 C 列中）更改为该Rplctips值，并且似乎继续查找（直到我停止宏）。

问题 1：替换值是否应该String代替Array？

问题 2：替换 C 列中所有这些值的最佳方法是什么？

标签： arraysexcelvbaloops

解决方案

是的，替换值RplcTips应该是 aString因为您正在处理一个值，而不是一组值。

然后，您不需要遍历单元格来替换。调用Replace整个范围。改变

For i = LBound(FindTips) To UBound(FindTips)
    For Each FindTips In Rng
        Rng.Cells.Replace FindTips(i), RplcTips
    Next FindTips
Next i

至

For i = LBound(FindTips) To UBound(FindTips)
    Rng.Replace FindTips(i), RplcTips, xlWhole, xlByColumns, True
Next i

有关参数的详细信息，请参阅Range.Replace文档。

推荐阅读