Cannot find x, cut & paste row, repeat and end.

[Cloudfall]

A contributor to this newsgroup kindly created the following example
code for me, which worked with the "MsgBox cell.Address" statement,
that is, it found each instance of "A" in the spreadsheet and displayed
its address. But I can't change the code to get it to cut and paste
rows to another worksheet. This is the original code:

Sub findit()
Dim cell As Range
Dim sFirst As String

Set cell = Cells.Find("A")
If Not cell Is Nothing Then
MsgBox cell.Address
sFirst = cell.Address
Do
Set cell = Cells.FindNext(cell)
If Not cell Is Nothing And cell.Address <> sFirst Then
MsgBox cell.Address
End If
Loop Until cell Is Nothing Or sFirst = cell.Address
End If
End Sub

My implementation requires that I cut and paste each of the "found"
rows from this worksheet into another. Each time I have tried to
implement this, either I get an error at the end after it has cut and
pasted all instances of the "found" rows, or something else goes wrong.
The above code seems to work without "selecting" any cells. As soon as
I select a cell or even use "cell.cut", which seems to select the cell
in order to cut it, the code begins misbehaving.

I can't seem to work out how to simply find something in my worksheet,
cut the row it's in, paste the row to another worksheet, come back to
the original worksheet, repeat the find/ cut/ paste/ return/, and then
elegantly exit when no further instances remain.

Thanking you now for any help.

 

[Norman Jones]

Hi Cloudfall,

But I can't change the code to get it to cut and paste
rows to another worksheet.

Try this adaptation of your code:
'<<===============
Public Sub FindAndCopyit()
Dim Rng As Range
Dim rCell As Range
Dim copyRng As Range
Dim LCell As Range
Dim WB As Workbook
Dim srcSH As Worksheet
Dim destSH As Worksheet
Dim sFirst As String
Dim CalcMode As Long
Const strSearch As String = "Nirvana" '<<===== CHANGE

Set WB = ActiveWorkbook '<<===== CHANGE
Set srcSH = WB.Sheets("Sheet1") '<<===== CHANGE
Set destSH = WB.Sheets("Sheet5") '<<===== CHANGE
Set Rng = srcSH.Range("A1:G100") '<<===== CHANGE

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Set rCell = Rng.Find(strSearch)
If Not rCell Is Nothing Then
Set copyRng = rCell.EntireRow
sFirst = rCell.Address
Do
Set rCell = Rng.FindNext(rCell)
If Not rCell Is Nothing And _
rCell.Address <> sFirst Then
Set copyRng = Union(rCell.EntireRow, copyRng)
End If
Loop Until rCell Is Nothing Or sFirst = rCell.Address
End If

Set LCell = destSH.Cells(Rows.Count, "A").End(xlUp)(2)

If Not copyRng Is Nothing Then
With copyRng
.Copy Destination:=LCell
.Delete
End With
End If

With Application
.CutCopyMode = False
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<===============

Depending on your data configuration, you may be able to use the Advanced
Filter feature. If so, then turning on the macro recorder while performing
the operations manually, should provide you with base code. This base code
can then be edited to enable generic application.

 

[Cloudfall]

Sorry for taking so long to reply to your post. I finally found out
what my problem was. Embarassingly, I have obviously had this problem
before because the textbook I use has only two yellow post-it notes in
it, and one of them was on the page with the solution on it. The
solution had pencilled arrows and asterisks pointing to it. I am
posting this as a response to your efforts and so that some other
beginner doesn't have to go through the frustrations that I did over
the past few weeks.

The reference work I am quoting from is "Using Excel Visual Basic for
Applications" 2nd Ed. by Jeff Webb from Que. On page 277 it says:

Finding and Replacing Text in Cells.
The Find and Replace methods act on the text in the cells of a single
sheet. If you are accustomed to finding and replacing text using word
processing software, Excel's behavior might seem confusing. Here are
some points to remember when using these methods:
1. Find and Replace are limited to a single sheet at a time.
2. Find never reaches the 'end' of a range, it simply restarts its
search at the top or bottom of the range. See the 'Finding All
Occurrences of Text' section for an example of how to work around this.
3. If the text is not found, Find returns Nothing, which causes an
error if you try to activate the returned value. You must always test
the result of Find before doing anything with the result.
4. Replace replaces all instances in a range; you can't selectively
search and replace using Replace.

Point 3 above was relevant for me. The author gives the following code
for testing the result of Find before doing anything with the result:

If TypeName(ws.Cells.Find(SearchItem)) = "Range" Then
:
End If

This is what worked for me. Not using this test caused my confusion and
made me to ask all the stupid questions in this forum. I hope this
helps someone else save days of frustration. I myself have received so
much help from this group that I feel I must give something back.