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]

Please post ALL the code, including the Main control macro, Sub and End Sub lines, and variables, if any, that are applicable to both procedures in the module (i.e. dimensioned above the first Sub line) so we can see where calls to the 2 procedures you posted are made and discern what changes might be made to the search ranges and when those occur. Possibly, the first routine removes the search term the 2nd routine will search for, thereby causing the run time error. Please also indicate which line in which called routine is highlighted when the error occurs.

 

[Mike2]

Hi JoeMo,
Thank you with much appreciation of you trying to help to resolve my issue.
Is it possible for me to send you the file on an email instead of posting ALL the codes on the forum?
It would be more efficient that way. Also, please note, I will only going to send you all of the coding without the related DATA FILE
as the data is the confidential information from my employer. I hope you will understand and still be able to assist.
If sending you an email is a possible method, please provide me with the email address. Thanks,

 

[JoeMo]

Hi JoeMo,
Thank you with much appreciation of you trying to help to resolve my issue.
Is it possible for me to send you the file on an email instead of posting ALL the codes on the forum?
It would be more efficient that way. Also, please note, I will only going to send you all of the coding without the related DATA FILE
as the data is the confidential information from my employer. I hope you will understand and still be able to assist.
If sending you an email is a possible method, please provide me with the email address. Thanks,

If you can substitute some non-confidential data for the data file and confirm that the substituted version still displays your problem(s), you can PM me and I will respond with an email address. My time is limited right now and I don't want to work with anything that's missing something.

 

[Mike2]

Hi JoeMo,

I have shorten up the coding to the issue I am encountering and have also substituted some non-confidential data on the data file.
Tested the coding and the problem still exists.

I am ready to send you the coding and the files to see if you can spot out where the problem is.

Thanks.​

 

[JoeMo]

PM me and I will send you an email address.