我试图在名称为“ wsPivotPreCCI”的Excel工作表中构建VBA宏。
在“ C”列中,我想找到多个文本字符串,并将其替换为相同的文本字符串。
例:
Find: "TIPS TELEPHONE", "TIPS TELEPHONE OTHER", "TIPS, TELEPHONE", or "TIPS,TELEPHONE"
Replace All with: "TIPS, TELEPHONE, OTHER"
经过数小时的研究和多次尝试(以下说明),我发现这些帖子是最有用的,但是我似乎仍然无法正确地完成“循环和替换”。
我的查找/替换数组是:
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)用String替换Array
因此,我将的范围RplcTips
从调整Array
为String
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列中所有这些值的最佳方法是什么?
是的,替换值RplcTips
应该是a,String
因为您要处理的是一个值,而不是一组值。
然后,您无需遍历要替换的单元格。调用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
参数的详细信息,请参阅文档。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句