Object variable or with block not set -
excel, vba
Solution
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
For example
Set SearchIn = Sheets("Sheet1").Range("A1:A740")
Similarly for others.
Problem
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 ```