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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,884
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,884
Office Version
  1. 365
Platform
  1. Windows
Great! Glad it helped you. Thanks for the feedback.
Hope you come back to Mr. Excel, again.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,426
Messages
5,837,162
Members
430,480
Latest member
iangessey

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