Results 1 to 10 of 10

Thread: Run Time Error '91' : Object Variable or With Block not Set
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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("")
    If r3Find Is Nothing Then
    End If
    On Error GoTo Handler2
    s3FirstAddress = r3Find.Address  -------> Getting run time error.
    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)
    Handler2:
    
    
    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
    Last edited by Fluff; Jun 20th, 2019 at 07:39 AM. Reason: code tags

  2. #2
    New Member
    Join Date
    Nov 2015
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    try this:

    Code:
    set s3FirstAddress = r3Find.Address

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,071
    Post Thanks / Like
    Mentioned
    410 Post(s)
    Tagged
    43 Thread(s)

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

    That error means that r3Find is Nothing
    @Danmc
    You don't use Set for strings, only objects.
    Last edited by Fluff; Jun 20th, 2019 at 07:13 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Nov 2015
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    thanks fluff, thought it was a range (address).

  5. #5
    New Member
    Join Date
    Aug 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi fluff if r3find is nothing how to resume next ? without error

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,071
    Post Thanks / Like
    Mentioned
    410 Post(s)
    Tagged
    43 Thread(s)

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

    What do you want to happen?
    If r3Find is nothing, r3Find2 will also be nothing.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Aug 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,071
    Post Thanks / Like
    Mentioned
    410 Post(s)
    Tagged
    43 Thread(s)

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

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Aug 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thank you so much fluff its working great!!! thanks you your wonderful support

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,071
    Post Thanks / Like
    Mentioned
    410 Post(s)
    Tagged
    43 Thread(s)

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

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •