Run Time Error '91' : Object Variable or With Block not Set

Sathish G

New Member
Joined
Aug 16, 2017
Messages
21
Am getting run time error on green marked line. Anyone please help to resolve this error.

Code:
Sheets("Sheet2").Select
Columns("A:A").Select
lr = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Set r3Search = Sheets("Sheet2").Range("A1:A" & lr)
Set r3Search2 = Sheets("Sheet2").Range("A1:A" & lr)
Set r3Find = r3Search.Find("<epcrealmsforroaminglist>")
If r3Find Is Nothing Then
End If
On Error GoTo Handler2
s3FirstAddress = r3Find.Address  -------> [COLOR=#6600cc]Getting run time error.[/COLOR]
Set r3Find2 = r3Search.Find(What:="</epcrealmsforroaminglist>", After:=r3Find)
If r3Find2 Is Nothing Then
Exit Sub
ElseIf r3Find2.Row < r3Find.Row Then
Exit Sub
End If
s3FirstAddress2 = r3Find2.Address
Sheets("Sheet2").Range(r3Find.Offset(1), r3Find2.Offset(-1)).Copy Sheets("work").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Handler2:


Do
Set r3Find = r3Search.Find(What:="<epcrealmsforroaminglist>", After:=r3Find)
If r3Find.Address = s3FirstAddress Then Exit Do
Set r3Find2 = r3Search.Find(What:="</epcrealmsforroaminglist>", After:=r3Find)
If r3Find2.Address = s3FirstAddress2 Then Exit Do
Sheets("Sheet2").Range(r3Find.Offset(1), r3Find2.Offset(-1)).Copy Sheets("work").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Loop
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,121
Office Version
365
Platform
Windows
That error means that r3Find is Nothing
@Danmc
You don't use Set for strings, only objects.
 
Last edited:

Sathish G

New Member
Joined
Aug 16, 2017
Messages
21
Hi fluff if r3find is nothing how to resume next ? without error
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,121
Office Version
365
Platform
Windows
What do you want to happen?
If r3Find is nothing, r3Find2 will also be nothing.
 

Sathish G

New Member
Joined
Aug 16, 2017
Messages
21
If r3Find is nothing, r3Find2 also nothing means then i need code to skip both to execute next.

For example:

If r3Find is nothing, r3Find2 also nothing

Then it have to go next for r4Find and r4Find2 without showing any error.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,121
Office Version
365
Platform
Windows
Maybe
Code:
   Sheets("Sheet2").Select
   Columns("A:A").Select
   LR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
   Set r3Search = Sheets("Sheet2").Range("A1:A" & LR)
   Set r3Search2 = Sheets("Sheet2").Range("A1:A" & LR)
   Set r3Find = r3Search.Find("")
   If Not r3Find Is Nothing Then
      s3FirstAddress = r3Find.Address
      Set r3Find2 = r3Search.Find(What:="", After:=r3Find)
      If r3Find2 Is Nothing Then
         Exit Sub
      ElseIf r3Find2.Row < r3Find.Row Then
         Exit Sub
      End If
      s3FirstAddress2 = r3Find2.Address
      Sheets("Sheet2").Range(r3Find.Offset(1), r3Find2.Offset(-1)).Copy Sheets("work").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
      
      
      Do
         Set r3Find = r3Search.Find(What:="", After:=r3Find)
         If r3Find.Address = s3FirstAddress Then Exit Do
         Set r3Find2 = r3Search.Find(What:="", After:=r3Find)
         If r3Find2.Address = s3FirstAddress2 Then Exit Do
         Sheets("Sheet2").Range(r3Find.Offset(1), r3Find2.Offset(-1)).Copy Sheets("work").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
      Loop
   End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,121
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,077,649
Messages
5,335,473
Members
399,017
Latest member
npatel917

Some videos you may like

This Week's Hot Topics

Top