vba 对象变量或未设置变量

Object Variable or With Variable not set

提问by speci

Learning some VBA. So far, I've constructed this piece of code which should allow me (though it does not, yet) to do the following thing:

学习一些VBA。到目前为止，我已经构建了这段代码，它应该允许我（尽管还没有）执行以下操作：

1. Get the number in the "M" & icell (in the first iteration it is M5).
2. Find that number in the A column.
3. Once it finds it, set the value of PutHereIfFoundthe same as the value of F6 (hence the offset).
4. If a number is found, then increment i in order for the loop to continue searching for M6, M7, ... all up to cell M20.

1. 获取"M" & i单元格中的数字（在第一次迭代中它是 M5）。
2. 在 A 列中找到该数字。
3. 一旦找到它，设置与PutHereIfFoundF6 的值相同的值（因此是偏移量）。
4. 如果找到一个数字，则增加 i 以使循环继续搜索 M6、M7、... 直到单元格 M20。

It's returning a Run-Time Error 91, which stands for Object Variable or With Variable not set. When I debug, it points to the Set PuthereIfFoundline.

它返回 a Run-Time Error 91，它代表Object Variable or With Variable not set。当我调试时，它指向该Set PuthereIfFound行。

What is the reason for this mistake?

这个错误的原因是什么？

 Sub FindThis()
    Dim FindThis As Range
    Dim PutHereIfFound As Range
    Dim i As Integer
    Dim f As Integer

    i = 5
    f = 5
    Do
        Set FindThis = ActiveSheet.Range("M" & i)
        Set PutHereIfFound = ActiveSheet.Range("N" & i)
            With ActiveSheet.Range("A:A")
                Set PutHereIfFound = .Find(What:=FindThis, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False).Offset(0, 5)

                If Not PutHereIfFound Is Nothing Then
                    i = i + 1
                Else
                    i = i                       
                End If                                    
            End With
     Loop While i <= 20
End Sub

采纳答案by Siddharth Rout

Further to my comment, your code can be optimized like this.

除了我的评论之外，您的代码可以像这样优化。

 Sub FindThis()
    Dim ws As Worksheet
    Dim FindThis As String
    Dim aCell As Range
    Dim i As Long

    Set ws = Sheets("Sheet1")

    With ws
        For i = 5 To 20
            FindThis = .Range("M" & i).Value

            Set aCell = .Columns(1).Find(What:=FindThis, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

            If Not aCell Is Nothing Then
                '~~> Do whatever you want here with the F Value
                PutHereIfFound = aCell.Offset(, 5).Value

                Debug.Print PutHereIfFound
            End If
        Next i
    End With
End Sub

回答by Doug Glancy

In answer to your question about the Object Variable or With Variable Not Seterror, it means that FindThiswasn't found and the Findreturned Nothing.

在回答您关于Object Variable or With Variable Not Set错误的问题时，这意味着FindThis未找到并且Find返回了Nothing.