Run-Time Error '91"

[Mike2]

Hello Everyone,

I have encounter a strange situation, the codes that i have below sometime runs without any error message, but
sometime it gives me a Run-Time Error '91': Object variable or With block variable not set

Can someone please help to explain why this happens and what can i do to resolve this situation?

It highlights : FoundVar=Range("B1:B" & LstRow).Find(what:=Var, LookIn:=xlValues).Row

Here is my code:


Dim Compare As String, LeftRes As String
Dim LstRow As Integer, Var As Integer, FoundVar As Integer, i As Integer, j As Integer

Range("B1").Value = "Item Code"
Range("D1").EntireColumn.Delete
Range("C1").Offset(0, 1).EntireColumn.Insert
Range("C1").Offset(0, 1).Value = "Cnt Qty"

LstRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1").Offset(0, 1).EntireColumn.Insert
Range("A1").Offset(0, 1).EntireColumn.NumberFormat = "@"

For i = 1 To LstRow
LeftRes = Left(Range("A" & i + 1), 4)
Range("B" & i + 1).Value = LeftRes
Next
Range("B2").Select
Var = "0201"


FoundVar = Range("B1:B" & LstRow).Find(What:=Var, LookIn:=xlValues).Row

For j = 1 To FoundVar
Compare = StrComp(Range("B2"), "0201")
If Compare <> 0 Then
ActiveCell.EntireRow.Delete
Else
Call PageFormat
Range("B1").EntireColumn.Delete
MsgBox "DONE !!", vbInformation, "Message Box"
Exit Sub
End If
Next

Thank you for your help in advance.

 

[JoeMo]

You get the error message when your search term (what:=Var) is not found. When it is found there is no error message.

The way I avoid such run time errors when using the Range.Find Method is illustrated below. Try it out with and w/o your search term being present.

Sub test()
Dim Var As Variant, FndRng As Range, FndVar As Long
Var = "joe"
Set FndRng = Range("B:B").Find(Var, , xlValues)
If Not FndRng Is Nothing Then
    FndVar = FndRng.Row
    MsgBox FndVar
Else
    MsgBox "search term not found in col B"
End If
End Sub

 

[Mike2]

Hi Joe,
Thank you so much for the explanation and suggesting the work around coding resolution for the issue.
I have incorporated your codes to mine and it works, but then it is causing my other subroutine to have the same
Error Code of Run-Time Error '91' and highlighted this line of code i have in that subroutine.

Rows(1).Find(what:"Value", lookat:=xlWhole).offset(1).Select

The funny thing is this line of code on the subroutine was running finely without any issue if i did not ran the other subroutine i mentioned
at the start of this thread. So, i am confuse why this would happen and how can i get around this issue?

 

[Mike2]

I discover the line of code in the subroutine will run again without any issue after i close down macro file, then re-open the macro file and rerun the subroutine.

Rows(1).Find(what:"Value", lookat:=xlWhole).offset(1).Select

Is there a way to clear the temporary subroutine cache so that there will not be a conflict between subroutines?
Does the temporary subroutine cache exist? Thanks.

 

[JoeMo]

I'm not aware of any temporary subroutine cache. You should fix the second subroutine in the same way I suggested you fix the first one. The run time error you get indicates the search term is not found.

 

[Mike2]

There has to be a conflict somewhere between both subroutines. Both subroutine works finely with no issue when it ran individually by itself at the start of the macro file.
It pops up the Run-Time Error '91' only after one of the two subroutine was ran.

I tried to modify the code from :
Rows(1).Find(what:="Value",lookat:=xlWhole).Offset(1).Select

to the codes below and ended up with the same Run-Time Error '91' code
Set FngRng = Rows(1).Find(what:="Value", lookat:=xlWhole)
If FngRng Is Nothing then
FngRng.offset(1).select
End If

when I change it to:
"If Not FngRng Is Nothing Then" instead

It just bypassed "FngRng.Offset(1).Select" that I need it to execute in order to carry on with the subroutine.

What am I missing from this code?

Thanks.

 

[JoeMo]

There has to be a conflict somewhere between both subroutines. Both subroutine works finely with no issue when it ran individually by itself at the start of the macro file.
It pops up the Run-Time Error '91' only after one of the two subroutine was ran.

I tried to modify the code from :
Rows(1).Find(what:="Value",lookat:=xlWhole).Offset(1).Select

to the codes below and ended up with the same Run-Time Error '91' code
Set FngRng = Rows(1).Find(what:="Value", lookat:=xlWhole)
If FngRng Is Nothing then
FngRng.offset(1).select
End If

when I change it to:
"If Not FngRng Is Nothing Then" instead

It just bypassed "FngRng.Offset(1).Select" that I need it to execute in order to carry on with the subroutine.

