run time error 91 object variable not set

[oniototo]

My VBA have this run time error 91 on the line below that underline with red color.
My MRStart1 didn't found anythings and jump to MBranch2: but if my MRStart2 didn't found anythings then the error 91 come out.
What is the problem guys?










If ThisWorkbook.Sheets("DATABASE").Range("D2").Value > 0 Then
On Error GoTo MBranch2
Dim MRStart1 As Range, MREnd1 As Range
Set MRStart1 = Columns("A").Find(What:=ThisWorkbook.Sheets("DATABASE").Range("D2").Value, LookIn:=xlValues, LookAt:=xlWhole).Offset(1)
Set MREnd1 = Columns("A").Find(What:="Total Sales For ", After:=MRStart1, LookIn:=xlValues, LookAt:=xlWhole).Offset(-1)
Range(MRStart1, MREnd1).Select
Selection.Value = ThisWorkbook.Sheets("DATABASE").Range("D2").Value
End If

MBranch2:
If ThisWorkbook.Sheets("DATABASE").Range("D3").Value > 0 Then
On Error GoTo MBranch3
Dim MRStart2 As Range, MREnd2 As Range
Set MRStart2 = Columns("A").Find(What:=ThisWorkbook.Sheets("DATABASE").Range("D3").Value, LookIn:=xlValues, LookAt:=xlWhole).Offset(1)
Set MREnd2 = Columns("A").Find(What:="Total Sales For ", After:=MRStart2, LookIn:=xlValues, LookAt:=xlWhole).Offset(-1)
Range(MRStart2, MREnd2).Select
Selection.Value = ThisWorkbook.Sheets("DATABASE").Range("D3").Value
End If

MBranch3:

 

[rollis13]

It's has something to do as On Error works but in my notes I didn't annotate the reason it happens, so just believe me , and try adding these two lines of code. MBranch3: will need the same treatment but changing reference:

'...
        Selection.Value = ThisWorkbook.Sheets("DATABASE").Range("D2").Value
    End If
MBranch2:
    Resume continue2                              '<- added
continue2:                                        '<- added
    If ThisWorkbook.Sheets("DATABASE").Range("D3").Value > 0 Then
        On Error GoTo MBranch3
        Dim MRStart2 As Range, MREnd2 As Range
'...

 

[oniototo]

It's has something to do as On Error works but in my notes I didn't annotate the reason it happens, so just believe me , and try adding these two lines of code. MBranch3: will need the same treatment but changing reference:

'...
        Selection.Value = ThisWorkbook.Sheets("DATABASE").Range("D2").Value
    End If
MBranch2:
    Resume continue2                              '<- added
continue2:                                        '<- added
    If ThisWorkbook.Sheets("DATABASE").Range("D3").Value > 0 Then
        On Error GoTo MBranch3
        Dim MRStart2 As Range, MREnd2 As Range
'...

it's work...but how and why?

 

[oniototo]

it's work...but how and why?

i just change the lines you added to resume next... and it's work too...but i still don't understand why

 

[StephenCrump]

It's has something to do as On Error works but in my notes I didn't annotate the reason it happens, so just believe me ....

@oniototo, on the first error, your error handler becomes active. Any second error will not be trapped by an On Error statement (current or new).
At its simplest:

Sub Test()

    Dim x As Long
    
    On Error GoTo Blah
    x = 1 / 0
    
Blah:
    x = 1 / 0   'will error!
    
End Sub

To get out of the error handling block, you'll need to use a Resume statement, as @rollis17 has done (or exit the Sub).

 

[oniototo]

@oniototo, on the first error, your error handler becomes active. Any second error will not be trapped by an On Error statement (current or new).
At its simplest:

Sub Test()

    Dim x As Long
   
    On Error GoTo Blah
    x = 1 / 0
   
Blah:
    x = 1 / 0   'will error!
   
End Sub

To get out of the error handling block, you'll need to use a Resume statement, as @rollis17 has done (or exit the Sub).

i see...thank you very much for the explaining

 

[rollis13]

@StephenCrump, I firstly thought that On Error Goto 0 would've been enought to fix the issue but was wrong.

Sub Test()
    Dim x      As Long
    On Error GoTo Blah
    x = 1 / 0
Blah:
    On Error GoTo 0             '<- here, why doesn't On Error reset
    On Error GoTo Blah2
    x = 1 / 0
Blah2:
End Sub

 

[StephenCrump]

I firstly thought that On Error Goto 0 would've been enought to fix the issue but was wrong ..

I assume you're thinking of:

On Error Resume Next
'Do something that may error
'Test for error and treat appropriately
On Error GoTo 0     'resets Err.Number to 0

Here, we have something different: On Error Goto SomeLabel.

When an error occurs, and execution is transferred to SomeLabel, you need to resolve the error before you can handle any second and subsequent errors with an On Error Goto SomeLabel or an On Error Resume Next. The only way to do this is with a Resume statement (which depending on context might be Resume, Resume Next or Resume SomeOtherLabel) or exit the Sub.

 

[rollis13]

Oh yes, it wasn't a Resume Next so that's why what I was suggesting in post #2 was resolving the issue.
Thank you StephenCrump