Need help with For-Next block

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 25 Sep 2007 13:12 GMT

I have the following code block:

For x = 1 To 10
If RowIsBlank(8) = True Then
Application.DisplayAlerts = False
Workbooks(TargetFile(x)).Close
Application.DisplayAlerts = True
Next x
End If
... more instructions ...
Next x

but when I run the code, I receive the expected "Next without For" error
message.
I need to somehow increment "x" when the IF-block evaluates to True. Can
someone kindly tell me how to pull this off?

Thanks in advance for any help.

Reply to this Message

Mike H - 25 Sep 2007 13:16 GMT

Bob,

Try

For x = 1 To 10
If RowIsBlank(8) = True Then
Application.DisplayAlerts = False
Workbooks(TargetFile(x)).Close
Application.DisplayAlerts = True
x=x+1
End If
... more instructions ...
Next x

Mike

> I have the following code block:
>
[quoted text clipped - 4 lines]
>
> Thanks in advance for any help.

Reply to this Message

Bob - 25 Sep 2007 13:42 GMT

Mike,
I should have been more clear in my objective. My apologies.

If the IF-block evaluates to True, not only do I need to increment "x" by 1,
but I also need to skip the "... more instructions ..." section, too. That's
the part I'm struggling with.

Thanks again for the help.
Bob

> Bob,
>
[quoted text clipped - 20 lines]
> >
> > Thanks in advance for any help.

Reply to this Message

Mike H - 25 Sep 2007 13:52 GMT

Bob,

There will probably be cries of outrage and spaghetti code accusations but
I'd use Goto like this

For x = 1 To 10
If RowIsBlank(8) = True Then
Application.DisplayAlerts = False
Workbooks(TargetFile(x)).Close
Application.DisplayAlerts = True
Goto 100
End If
... more instructions ...
100
Next x

X will then increment by 1 when it reaches Next X

Mike

> Mike,
> I should have been more clear in my objective. My apologies.
[quoted text clipped - 30 lines]
> > >
> > > Thanks in advance for any help.

Reply to this Message

Bob - 25 Sep 2007 14:06 GMT

Mike,
I, too, prefer NOT to use Goto. JW suggested using an Else clause which did
the trick. Thanks all the same.
Bob

> Bob,
>
[quoted text clipped - 50 lines]
> > > >
> > > > Thanks in advance for any help.

Reply to this Message

JW - 25 Sep 2007 13:58 GMT

Not to take anything away from Mike's code, but I still believe that a
simple Else clause is all you need.
Do this if true
Else
Do this if false
Next x

Seems to me to be exactly what you want. See the code I posted
earlier. If I am completely lost, then please set me straight.

> Mike,
> I should have been more clear in my objective. My apologies.
[quoted text clipped - 30 lines]
> > >
> > > Thanks in advance for any help.

Reply to this Message

Mike H - 25 Sep 2007 14:06 GMT

JW,

I agree your solution is much neater.

Mike

> Not to take anything away from Mike's code, but I still believe that a
> simple Else clause is all you need.
[quoted text clipped - 40 lines]
> > > >
> > > > Thanks in advance for any help.

Reply to this Message

JW - 25 Sep 2007 13:20 GMT

x will automatically increment wen the Next x line is reached. You do
not need two Next x lines to pull this off.
Do you want to skip over the ...more instructions... portion if the If
statement is true? That's the way I am interpreting it. If so, just
throw in an Else clause and remove the unneeded Next x statement.
For x = 1 To 10
If RowIsBlank(8) = True Then
Application.DisplayAlerts = False
Workbooks(TargetFile(x)).Close
Application.DisplayAlerts = True
Else
... more instructions ...
End If
Next x

> I have the following code block:
>
[quoted text clipped - 14 lines]
>
> Thanks in advance for any help.

Reply to this Message

Bob - 25 Sep 2007 14:06 GMT

JW,
Yes, I also wanted to skip over the ...more instructions... portion.
Inserting an Else clause did the trick. (I'm embarrassed that I didn't think
of that.) Thanks for your help!
Bob

> x will automatically increment wen the Next x line is reached. You do
> not need two Next x lines to pull this off.
[quoted text clipped - 29 lines]
> >
> > Thanks in advance for any help.

Reply to this Message

Nobody - 25 Sep 2007 17:29 GMT

Perhaps...

For x = 1 to 10
If RowIsBlank(8) = True Then
Application.DisplayAlerts = False
Workbooks(TargetFile(x)).Close
Application.DisplayAlerts = True
x = x +1
Else
... more instructions ...
End If
Next x

>I have the following code block:
>
[quoted text clipped - 14 lines]
>
> Thanks in advance for any help.

Reply to this Message