我正在尝试在Mac版本的Excel上模拟"查找全部"功能(Windows版本具有的功能(。
我相信我已经设置了这个宏来解析活动工作表中的每个活动、使用的单元格。当它找到一个包含字母"K"的单元格时,我将其删除(用"(替换它,然后将单元格中的剩余值乘以 1000。
这就是我所拥有的。
Sub TestingFindAndReplace()
For Each Cell In ActiveSheet.UsedRange.Cells
Cells.Find(What:="K", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Replace What:="K", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
ActiveCell = ActiveCell * 1000
Next Cell
脚本按预期查找、替换和相乘。但是,当它用完包含"K"的单元格时
运行时错误"91"对象变量或未设置块变量
弹出并阻止我移动到宏的下一部分。
我猜当它不再有任何单元格来使"活动"(就像它在突出显示的行的末尾所做的那样(时,它后面的行,期待一个"ActiveCell",跳闸。
我尝试将 ActiveCell、Cell 和 Cells 设置为对象,因为从我能够找到的内容来看,看起来我需要将某种对象定义为某种对象才能继续。
相似但恕我直言稍微好一点
Sub Demo()
Dim r As Range
Dim c As Range
Dim FirstAddr As String
Set r = ActiveSheet.UsedRange
Set c = r.Find(What:="K", LookIn:=xlValues, Lookat:=xlPart, MatchCase:=True)
Do Until c Is Nothing
c.Value2 = Replace$(c.Value2, "K", "")
If IsNumeric(c.Value2) Then
c.Value2 = c.Value2 * 1000
End If
Set c = r.FindNext(c)
Loop
End Sub
我猜你在追求这样的事情(评论中的解释(:
Sub main()
Dim f As range
With ActiveSheet.UsedRange ' reference wanted range
Set f = .Find(what:="K", LookIn:=xlFormulas, lookat:=xlPart, MatchCase:=True) ' try and find first cell matching searched value in referenced range
If Not f Is Nothing Then ' if found
Do
f.Replace what:="K", replacement:="", lookat:=xlPart, MatchCase:=True ' replace matched string in found cell
f.value = f.value * 1000 ' update found cell
Set f = .FindNext(f) ' try and find next occurrence of searched value in referenced range
Loop While Not f Is Nothing
End If
End With
End Sub