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

gauravjain1604

New Member
Joined
Dec 9, 2016
Messages
3
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,773
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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....
Code:
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....
Code:
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

New Member
Joined
Dec 9, 2016
Messages
3
It's working, thanks a lot Sykes
The problem was re-running the initialization event again and again.
Thanks again
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,773
Office Version
  1. 365
Platform
  1. Windows
Great! Glad it helped you. Thanks for the feedback.
Hope you come back to Mr. Excel, again.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top