Hi all,
I'm getting error 91 object variable or block variable not set on the red color text line where the loop starts until nothing.
it is rngFind.Address that is stating it is nothing, why is it not just going to the next step?
Thank you for any help.
HP Fruity.
I'm getting error 91 object variable or block variable not set on the red color text line where the loop starts until nothing.
it is rngFind.Address that is stating it is nothing, why is it not just going to the next step?
Thank you for any help.
HP Fruity.
VBA Code:
Sub Lookup()
'declare the variables
Dim rngFind As Range
Dim strFirstFind As String
'On Error GoTo ErrHandler
Application.Cursor = xlDefault
Application.ScreenUpdating = False
'clear the listbox
lstLookup.Clear
'look up parts or all of full name
With Sheet2.Range("mytable[F_N]")
Set rngFind = .Find(txtLookup.Text, LookIn:=xlValues, LookAt:=xlPart)
'if value found then set a variable for the address
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
'add the values to the listbox
Do
If rngFind.Row > 1 Then
lstLookup.AddItem rngFind.Text
lstLookup.List(lstLookup.ListCount - 1, 1) = rngFind.Offset(0, -4)
lstLookup.List(lstLookup.ListCount - 1, 2) = rngFind.Offset(0, 3)
lstLookup.List(lstLookup.ListCount - 1, 3) = rngFind.Offset(0, 4)
lstLookup.List(lstLookup.ListCount - 1, 4) = rngFind.Offset(0, 5)
lstLookup.List(lstLookup.ListCount - 1, 5) = rngFind.Offset(0, 7)
lstLookup.List(lstLookup.ListCount - 1, 6) = rngFind.Offset(0, 8)
lstLookup.List(lstLookup.ListCount - 1, 7) = rngFind.Offset(0, 9)
lstLookup.List(lstLookup.ListCount - 1, 8) = rngFind.Offset(0, 106)
End If
'find the next address to add
Set rngFind = .FindNext(rngFind)
[COLOR=rgb(184, 49, 47)] Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind[/COLOR]
End If
End With
Application.ScreenUpdating = True
On Error GoTo 0
Exit Sub
ErrHandler:
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"The administrator has been notified and will look into this error"
Set objLook = CreateObject("Outlook.Application")
Set objEmail = objLook.CreateItem(0)
With objEmail
.To = "XXX"
.Subject = "Error Lookup"
.Body = "frmReg Lookup Error From " & Application.UserName & " " & Err.Number & vbCrLf & Err.Description
.send
End With
Set objLook = Nothing
Set objEmail = Nothing
Application.ScreenUpdating = True
End Sub