搜索特定字符串的单元格范围,并替换
问题描述:
我想搜索某些字符串值的单元格的范围,并用其他东西替换它们。目前,我正在试图从AS22检查一个列范围到?搜索特定字符串的单元格范围,并替换
我想寻找的是:
Green
Amber
Red
并将其替换:
On Track
Minor Variance
Major Variance
这是我到目前为止的代码,但它不工作,我不接受任何错误 - 不知道有什么错误。
Dim i As Variant
Dim k As Variant
i = ("Green, Amber, Red")
k = ("On Track, Minor Variance, Major Variance")
Columns("AS").Replace What:=i, Replacement:=k, lookat:=xlPart, MatchCase:=False
理想情况下,我想一次搜索AS,AT,AU列,而不必重复三次代码。
任何人都了解的原因一些轻为什么这个代码将无法正常工作
答
考虑:
Sub qwerty()
With Range("AS:AU")
.Replace what:="Green", Replacement:="On Track"
.Replace what:="Amber", Replacement:="Minor Variance"
.Replace what:="Red", Replacement:="Major Variance"
End With
End Sub
+0
谢谢,这是一个更简单的解决方案。它工作得很好 – user3088476
答
使用指定的值作为阵列到相应的Variant变量,然后循环通过他们的LBound function和UBound function来定义范围。
Dim i As Variant, k As Variant, w As Long
i = Array("Green", "Amber", "Red")
k = Array("On Track", "Minor Variance", "Major Variance")
With Worksheets("Sheet1") '<~~you should know what worksheet you are on~
For w = LBound(i) To UBound(i)
.Columns(45).Replace what:=i(w), replacement:=k(w), _
lookat:=xlWhole, MatchCase:=False
Next w
End With
按照我上面的评论,我已经与lookat:=xlWhole
更换您的lookat:=xlPart
。
答
Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long
fndList = Array("Red", "Amber", "Green")
rplcList = Array("On track", "Minor Variance", "Minor Variance")
'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht
Next x
你为什么在看'xlPart'?你想用'StoMajor Variance'替换'Stored'吗? – Jeeped
这是我在Google搜索中找到的一个解决方案,曾尝试过几件事,但无法使其正常工作 – user3088476