Seach and Copy Macro

**mwehby18** · 12-04-2008, 10:24 AM

Hi everyone,
Its been a long time since I have had to do this so I am a lot rusty on Creating Macro's.

I have 2 that I am interested in creating, the first:
I would like to, from a command button, click it, and a text input box appears, input the text you would like to search for in Column B (from sheet 1), if found copy the whole row and paste it into a different worksheet.

Here is the Macro I have tried to play with to get this, it obviously isn't right but it has some of the characteristics I want...
It has the search and copy criteria good (it searches in Column B if the correct work sheet and it pastes the whole row, if that word is found, into a different worksheet....)
What it doesnt do, and I'm hoping you all can help with is..
1) It doesnt incorporate a textbox and use the value of the inputted text in the text box to search.
2) It only searches the value (in the case that I am pasting now "empty"). I would like it to search if it has that string of characters in the cell at all....like "empty box", not just if it says "empty" only.. I am not sure I am saying this correct...it should copy and choose that row to copy, if the word (or string of characters) I input appears anywhere in that cell (in the whole of column B), not just the exact and only word in there.
3) If possible, that it take you to that work sheet automatically after you got it to search for you

Sub SearchForString()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer

    On Error GoTo Err_Execute

    'Start search in row 4
    LSearchRow = 4

    'Start copying data to row 2 in Sheet8 (row counter variable)
    LCopyToRow = 2

    While Len(Range("A" & CStr(LSearchRow)).Value) > 0

        'If value in column E = "Mail Box", copy entire row to Sheet2
        If Range("B" & CStr(LSearchRow)).Value = "Empty" Then

            'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy

            'Paste row into Sheet2 in next row
            Sheets("Find").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste

            'Move counter to next row
            LCopyToRow = LCopyToRow + 1

            'Go back to Sheet1 to continue searching
            Sheets("Inventory").Select

        End If

        LSearchRow = LSearchRow + 1

    Wend

    'Position on cell A3
    Application.CutCopyMode = False
    Range("A3").Select

    MsgBox "All matching data has been copied."

    Exit Sub

Err_Execute:
    MsgBox "An error occurred."

End Sub

The other macro I would like to create, is I macro, based on a command button, that deletes the information that was just populated by the original macro, and takes you back to the original sheet.

Thank you guys for taking a look at this and hopefully helping me out, its much appreciated

**mwehby18** · 12-04-2008, 04:42 PM

Any Thoughts?
(not sure how long a responce usually takes, or perhaps I didn't word my dilemma properly...if thats the case please ;et me know)

**mwehby18** · 12-05-2008, 11:46 AM

OK so i got it to incorporate an Input box now, and the value that was inputted....Except, it still is based on if the Value of the cell is exactly what I inputted...I need it to choose if the cell contains that string of charcters I inputted in the input Box anywhere within that cell.....Here's what I got...

Sub SearchForString()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    Dim Ghetto As String
    On Error GoTo Err_Execute
    Ghetto = InputBox("Search for")
    'Start search in row 4
    LSearchRow = 4

    'Start copying data to row 2 in Sheet8 (row counter variable)
    LCopyToRow = 2

    While Len(Range("A" & CStr(LSearchRow)).Value) > 0

        'If value in column B = Ghetto copy entire row to Sheet2
        If Range("B" & CStr(LSearchRow)).Value = Ghetto Then

            'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy

The discoloured line is where I believe my problem to be,
I would really appreciate any Help here