Private Sub cmdFIND_Click()
Sheets("MAINARES2").UsedRange.ClearContents --->clears the destination sheet
Dim lastrow, lastrow2 As Integer, X As String, c As Range, rw As Long, firstAddress As Variant, rowno As Variant, RownoA As Variant
X = MAINWINDOW2.TextBox11.Value
With Worksheets("Sheet2").Range("E1:E31103") ---> col. to search
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("MAINARES2").Range("B" & rw) --->copy operation
rw = rw + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
lastrow = Sheets("MAINARES2").Range("B" & rows.count).End(xlUp).Row
If lastrow = 1 Then
Range(Cells(c.Row + 7, 2), Cells(c.Row, 7)).Copy Destination:=Sheets("MAINARES2").Range("B" & rw)
Else
End If
Else
MsgBox "value not found"
End If
End With
rowno = Sheets("MAINARES2").Range("B2").End(xlDown).Row
Sheets("MAINARES2").Range("H1").Value = rowno 'total rows found in search
Sheets("MAINARES2").Range("I1").Value = X 'value to find, i.e.,, "last days"
MAINWINDOW2.ListBox1.ListIndex = 0
MAINWINDOW2.TextBox4.Value = Sheets("MAINARES2").Range("H1").Value
Dim firstrow As Integer, myrange As Range
Set myrange = Sheets("MAINARES2").Range("B1")
If myrange <> " " Then
firstrow = myrange.Row
Else
MsgBox "First row is blank"
End If
MAINWINDOW2.TextBox9.Value = Sheets("MAINARES2").Range("I1").Value