Question: In Microsoft Excel 2003/XP/2000/97, how do I write a macro that will ask the user to enter a search value in an InputBox. It would then look for this string value in an entire column. If the string is found, it will copy that entire row to a new sheet in that same file?
I want to enter the value "Mail Box" in an InputBox. Then I want to search Column E for cells that contain "Mail Box". When the string "Mail Box" is found, the corresponding row should be copied to a new sheet.
Answer: Let's look at an example.
Download Excel spreadsheet (as demonstrated below)
In our spreadsheet, we've created a button on Sheet1 called "Search for String". When the user clicks on this button, a macro called SearchForString will run.
This macro will prompt the user for a search value using an InputBox. It will then search through the values in Column E looking for that search value. In this example, we've entered "Mail Box" as the search value. When the "Mail Box" string is found in Column E, the corresponding row will be copied to Sheet2.
When the macro has completed, the above message box will appear.
If you view Sheet2, you can see that only the rows where Column E contains the string "Mail Box" have been copied.
You can press Alt-F11 to view the VBA code.
The macro code looks like this:
Sub SearchForString() Dim LSearchRow As Integer Dim LCopyToRow As Integer Dim LSearchValue As String On Error GoTo Err_Execute LSearchValue = InputBox("Please enter a value to search for.", "Enter value") 'Start search in row 4 LSearchRow = 4 'Start copying data to row 2 in Sheet2 (row counter variable) LCopyToRow = 2 While Len(Range("A" & CStr(LSearchRow)).Value) > 0 'If value in column E = LSearchValue, copy entire row to Sheet2 If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then 'Select row in Sheet1 to copy Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select Selection.Copy 'Paste row into Sheet2 in next row Sheets("Sheet2").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("Sheet1").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
(scroll to see more)