Loop Only Works on Active Sheet

[ramohse]

Hello all,


I am trying to write a simple loop that will replace values in a range on a specific number of sheets, however the loop only works on the active sheet (i.e. I have Excel open to a sheet, I hit F5 in the editor, and it only works on that sheet). I have tried several methods of naming the sheets I want (index, Select Case) and this is the current method I am trying, however the problem persists in all scenarios.



Sub LoopTest()
Dim ws As Worksheet
For Each ws In Application.Worksheets

If ws.Name Like "*5yr*" Then
[C11:N37].Replace What:="X", Replacement:="Y"
Else:
If ws.Name Like "*8Qtr*" Then [C11:N37].Replace What:="X", Replacement:="Y"
End If

Next ws
End Sub



I am not sure what is breaking the loop. I have searched the internet and do not see how the syntax here is different than a normal For Each Loop. I am new at this, so am probably missing something blatantly obvious.

I have about 45 5yr and 8Qtr tabs I need to change. I have also tried doing this individually (i.e. separate loop for each tab type), also to no avail.

Thank you very much for your time and assistance.

[SamT]

Top o' my head code:

Sub SamT()
Dim Cel As Range
Dim strReplaceWhat As String
Dim strWithWhat As String
Dim Ws Aa Worksheet

For Each ws in Worksheets
Select Case LCase(Right(Ws.Name, 3)
Case "5yr"
strReplaceWhat = "" 'Modify as needed for Year sheets
strWithWhat = ""
Case "8qt"
strReplaceWhat = "" 'Modify as needed for Qtr Sheets
strWithWhat = ""
Case Else
GoTo WSNEXT
End Select
For Each Cel in Ws.Range("C11:N37") Replace strReplaceWhat, strWithWhat
Next Cel
WSNEXT:
Next Ws
End Sub

[ramohse]

Thanks a ton!


Though it tells me it expects an "End of Statement" at the point of "("C11:N37") Replace" I've tried adding a period, Application.Replace, putting it on the line below, but none of that seemed to work...

[SamT]

If you set the cursor in the word "Replace" in your code, and press F1, the VB Editor (VBE) will open the Help file to the Replace Page and you will see the error in the Replace Function.

Hint: You need a "Cel.[Value or Text or Formula]" depending on what you are replacing, and some brackets and commas.

[ramohse]

Got it! And it worked perfectly. Thank you very much!!!