vba 对象变量或未设置块 -
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10940455/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Object variable or with block not set -
提问by speci
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
所以，我有一个工作表，我想在其中搜索 Range("A:A") 中 Range("M" & i) 的值。但是，当我尝试运行此代码时，它返回一个错误：“运行时错误'91'：对象变量或未设置块。当我单击调试时，它发现错误
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
回答by Siddharth Rout
I have not tested it but is this what you are trying to do?
我还没有测试过，但这是你想要做的吗？
When assigning a range, you have to use SET
分配范围时，您必须使用 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
编辑：此外，建议使用完整路径，否则搜索将始终发生在活动工作表中
For example
例如
Set SearchIn = Sheets("Sheet1").Range("A1:A740")
Similarly for others.
对于其他人也是如此。
