对象变量或块未设置 - [英] Object variable or with block not set -

问题描述

所以，我有一个工作表，我想在Range（A：A）中搜索Range（M& i）的值。但是，当我尝试运行这个代码时，它返回一个错误：运行时错误'91'：对象变量或块未设置当我点击调试，它在

So, I have a worksheet, in which I want to search for a value of Range("M" & i) in Range("A:A"). However, when I try to run this code, it returns an error: "Run-Time Error '91': Object Variable or With block not set. When I click debug, it finds an error on

 SearchIn = Range("A:A") 

我做了谷歌的互联网和这个网站（发现的东西），但我仍然无法解决问题。任何人都有线索。

I did google the internet and this site (found something), but I still can't solve the issue. Anyone got a clue?

Sub Find_Replace()

Dim i As Integer
Dim SearchIn As Range
Dim SearchedObject As Range
Dim FinalCell As Range
Dim SumCell As Range


i = 5
SearchIn = Range("A1:A740")
StartSearch = Range("A" & i)
FinalCell = Range("N" & i)

Do While i <= 740

SearchedObject = SearchIn.Find(What:="M" & i, After:=StartSearch, LookIn:=xlValues,          LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If SearchedObject.Value = Range("M" & i).Value Then FinalCell = FinalCell.Value + SearchedObject.Offset(0, 5).Value

Loop


End Sub

推荐答案

我没有测试过，但这是你想要做的？

I have not tested it but is this what you are trying to do?

分配范围时，您必须使用 SET

When assigning a range, you have to use SET

Sub Find_Replace()
    Dim i As Integer
    Dim SearchIn As Range
    Dim SearchedObject As Range
    Dim FinalCell As Range
    Dim SumCell As Range

    i = 5

    Set SearchIn = Range("A1:A740")
    Set StartSearch = Range("A" & i)
    Set FinalCell = Range("N" & i)

    Do While i <= 740

        Set SearchedObject = SearchIn.Find(What:="M" & i, After:=StartSearch, _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        If Not SearchedObject Is Nothing Then
            If SearchedObject.Value = Range("M" & i).Value Then _
            FinalCell.Value = FinalCell.Value + SearchedObject.Offset(0, 5).Value
        End If
    Loop
End Sub

编辑：建议使用完整路径，否则搜索将始终发生在活动工作表中

EDIT: Also it is advisable to use full path else the search will always happen in active sheet

例如

Set SearchIn = Sheets("Sheet1").Range("A1:A740")

与其他人一样。

这篇关于对象变量或块未设置 - 的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！