Run-Time error 91 object variable or with block variable not set

[gauravjain1604]

Hi, I am trying to retrieve a excel row (based on some calculation) into my userform, but am getting run time error 91 message which am not sure about. Below is the sample code. I am not sure which line is throwing error since I am able to get the data in userform but at the time of closing the userform it throws error. can anyone please help with this

Private Sub UserForm_Initialize()
Dim LastRow As Long
Dim i As Integer
Dim j, k, l, x, TotalCount As Integer
Dim str, analyst As String
Dim name As Variant


analyst = Application.UserName
TotalCount = 0
Sheets("sheet1").Activate
For i = 2 To 4
If Range("s" & i).Value = analyst Then
str = Range("U" & i).Value
End If
Next i




LastRow = Cells(Rows.Count, "c").End(xlUp).Row
For i = 2 To LastRow
If Range("c" & i).Value = str Then
TotalCount = TotalCount + 1
Else
End If
Next i


If TotalCount > 10 Then
j = 10
Else
j = TotalCount
End If

x = 3


For i = 2 To 3
If Range("s" & i).Value = analyst Then
name = Range("t" & i).Value
Else
End If
Next i


For k = 1 To j
For l = LastRow To 2 Step -1
If Range("c" & l).Value = str Then
Range(name & x).Value = l
x = x + 1
k = k + 1
Else
End If
Next l
Next k


UserForm1.TextBox2.Value = Range("a" & Range(name & 3)).Value
UserForm1.TextBox3.Value = Range("b" & Range(name & 3)).Value
UserForm1.TextBox4.Value = Range("c" & Range(name & 3)).Value
UserForm1.TextBox5.Value = Range("d" & Range(name & 3)).Value




UserForm1.Show
End Sub

 

[sykes]

Hi, and Welcome to Mr. Excel!

Because I don't have the sheet with your data or userform on it, I can't be sure where the problem lies (but somebody else might pop up soon!), however, a couple of suggestions:
1. To look at where it's bugging out, you could insert your code into a module instead, and use F8 to step through it line, by line. You'd need to rename the subroutine, so it should look thus:

Private Sub test()
Dim LastRow As Long
Dim i As Integer
Dim j, k, l, x, TotalCount As Integer
Dim str, analyst As String
Dim name As Variant


analyst = Application.UserName
TotalCount = 0
Sheets("sheet1").Activate
For i = 2 To 4
If Range("s" & i).Value = analyst Then
str = Range("U" & i).Value
End If
Next i




LastRow = Cells(Rows.Count, "c").End(xlUp).Row
For i = 2 To LastRow
If Range("c" & i).Value = str Then
TotalCount = TotalCount + 1
Else
End If
Next i


If TotalCount > 10 Then
j = 10
Else
j = TotalCount
End If

x = 3


For i = 2 To 3
If Range("s" & i).Value = analyst Then
name = Range("t" & i).Value
Else
End If
Next i


For k = 1 To j
For l = LastRow To 2 Step -1
If Range("c" & l).Value = str Then
Range(name & x).Value = l
x = x + 1
k = k + 1
Else
End If
Next l
Next k


UserForm1.TextBox2.Value = Range("a" & Range(name & 3)).Value
UserForm1.TextBox3.Value = Range("b" & Range(name & 3)).Value
UserForm1.TextBox4.Value = Range("c" & Range(name & 3)).Value
UserForm1.TextBox5.Value = Range("d" & Range(name & 3)).Value


UserForm1.Show
End Sub

2. The other thing which may be causing the problem, is that you're using....

Userform1.show

....at the end of your code, but the Userform_initialize event (where you have your code) is automatically run when you show the UserForm with....

Userform1.show

.
Instead, I'd be inclined to call the userform from somewhere else, otherwise, you're initializing the userform, then "Showing" it, which will just re-run the initialization event again (and possibly so-on!)

By the way, if you don't have a module in your VBA project, you can insert a new one by right-clicking the project name in the VBA project pane, and using "insert" / "Module."

 

[gauravjain1604]

Thanks Sykes for your kind response
Let me try your suggestion and will revert with my findings

 

[gauravjain1604]

It's working, thanks a lot Sykes
The problem was re-running the initialization event again and again.
Thanks again

 

[sykes]

Great! Glad it helped you. Thanks for the feedback.
Hope you come back to Mr. Excel, again.