I am facing a problem in program that:
I am having a userform to search / find a text in sheet2 and paste all related data from different columns of sheet2 to sheet5, all works well, but I need to add a code line which counts the searched criteria text of textbox1 of my userform and paste the result as like " 15 records found " bellow the last used row of worksheet. (for example last row of col " E ").
Lets me more define it: I am finding the month " April " via textbox1 of userform pressing command button1 to find it in sheet2, then it finds "April" 20 times in col "G" of sheet2 and it copies data from the same rows of "April" and paste it in last empty rows of sheet5 from col "C" to col "M" here I need to add a "text line" through vba code at the end of last used row in any column which say about the total count of found records. After this last "text line" in last used row I shall again search any thing else in sheet2 then it copies the records and again paste it after the "text line" and again it add a "text line" showing the count of new searched text or values pasted in sheet5, and so on.
I am using the following code:
I am having a userform to search / find a text in sheet2 and paste all related data from different columns of sheet2 to sheet5, all works well, but I need to add a code line which counts the searched criteria text of textbox1 of my userform and paste the result as like " 15 records found " bellow the last used row of worksheet. (for example last row of col " E ").
Lets me more define it: I am finding the month " April " via textbox1 of userform pressing command button1 to find it in sheet2, then it finds "April" 20 times in col "G" of sheet2 and it copies data from the same rows of "April" and paste it in last empty rows of sheet5 from col "C" to col "M" here I need to add a "text line" through vba code at the end of last used row in any column which say about the total count of found records. After this last "text line" in last used row I shall again search any thing else in sheet2 then it copies the records and again paste it after the "text line" and again it add a "text line" showing the count of new searched text or values pasted in sheet5, and so on.
I am using the following code:
Code:
Sub copycolaYn()
Application.ScreenUpdating = False
Dim response As String, sAddr As String
Dim foundResponse As Range
Dim erow As Long
Worksheets("srYn").Unprotect "Hakeem"
On Error Resume Next
response = Me.TextBox1 'InputBox("Please enter the name of the disease.")
If response = "" Then
MsgBox ("You have not entered a name.")
Exit Sub
End If
Set foundResponse = Sheets("PtR").Range("H:H").Find(response, LookIn:=xlValues, LookAt:=xlPart)
If Not foundResponse Is Nothing Then
sAddr = foundResponse.Address
Do
Sheets("PtR").Range("I" & foundResponse.Row).Copy _
Sheets("srYn").Cells(Sheets("srYn").Rows.Count, "E").End(xlUp).Offset(1, 0)
Sheets("PtR").Range("E" & foundResponse.Row).Copy _
Sheets("srYn").Cells(Sheets("srYn").Rows.Count, "D").End(xlUp).Offset(1, 0)
Sheets("PtR").Range("D" & foundResponse.Row).Copy _
Sheets("srYn").Cells(Sheets("srYn").Rows.Count, "I").End(xlUp).Offset(1, 0)
Sheets("PtR").Range("C" & foundResponse.Row).Copy _
Sheets("srYn").Cells(Sheets("srYn").Rows.Count, "J").End(xlUp).Offset(1, 0)
Sheets("PtR").Range("B" & foundResponse.Row).Copy _
Sheets("srYn").Cells(Sheets("srYn").Rows.Count, "K").End(xlUp).Offset(1, 0)
Sheets("PtR").Range("F" & foundResponse.Row).Copy _
Sheets("srYn").Cells(Sheets("srYn").Rows.Count, "L").End(xlUp).Offset(1, 0)
Sheets("PtR").Range("A" & foundResponse.Row).Copy _
Sheets("srYn").Cells(Sheets("srYn").Rows.Count, "H").End(xlUp).Offset(1, 0)
Sheets("PtR").Range("J" & foundResponse.Row).Copy _
Sheets("srYn").Cells(Sheets("srYn").Rows.Count, "F").End(xlUp).Offset(1, 0)
Sheets("PtR").Range("L" & foundResponse.Row).Copy _
Sheets("srYn").Cells(Sheets("srYn").Rows.Count, "G").End(xlUp).Offset(1, 0)
erow = Worksheets("srYn").Cells(Rows.Count, 13).End(xlUp).Offset(1, 0).Row
Worksheets("srYn").Cells(erow, 13) = Me.TextBox1.Value
Set foundResponse = Sheets("PtR").Range("H:H").FindNext(foundResponse)
Loop While foundResponse.Address <> sAddr
sAddr = ""
PUFsrctpYnok.Label3.Caption = Me.TextBox1.Value
Me.Hide
PUFsrctpYnok.Show
Else
Me.Hide
PUFsrctpYnA.Label4.Caption = Me.TextBox1.Value
PUFsrctpYnA.Show
End If