ramohse
09-19-2013, 10:46 AM
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.
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.