我有一个范围
Dim r as range
该范围内的单元格可能是可见的,也可能是隐藏的。
我想在公式中搜索 $B$2 的所有实例,并将它们替换为 $C$3。
这样做的好方法是什么?
尝试类似这样的事情,
Dim r as range, fw as string, rw as string
on error resume next
application.calculation = xlcalculationmanual
set r = Range("A1:Z99").SpecialCells(xlCellTypeFormulas, 23)
if not r is nothing then
fw = "$B$2": rw = "$C$3"
r.Replace what:=fw, replacement:=rw, lookat:=xlPart, matchcase:=False
set r = nothing
end if
application.calculation = xlcalculationautomatic
编辑:真的不需要循环,所以我删除了它。
以下内容
也适用于过滤器隐藏的单元格:
Sub test()
Dim r As Range, fw As String, rw As String
On Error Resume Next
Set r = Range("A1:A5").SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not r Is Nothing Then
fw = "$B$2": rw = "$C$3"
For Each c In r.Cells
c.Replace what:=fw, replacement:=rw, lookat:=xlPart, MatchCase:=False
Next c
Set r = Nothing
End If
End Sub
我修改了吉普德发布的解决方案