I
Imran J Khan
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
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