Excel pop out application inputbox ( search function )

Question

i am trying to create a pop out box to search for a certain value on the 2nd column, as i am still new to vba coding, i could only do this much through googling.

What i'm trying to do was (all these were to be done in macro), to find a certain value, and then when the program found the certain value, it will copy the entire row where the value was and paste it into a new sheet in excel ( with the same header )

this is the code that i have so far.

Sub macrotest()
x = 2
Do While Cells(x, 1) <> ""
If Cells(x, 2) = "TEST" Then
Worksheets("Sheet1").Rows(x).Copy
Worksheets("Sheet2").Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
End If
Worksheets("Sheet1").Activate
x = x + 1
Loop

End Sub

enter image description here

as you can see from above, i'm trying to copy the circled row into a new sheet.

Why not use autofilter? See [THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) — Siddharth Rout, Oct 17 '13 at 06:26
As Siddharth said, This could easily be achieved through AutoFilter. No need to go for VBA. — dotNET, Oct 17 '13 at 06:30
@SiddharthRout i have seen their program, but it doesn't still answer to my question, what they have done in their codes is that, they have set the variable to the value/string they wanted to search in VBA, but i wanted to do it on excel, like an input box for me to search whatever i want , not by editing the codes to search what i wanted. Thanks.. — user2837847, Oct 17 '13 at 06:41
I know that :) Your input box can pass that text to the code which is mentioned in that link :) — Siddharth Rout, Oct 17 '13 at 06:42
@SiddharthRout My apologies, as i am still new to vba, may i know which line is the one that pass the text to the code? thanks. — user2837847, Oct 17 '13 at 06:47

score 0 · Accepted Answer · edited May 23 '17 at 12:21

Like I mentioned in the comments above, you can use Autofilter to do it. Unlike in the code which is mentioned HERE, you can use the InputBox to get the search text

In that link strSearch = "Clarke, Matthew" is hard coded. Now you can use the below code to get your own strSearch

Sub Sample()
    Dim strSearch

    strSearch = Application.InputBox("Please enter the search string")

    If strSearch <> False And Len(Trim(strSearch)) <> 0 Then
        Debug.Print strSearch

        '
        '~~> Rest of the code
        '
    End If
End Sub

Excel pop out application inputbox ( search function )

1 Answers1