Looping a loop?
 

I realize the following maybe a bit repatitve, but my main concern is that I
run the code and it works for finding "SA", but not "NI" or "DN". Any help
is appreciated.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("t0983101").Select
Application.CutCopyMode = False
Range("A4").Select
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)

Set rngFound = rngToSearch.Find(what:="SA", LookIn:=xlValues,
lookat:=xlWhole)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("SA").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Sheets("t0983101").Select
Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)
Set rngFound = rngToSearch.Find(what:="ni", LookIn:=xlValues)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("ni").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)
Set rngFound = rngToSearch.Find(what:="DN", LookIn:=xlValues)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("DN").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
end sub

Looping a loop?
 

John wrote:
I realize the following maybe a bit repatitve, but my main concern is
that I run the code and it works for finding "SA", but not "NI" or
"DN". Any help is appreciated.


John: When you 'Find' SA, you're looking at (LookAt) xlWhole.

Set rngFound = rngToSearch.Find(what:="SA", LookIn:=xlValues,
lookat:=xlWhole)

That argument persists between Finds unless you specifically change it.
When you search for NI, you don't specify the LookAt argument, so it remains
xlWhole. Based on what you've presented here, I would think you want that
to happen, but I don't know for sure. If NI and DN are not the whole cell,
then specify LookAt in the subsequent Finds as xlPart. I didn't see
anything else that would prevent those from being found.

Also, consider re-writing your sub as:

Sub NoTest()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Dim vaWhats As Variant
Dim i As Long

Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)
vaWhats = Array("SA", "ni", "DS")

For i = LBound(vaWhats) To UBound(vaWhats)
Set rngFound = rngToSearch.Find(what:=vaWhats(i), _
LookIn:=xlValues, lookat:=xlWhole)

If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
Exit For
Else
Do
rngFound.Copy _
Sheets(vaWhats(i)).Range("a9").End(xlDown).Offset( 1, 0)
rngFound.ClearContents
Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Next i

End Sub


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Looping a loop?
 

You have another similar, but different, reply at your other post.

John wrote:

I realize the following maybe a bit repatitve, but my main concern is that I
run the code and it works for finding "SA", but not "NI" or "DN". Any help
is appreciated.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("t0983101").Select
Application.CutCopyMode = False
Range("A4").Select
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)

Set rngFound = rngToSearch.Find(what:="SA", LookIn:=xlValues,
lookat:=xlWhole)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("SA").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Sheets("t0983101").Select
Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)
Set rngFound = rngToSearch.Find(what:="ni", LookIn:=xlValues)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("ni").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)
Set rngFound = rngToSearch.Find(what:="DN", LookIn:=xlValues)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("DN").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
end sub

--

Dave Peterson

Looping a loop?
 

Thanks all... thats works!

"Dave Peterson" wrote:

You have another similar, but different, reply at your other post.

John wrote:

I realize the following maybe a bit repatitve, but my main concern is that I
run the code and it works for finding "SA", but not "NI" or "DN". Any help
is appreciated.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("t0983101").Select
Application.CutCopyMode = False
Range("A4").Select
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)

Set rngFound = rngToSearch.Find(what:="SA", LookIn:=xlValues,
lookat:=xlWhole)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("SA").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Sheets("t0983101").Select
Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)
Set rngFound = rngToSearch.Find(what:="ni", LookIn:=xlValues)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("ni").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)
Set rngFound = rngToSearch.Find(what:="DN", LookIn:=xlValues)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("DN").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
end sub

--

Dave Peterson