Help with a loop please.

R

ryguy7272

For some reason, all changes occur in one single sheet. I used F8 to move
through the code and still didn't see why it's not moving from one sheet to
another sheet. Can someone point out my error?

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh

Thanks,
Ryan---
 
J

JLGWhiz

For Each sh In ThisWorkbook.Sheets

sh.Cells.Select

sh.Columns("D:D").Select

you need to qualify all of the range objects with the sh. prefix so VBA
knows to go to other that the active sheet.
 
B

Bernard Liengme

Here is my test sub

Sub tryme()
For Each ws In Worksheets
If ws.Name Like "temp*" Then
ws.Range("A1") = "X"
End If
Next ws
End Sub

If I used Range("A1")="X" without referencing the ws varaible, the sub put X
in A1 of the active worksheet and no others. This. of course, is exacly
whyat the code would mean.

You need something like

Dim sh As Worksheet
For Each sh In Sheets
If sh.Name Like "RVP - Q1*" Then
with sh
.Cells.Select
.Selection.Copy
,,,,
next with
next sh


best wsihes
 
R

ryguy7272

Thanks for the info. JLGWhiz. That makes sense, but it still doesn't work.
Now it errors on this line:
sh.Columns("D:D").Select

Any thoughts?


Thanks,
Ryan---
 
R

ryguy7272

Run-time error '1004':
Select method of Range class failed

....guess that would be helpful is debugging this thing...
 
R

ryguy7272

I made a little progress. I eliminated the error, but all changes are made
to one single sheet, so I literally get 'Next Quarter' 10 times, in a cell in
one sheet and 'Current Quarter' 10 times in the same cell in the same sheet!!

Application.DisplayAlerts = False
Dim sh As Worksheet

For Each sh In ThisWorkbook.Sheets
If sh.Name Like "RVP - Q1*" Then
'If InStr(1, sh.Name, "*Q1*") Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ActiveSheet.Select
Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
End If
Next sh

For Each sh In ThisWorkbook.Sheets
If sh.Name Like "RVP - Q4*" Then
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ActiveSheet.Select
Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Current Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Current
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Current
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Current
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Current Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh


ActiveWorkbook.Save
ActiveWorkbook.Close

Application.DisplayAlerts = True

There is something wrong with the logic of the loop because it doesn't
evaluate the names of the sheets, and it doesn't move to the next sheet after
it does the find/replace on the first sheet that it lands on. Can someone
please point out my error?

Thanks so much,
Ryan---
 
J

JLGWhiz

Range("D:D")
Columns("D")
Columns(4)

ryguy7272 said:
Run-time error '1004':
Select method of Range class failed

...guess that would be helpful is debugging this thing...
Click to expand...
 
R

ryguy7272

Thanks. Unfortunately, it doesn't move from one sheet to another sheet in
ThisWorkbook.Sheets

What can I do?

Thanks,
Ryan---
 
J

JLGWhiz

Copy this and paste it in the module. Fix any line wraps (they should be red
in the code module if they wrapped). Comment out your old code. Then try it.

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

With Sh.Columns(4)
.Replace What:="Total Display", _
Replacement:="Total Display - Next Quarter", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="Class 1", Replacement:="Class 1 - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:= False, ReplaceFormat:=False
.Replace What:="Class 2", Replacement:="Class 2 - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
.Replace What:="Search", Replacement:="Search - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End With

End If
Next sh
 
J

JLGWhiz

In the locals window it shows that it does change sheets. You won't see the
sheets change because the code does not use select. Check to see if the
changes were made, if noit then make sure your criteria is correct in the
If...Then statement for the worksheet name including the spaces between RVP
the dash and the Q1.

You have another built in problem, Ryan. The words you are replacing are
repeatded in the different statements, and the code is doing exactly what it
is instructed to do. When it sees Total Display, it replaces it with Total
Display - Next Quarter, then later when it sees Total, it replacest that with
Total - Next Quarter so that the first replacement ends up looking like
"Total - Next Quarter Display - Next Quarter" because it replaced the Total
that had already been changed. You need LookAt:=xlWhole.

I ran this code with the changes and it worked ok for me with three
worksheets. So if you still have a problem, it has to be the sheet name not
registering because of structural differences.

Sub dk()
Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With sh.Columns(4)
.Replace What:="Total Display", _
Replacement:="Total Display - Next Quarter", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="Class 1", Replacement:="Class 1 - Next Quarter", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
.Replace What:="Class 2", Replacement:="Class 2 - Next Quarter", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
.Replace What:="Search", Replacement:="Search - Next Quarter", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End With

End If
Next sh

End Sub
 
R

ryguy7272

This did exactly what I wanted it to do. I am so happy now. Yes, you are
right JLGWhiz, the Total and Total Display was screwing me up. I didn't even
realize it before, because I was focusing on the looping issue. I've never
used the 'LookAt:=xlWhole' object before. Thanks for pointing that out!
Thanks for everything!! I can move on with my project now!!

Kind Regards,
Ryan--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find and replace multiple values in one statement 3
Macro to select columns and replace data 0
Find and Replace Help 3
Need help with a "If" "Else" VB code 4
Need help with correct syntax please 2
Can I improve processing speed of macro 4
Limits of Substitute and Replace functions 4
Printing Macro 4

Top