Find Function Error 91

[nirmalachr]

Hi,

I am using the following code to find values in particular column, when the entered value is available in the particular column it is showing the particular rows in the list box. Now the problem is that if the value is not available it returning to the error 91.

Private Sub CommandButton1_Click()
ListBox1.Clear
Dim cell As Range
Dim saddr As String
Dim sh As Worksheet
Set sh = ActiveWorkbook.Sheets("ENTRY")
Set cell = sh.Range("G2:G10000").Find(What:=TextBox1.Text, after:=Range("G10000"), LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
If Not cell Is Nothing Then saddr = cell.Address
Do
With ListBox1
.AddItem cell.Offset(0, -3).Value & " " & cell.Offset(0, -2).Value & " " & cell.Offset(0, -1).Value & " " & cell.Offset(0, 0).Value & " " & cell.Offset(0, 1).Value & " " & cell.Offset(0, 2).Value & " " & cell.Offset(0, 4).Value & " " & cell.Offset(0, 5).Value
'.List(ListIndex, 2) = cell.Offset(0, 4).Value
'.List(ListIndex, 3) = cell.Offset(0, 4).Address
End With
Set cell = sh.Range("G2:G10000").FindNext(cell)
Loop While cell.Address <> saddr
MsgBox ("Thank you")
End Sub

Kindly help me out from this issue

 

[skywriter]

Maybe this.

Private Sub CommandButton1_Click()
ListBox1.Clear
Dim cell As Range
Dim saddr As String
Dim sh As Worksheet
Set sh = ActiveWorkbook.Sheets("ENTRY")
Set cell = sh.Range("G2:G10000").Find(What:=TextBox1.Text, after:=Range("G10000"), LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
If Not cell Is Nothing Then
saddr = cell.Address
Do
With ListBox1
.AddItem cell.Offset(0, -3).Value & " " & cell.Offset(0, -2).Value & " " & cell.Offset(0, -1).Value & " " & cell.Offset(0, 0).Value & " " & cell.Offset(0, 1).Value & " " & cell.Offset(0, 2).Value & " " & cell.Offset(0, 4).Value & " " & cell.Offset(0, 5).Value
'.List(ListIndex, 2) = cell.Offset(0, 4).Value
'.List(ListIndex, 3) = cell.Offset(0, 4).Address
End With
Set cell = sh.Range("G2:G10000").FindNext(cell)
Loop While cell.Address <> saddr
MsgBox ("Thank you")
End If
End Sub

 

[nirmalachr]

Thank you very much Skywriter. it helped me a lot

 

[skywriter]

My pleasure, thanks for the feedback.