Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim p As Integer
Dim q As Integer
p = 3
q = p + 1
fnd = "$3"
rplc = "$" & q
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht
p=q
End Sub
我在 A 列到 O 列中有此公式,并且单元格引用每列都在变化
='[MASTER_MASTER - Copy.xlsx]DATA 02'!$A$8
我要做的就是将最后两个字符“$8”替换为下一个值,即“$9”。上面给出的示例代码很好,但是我无法循环运行它。我想单击 vba 运行按钮,并期望每次单击都会将值增加到 +1(从 8 到 9 再到 10),直到 2500。我该怎么做?运行上述代码,但每次运行时将 P 的值增加 1?
答案1
每次运行宏时,将“q”保存在工作簿单元格中。然后每次运行宏时从该单元格读取它。
如果保存它的单元格位于工作表“参数”中的单元格 A1 上,那么开头如下:
q = ActiveWorkbook.Worksheets("parameters").Range("A1") + 1
最后是这样的:
ActiveWorkbook.Worksheets("parameters").Range("A1") = q
当然,您需要确保现有的代码不会处理新表:
For Each sht In ActiveWorkbook.Worksheets
if sht.Name <> "parameters" then
sht.Cells.Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
end if
Next sht
您可能还会考虑在宏中构建公式而不是使用Cells.Replace
,但这不是 100% 必要的。
答案2
稍加改动就能满足我的需求,感谢您提供的解决方案
Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Static p As Integer
Static q As Integer
p = ActiveWorkbook.Worksheets("master01").Range("A1") + 1
q = ActiveWorkbook.Worksheets("master01").Range("A2") + 1
fnd = "$" & p
rplc = "$" & q
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
ActiveWorkbook.Worksheets("master01").Range("A1") = p
ActiveWorkbook.Worksheets("master01").Range("A2") = q
Next sht
End Sub