Private Sub cmdFIND_Click()
Sheets("NEWRESULT").UsedRange.ClearContents ' NEWRESULT is the sheet that Find copies the results to
Dim lastrow As Integer
Dim X As String
Dim c As Range
Dim rw As Long
Dim firstAddress As Variant
Dim rowno As Variant
X = Me.TextBox6.Value 'Textbox6 takes any word or string value, i.e., "east", or "the end of all flesh", or "Matthew 24:15" (w/out quotes)
TextBox7.Value = TextBox6.Value
With Worksheets("Sheet2").Range("E1:E31103") 'searches down Sheet2 col E specified by With of C,D, E and F cols.
Set c = .Find(X, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
rw = 1
firstAddress = c.Address
Do
Worksheets("Sheet2").Select
c.Select
Range(Cells(c.row, 2), Cells(c.row, 7)).Copy Destination:=Sheets("NEWRESULT").Range("B" & rw) ' NEWRESULT sheet that Find copies the results to
rw = rw + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Else
MsgBox "No value found"
End If
End With
rowno = Sheets("NEWRESULT").Range("B2").End(xlDown).row
lastrow = Sheets("NEWRESULT").Range("B" & Rows.count).End(xlUp).row
TextBox8.Value = lastrow & " " & "verses found for " & """" & TextBox6.Value & """"
'TextBox3.Value = LastRow & " " & "verses found for " & TextBox1.Value
Sheets("NEWRESULT").Range("H1").Value = rowno
Sheets("NEWRESULT").Range("I1").Value = X
ListBox1.ListIndex = 0
'Sheets("Sheet3").Select
End Sub
