StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First things first: </p> <ul> <li>Stop using .Select and .Activate when they're not needed, they're the devil's methods. Deal with range/worksheet objects directly. </li> <li>Change your row counters from intergers to longs just in case.</li> <li>Explicitly declaring which worksheet you're working with can save yourself from odd bugs/errors. If you don't like the typing use a worksheet object.</li> <li>Your error handler should always output err.Number and err.Description. If you'd done that from the beginning you probably wouldn't have had to post this question.</li> <li>Range.Copy has a destination argument. Use it instead of Range.Paste to save some potential headaches.</li> </ul> <p>Here's some simplified code, see if it works: </p> <pre><code>Sub SearchForString() Dim LSearchRow As Long Dim LCopyToRow As Long Dim wksInput As Worksheet Dim wksOutput As Worksheet On Error GoTo Err_Execute 'Create a new sheet output to and store a reference to it 'in the wksOutput variable Set wksOutput = Worksheets.Add(AFter:=Worksheets(Worksheets.Count)) wksOutput.Name = "MySheet" 'The wksInput variable will hold a reference to the worksheet 'that needs to be searched Set wksInput = ThisWorkbook.Worksheets("Sheet2") 'Start copying data to row 2 in Sheet2 (row counter variable) LCopyToRow = 2 'Loop through all the rows that contain data in the worksheet 'Start search in row 4 For LSearchRow = 4 To wksInput.UsedRange.Rows.Count 'If value in column E = "Mail Box", copy entire row to wksOutput If wksInput.Cells(LSearchRow, 5) = "Mail Box" Then 'One line copy/paste wksInput.Rows(LSearchRow).Copy wksOutput.Cells(LCopyToRow, 1) 'Increment the output row LCopyToRow = LCopyToRow + 1 End If Next LSearchRow With wksInput .Activate .Range("A3").Select End With MsgBox "All matching data has been copied." Exit Sub Err_Execute: MsgBox "An error occurred. Number: " & Err.Number & " Description: " & Err.Description End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload