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

Sathish G

New Member
Joined
Aug 16, 2017
Messages
44
Office Version
  1. 2013
Platform
  1. Windows
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:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
That error means that r3Find is Nothing
@Danmc
You don't use Set for strings, only objects.
 
Last edited:
Upvote 0
Hi fluff if r3find is nothing how to resume next ? without error
 
Upvote 0
What do you want to happen?
If r3Find is nothing, r3Find2 will also be nothing.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Thank you so much fluff its working great!!! thanks you your wonderful support
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top