Why wont my search work right?

[Rick5070]

Hello,

I have been working on this database for a while but it has actually grown and now it is not acting like it should.
The search function that I have been using will no longer search the entire database for some reason and I cant seem to figure it out.
Any help would be greatly appreciated.
Here is what I have been using:

Private Sub CommandButton1_Click() 'Searching Inventory Code

Dim FindString  As String
Dim Rng As Range
Dim MyFind      As Long

FindString = InputBox("Enter Part Number")

    If FindString = vbNullString Then
Exit Sub
    End If

    If Trim(FindString) <> "" Then
With Sheets("Ergo II Spares").Range("A2:J" & Range("J" & Rows.Count).End(xlUp).Row)
Set Rng = .Find(What:=FindString, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not Rng Is Nothing Then
MyFind = Rng.Row
Do
Set Rng = .FindNext(Rng)

Select Case MsgBox("Found here:" & vbCrLf & Chr(9) & _
"Area " & Chr(9) & ": " & Range("A" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Part " & Chr(9) & ": " & Range("B" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Mikron " & Chr(9) & ": " & Range("C" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Item " & Chr(9) & ": " & Range("D" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Cabinet" & Chr(9) & ": " & Range("E" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Drawer " & Chr(9) & ": " & Range("F" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Manufacturer " & Chr(9) & ": " & Range("G" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Vendor " & Chr(9) & ": " & Range("J" & Rng.Row).Value & vbCrLf & Chr(9) & _
"QTY " & Chr(9) & ": " & Range("H" & Rng.Row).Value & vbCrLf & vbCrLf & _
"Yes = Next" & Chr(9) & "No = Stop", vbInformation + vbYesNo, "Search string: " & FindString)
Case Is <> vbYes: GoTo exitLoop

End Select
Loop While Not Rng Is Nothing And Rng.Row <> MyFind

exitLoop:
Else
MsgBox "Nothing found"
End If
End With
End If
End Sub

 

[Peter_SSs]

I haven't studied the whole code in detail but here is one likely culprit. Try adding the blue text

With Sheets("Ergo II Spares").Range("A2:J" & Sheets("Ergo II Spares").Range("J" & Rows.Count).End(xlUp).Row)