I have looked thru the other postings for "error 91 Object variable or With
block variable not set", but have not been able to fiqure out what I need to
change in my code. I have a spread sheet that has pop up form that presents
records from range security_name as a list, and the user can highligth one of
the records from this list to delete, this deletes corresponding records from
other sheets in the workbook as well. This works about 50% of the time, but
the other 50% it does not. The bug is the error 91 mentioned above, and it
affects (or not) the same records. Any help is appreciated.

Code from the popup form:

Private Sub UserForm_Initialize()
Dim MyArray()

MyArray() = (Sheet10.Range("SecurityName").Value)
Me.ListBox1.List = MyArray
End Sub

Private Sub cmdEnter_Click()
Dim N As Integer
Dim Str As String

If Me.ListBox1.Value < "" Then
Str = Me.ListBox1.Value 'Assign String value of selected listbox1
option
'Delete Security from Weightage table
Set LastRow = Sheet4.Range("SecurityName_Weightage").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Core Model
Set LastRow = Sheet10.Range("SecurityName").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 1
Set LastRow = Sheet3.Range("SecurityName_1").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 2
Set LastRow = Sheet5.Range("SecurityName_2").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 3
Set LastRow = Sheet6.Range("SecurityName_3").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 4
Set LastRow = Sheet7.Range("SecurityName_4").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 5
Set LastRow = Sheet8.Range("SecurityName_5").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 6
Set LastRow = Sheet9.Range("SecurityName_6").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Record Transaction in Transaction Sheet
Set LastRow = Sheet2.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).EntireRow.Insert
LastRow.Offset(1, 0).Value = Date
LastRow.Offset(1, 1).Value = "Remove Security"
'Record Security name only, without stock symbol
LastRow.Offset(1, 2).Value =
Application.WorksheetFunction.Replace(Arg1:=Str, Arg2:=(InStr(Str, "(")),
Arg3:=8, Arg4:="")
'Removing Security from ListBox1 and Opening Messagebox to notify
user operation has taken palce
N = Me.ListBox1.ListIndex
Me.ListBox1.RemoveItem (N)
MsgBox (" The Security Named '" & Str & "' Removed")
Unload Me
Else
MsgBox ("Please Select a Security to Remove")


End If
End Sub

Hi

As you don't state which line is throwing the error, it's not easy to help
you.

A guess would be that the selected string isn't found. To verify that try to
enter the line below in the immediate window in debug mode, when the error
has emerged.

If that's the case you will get the error when trying to delete a row. You
will need something like this:

If not LastRow is Nothing then
LastRow.EntireRow.Delete
Else
'Str wasn't found!
End if

Hopes this helps.

---
Per

"Imran J Khan" skrev i meddelelsen
...
I have looked thru the other postings for "error 91 Object variable or With
block variable not set", but have not been able to fiqure out what I need
to
change in my code. I have a spread sheet that has pop up form that
presents
records from range security_name as a list, and the user can highligth one
of
the records from this list to delete, this deletes corresponding records
from
other sheets in the workbook as well. This works about 50% of the time,
but
the other 50% it does not. The bug is the error 91 mentioned above, and it
affects (or not) the same records. Any help is appreciated.

Code from the popup form:

Private Sub UserForm_Initialize()
Dim MyArray()

MyArray() = (Sheet10.Range("SecurityName").Value)
Me.ListBox1.List = MyArray
End Sub

Private Sub cmdEnter_Click()
Dim N As Integer
Dim Str As String

