在 Excel 中查找公式的替换值但增量且循环

在 Excel 中查找公式的替换值但增量且循环
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

相关内容