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
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