Whats wrong with my loop? Only a beginner!!!

[rob_sheeds]

Hi, my loop is red and wont work. Can you suggest a change?
Thanks in advance!!!!!

Public Sub Hide_Sheets_If_Zero_Closing_Bal()
Worksheets("Summary").Activate
Range("E2").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value = "0" Then
Worksheets(ActiveCell.Offset(0, -4)).Visible = False
Else
Worksheets(ActiveCell.Offset(0, -4)).Visible = True
End If
Range(ActiveCell.Offset(1, 4)).Select
Loop Until (ActiveCell.Value = "")
End Sub

 

[AlphaFrog]

With a Do-Loop block, you can have the exit condition (While ActiveCell.Value <> "") on the Do line or on the Loop line but not both. You have a While condition on the Do line and then an Until condition on the Loop line. Remove one of those two conditions.

This...
Do While ActiveCell.Value <> ""
'code
Loop

Or this...
Do
'code
Loop Until ActiveCell.Value = ""

 

[Cindy Ellis]

I think there are other problems with the code, though. It kind of depends on the layout of the data and the contents of the cells. Specifically, the select just before the loop would march through the file in a 1-row-down, 4-columns-over pattern (E2, then I3, then M4, etc.). It seems to me to be an unusual way to lay out the data, although anything is possible.

 

[rob_sheeds]

I have changed the code....the offsets seemed to be worng and a line wasnt needed.

Public Sub Hide_Sheets_If_Zero_Closing_Bal()
Worksheets("Summary").Activate
Range("E2").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value = "0" Then
Worksheets(ActiveCell.Offset(0, -4)).Visible = False
Range(ActiveCell.Offset(1, 0)).Select
Loop
End Sub

THe loop is still giving error "Loop without Do". Im baffled?

 

[Cindy Ellis]

You need to end your "If" with an "End If"
Hope that helps,
Cindy

 

[AlphaFrog]

You're now missing an End If

Give ths a try if I understand correctly what you are trying to do.

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Hide_Sheets_If_Zero_Closing_Bal()<br>    <SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Worksheets("Summary")<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> .Range("E2", .Range("E2").End(xlDown))<br>            Worksheets(cell.Offset(0, -4).Value).Visible = cell.Value <> 0<br>        <SPAN style="color:#00007F">Next</SPAN> cell<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Cindy Ellis]

I like it! (Hope the OP does, too)

 

[rob_sheeds]

With a Do-Loop block, you can have the exit condition (While ActiveCell.Value <> "") on the Do line or on the Loop line but not both. You have a While condition on the Do line and then an Until condition on the Loop line. Remove one of those two conditions.

This...
Do While ActiveCell.Value <> ""
'code
Loop

Or this...
Do
'code
Loop Until ActiveCell.Value = ""

I tried both scenarios and its giving thh Loop without Do error..
Worksheet pic 'http://s1229.photobucket.com/albums/ee470/rob_sheeds/?action=view&current=Worksheet.png'..

 

[rob_sheeds]

With a Do-Loop block, you can have the exit condition (While ActiveCell.Value <> "") on the Do line or on the Loop line but not both. You have a While condition on the Do line and then an Until condition on the Loop line. Remove one of those two conditions.

This...
Do While ActiveCell.Value <> ""
'code
Loop

Or this...
Do
'code
Loop Until ActiveCell.Value = ""

You're now missing an End If

Give ths a try if I understand correctly what you are trying to do.

Public Sub Hide_Sheets_If_Zero_Closing_Bal()
Dim cell As Range
Application.ScreenUpdating = False
With Worksheets("Summary")
For Each cell In .Range("E2", .Range("E2").End(xlDown))
Worksheets(cell.Offset(0, -4).Value).Visible = cell.Value <> 0
Next cell
End With
Application.ScreenUpdating = True
End Sub

Tried that but getting subscript out of range error on the line
Worksheets(cell.Offset(0, -4).Value).Visible = cell.Value <> 0

I am in awe of your skills though
I posted a link of the worksheet picture just before though if it help!

 

[Cindy Ellis]

Did you try Alphafrog's code? It should do what you want.
But at a minimum, you need to add "End If" just before the "Loop" statement in your code.
Cindy