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,544
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,544
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,544
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,544
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,078,461
Messages
5,340,442
Members
399,375
Latest member
alwayssunny

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top