If Me.ListBox1.Value < "" Then
Str = Me.ListBox1.Value 'Assign String value of selected listbox1
option
'Delete Security from Weightage table
Set LastRow = Sheet4.Range("SecurityName_Weightage").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Core Model
Set LastRow = Sheet10.Range("SecurityName").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 1
Set LastRow = Sheet3.Range("SecurityName_1").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 2
Set LastRow = Sheet5.Range("SecurityName_2").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 3
Set LastRow = Sheet6.Range("SecurityName_3").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 4
Set LastRow = Sheet7.Range("SecurityName_4").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 5
Set LastRow = Sheet8.Range("SecurityName_5").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 6
Set LastRow = Sheet9.Range("SecurityName_6").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Record Transaction in Transaction Sheet
Set LastRow = Sheet2.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).EntireRow.Insert
LastRow.Offset(1, 0).Value = Date
LastRow.Offset(1, 1).Value = "Remove Security"
'Record Security name only, without stock symbol
LastRow.Offset(1, 2).Value =
Application.WorksheetFunction.Replace(Arg1:=Str, Arg2:=(InStr(Str, "(")),
Arg3:=8, Arg4:="")
'Removing Security from ListBox1 and Opening Messagebox to notify
user operation has taken palce
N = Me.ListBox1.ListIndex
Me.ListBox1.RemoveItem (N)
MsgBox (" The Security Named '" & Str & "' Removed")
Unload Me
Else
MsgBox ("Please Select a Security to Remove")


End If
End Sub

I believe that when you used the Find method like this:

Set LastRow = Sheet4.Range("SecurityName_Weightage").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete

And Str is not found it will throw the error for Object Variable not set.
Maybe if you use an if statement like this:

Set LastRow = Sheet4.Range("SecurityName_Weightage").Find(Str,
LookIn:=xlValues)
If Not LastRow Is Nothing Then
LastRow.EntireRow.Delete
Else
MsgBox "Str Not Found"
End If

It is the only thing that I can see that might cause the message to display.




"Imran J Khan" wrote:

I have looked thru the other postings for "error 91 Object variable or With
block variable not set", but have not been able to fiqure out what I need to
change in my code. I have a spread sheet that has pop up form that presents
records from range security_name as a list, and the user can highligth one of
the records from this list to delete, this deletes corresponding records from
other sheets in the workbook as well. This works about 50% of the time, but
the other 50% it does not. The bug is the error 91 mentioned above, and it
affects (or not) the same records. Any help is appreciated.

Code from the popup form:

Private Sub UserForm_Initialize()
Dim MyArray()

MyArray() = (Sheet10.Range("SecurityName").Value)
Me.ListBox1.List = MyArray
End Sub

Private Sub cmdEnter_Click()
Dim N As Integer
Dim Str As String

If Me.ListBox1.Value < "" Then
Str = Me.ListBox1.Value 'Assign String value of selected listbox1
option
'Delete Security from Weightage table
Set LastRow = Sheet4.Range("SecurityName_Weightage").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Core Model
Set LastRow = Sheet10.Range("SecurityName").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 1
Set LastRow = Sheet3.Range("SecurityName_1").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 2
Set LastRow = Sheet5.Range("SecurityName_2").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 3
Set LastRow = Sheet6.Range("SecurityName_3").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 4
Set LastRow = Sheet7.Range("SecurityName_4").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 5
Set LastRow = Sheet8.Range("SecurityName_5").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Delete Security from Model 6
Set LastRow = Sheet9.Range("SecurityName_6").Find(Str,
LookIn:=xlValues)
LastRow.EntireRow.Delete
'Record Transaction in Transaction Sheet
Set LastRow = Sheet2.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).EntireRow.Insert
LastRow.Offset(1, 0).Value = Date
LastRow.Offset(1, 1).Value = "Remove Security"
'Record Security name only, without stock symbol
LastRow.Offset(1, 2).Value =
Application.WorksheetFunction.Replace(Arg1:=Str, Arg2:=(InStr(Str, "(")),
Arg3:=8, Arg4:="")
'Removing Security from ListBox1 and Opening Messagebox to notify
user operation has taken palce
N = Me.ListBox1.ListIndex
Me.ListBox1.RemoveItem (N)
MsgBox (" The Security Named '" & Str & "' Removed")
Unload Me
Else
MsgBox ("Please Select a Security to Remove")


End If
End Sub