excel - 为什么缺少对象变量或 With-block 变量？

Question

为什么有一个

缺少对象变量或 With-bock 变量

如果我能在互联网上找到任何东西，我已经用谷歌搜索了，但我没有

Sub One_Find()
    Dim FieldRange As Range
    Dim FirstAddress As String
    FieldRange = Cells.Find(What:=ActiveCell.Value, LookIn:=xlValues, LookAt:=xlWhole)
    FirstAddress = FieldRange.Address

    Do
        FieldRange = Cells.FindNext(FieldRange)
    Loop While FieldRange.Address <> FirstAddress

    FieldRange.Value = "WORKS"
End Sub

标签： excelvba

Answer 1

一些一般规则：

1. 确保您的代码包含您要使用的工作簿和工作表。这可以通过使用With Statement

   With ThisWorkbook.Worksheets("Sheet1")
   End With
   

2. 避免ActiveCell使用单元格地址引用单元格。

3. 在使用之前，请FieldRange确保检查它是否Nothing存在。

   If FieldRange Is Nothing Then
       Debug.Print "Value was not found."
   Else
       Debug.Print "Value found in :" & FieldRange.Address
   End If
   

4. 一种Do … While结构

   Do While i < 5
       i = i + 1
       MsgBox "The value of i is : " & i
   Loop