What am I missing from this code?

Thanks.

I think you are missing the point I've been trying to communicate: if the search term cannot be found in the designated range then FndRng will be Nothing. In that case, you can't select something that's offset from NOTHING. So, the lines in red above are going to cause a run time error as you have observed. Follow the example I posted in Post #2 so that you get a pop-up message when the search term cannot be found rather than a run time error.

 

[Mike2]

what I really don't understand is why the Run-Time Error '91' ONLY pops up when either one of the two subroutine was ran in a global Call of the macro
but not when they were ran individually in a local Call of the macro?

I have multiple Call of different subroutines to run controlled by a Global macro,
if one of these two subroutine was executed, then the Run Time Error would pops when the 2nd subroutine tries to run its code after the first one subroutine.

Then I will need to close the global/master control macro file, reopen the file and run the subroutine locally by itself for testing and works with no issue.
So, there is a conflict somewhere between the two subroutine.

With your suggestion to pop-up a message rather than a run time error when the search term is not found doesn't help to resolve my issue to continue to run the remaining lines of code in the subroutine.

I am sorry if I have not explained the issue clear enough to get the help I need.

 

[JoeMo]

Hard to know what's happening w/o seeing all the code you are running. If you post all the code, using code tags to make it more readable, perhaps someone here will be able to spot the problem.

 

[Mike2]

I have a main macro button to Call to execute these two subroutines as well as other subroutines in the same module.

Again, if each macro is to run individually on its own, then there is no Run-Time Error '91' message.
There is Run-Time Error '91' only when both subroutines are to be ran one after the other in the master macro execution.

Here is my coding for the 1st subroutine to run:

    Dim Compare As String, LeftRes As String
    Dim LstRow As Integer, i As Integer, j As Integer
    Dim Var As Variant
    Dim FndRng As Range
    Dim FoundVar As Long
            
    Range("B1").Value = "Item Code"
    Range("D1").EntireColumn.Delete
    Range("C1").Offset(0, 1).EntireColumn.Insert
    Range("C1").Offset(0, 1).Value = "Cnt Qty"
    
    LstRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1").Offset(0, 1).EntireColumn.Insert 
    Range("A1").Offset(0, 1).EntireColumn.NumberFormat = "@" 
    
    'To run a loop to get 4 characters from the left string and extract it into the next column
    For i = 1 To LstRow
        LeftRes = Left(Range("A" & i + 1), 4)
        Range("B" & i + 1).Value = LeftRes
    Next
    Range("B2").Select  
    Var = "0201"        


    '****Original coding before change to "Set FndRng = Range("B:B")" below ****
    'FoundVar = Range("B1:B" & LstRow).Find(What:=Var, LookIn:=xlValues).Row  
        
    Set FndRng = Range("B:B").Find(what:=Var, LookIn:=xlValues)
    If Not FndRng Is Nothing Then
        FoundVar = FndRng.Row
    End If
    
    'Using that range location as a stopping point for the search to a specific variable
    'then delete that row when the string comparision did not match
    For j = 1 To FoundVar
        Compare = StrComp(Range("B2"), "0201")  'To compare both string
        If Compare <> 0 Then
            ActiveCell.EntireRow.Delete
        Else
            Call PageFormat
            Range("B1").EntireColumn.Delete
            MsgBox "DONE !!", vbInformation, "Message Box"
            Exit Sub
        End If
    Next

Here is my coding for the 2nd subroutine to run sometime in my Main control macro, it is to run after the 1st set of subroutine coding.

    Dim Num As Integer
        
    Workbooks("Commercial - Accessories").Activate
   
    Num = Cells.Find(what:="Whs", after:=[A1], lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
    
    ActiveCell.CurrentRegion.AutoFilter field:=Num, Criteria1:=Array("1051"), Operator:=xlFilterValues
    ActiveCell.CurrentRegion.Copy
    Workbooks("Comm_Accessories Warehouse").Activate
    Range("A1").PasteSpecial
   
    Rows(1).Find(what:="Value", lookat:=xlWhole).Offset(1).Select
    LstRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row + 1
    Cells(LstRow, ActiveCell.Column).Formula = "=Round(" & "SUM(" & ActiveCell.Address(0, 0) & ":" & Cells(LstRow - 1, ActiveCell.Column).Address(0, 0) & ")" & ",2" & ")"
    
    ActiveCell.End(xlDown).Copy
    ActiveCell.Offset(0, 1).End(xlDown).Offset(1).PasteSpecial
    
    ActiveCell.CurrentRegion.Columns.AutoFit
    Call Freeze_Row
    Call ColorTitle
    Call LockProtect
    
    ActiveWorkbook.Save
    ActiveWorkbook.Close

Your help to a resolution is greatly greatly appreciated...