'Do Until...' Restart
 

Hi All,
Thanks for all the replies - but maybe I should start from scratch and
explain the question in, hopefully, a clearer way [ sorry if I confused
everyone]

In A1 I have '=Countif(B1:B500,15)'

The integer values in cells B1:B500 get incremented with each sheet recalc
due to random functions elsewhere in the sheet and therefore A1 is
incremented randomly with each recalc.

The macro is required to recalculate the sheet until A1 = 'exactly' 100. -
i.e when there are exactly 100 cells in B1:B500 with a value of 15.

So I wrote

Do Until Range("a1") = 100
' Do Stuff
Calculate
Loop

The problem is that A1 can be incremented in irregular steps - e.g. from 1
to 2 to 6 etc. and from 93 to 94 to 99 to 102 etc. and therefore a1=100 is
never true - hence a permanent loop

But I need to restart the Do Until loop if and when A1 exceeds 100.

[BTW I can handle the resetting of the values in B1:B500 with a small
routine between 'Loop' and 'End']

Thanks again
Jack

'Do Until...' Restart
 

Hi Jack,
I am trying to understand the logic here.
If A1 is less than 100 (i.e. there are less than 100 entries in B1:B500 that
equal 15), the macro will loop. In that looping are some random actions that
will eventually add the number 15 to the B1:B500 range enough times that cell
A1 will equal or exceed 100.
When that happens, you want the Do Until loop to end.
But then it looks like you are asking for the Do Until loop to restart. How
can it restart if A1 =100?

What am I missing?
--
Ken Hudson


"Jack" wrote:

Hi All,
Thanks for all the replies - but maybe I should start from scratch and
explain the question in, hopefully, a clearer way [ sorry if I confused
everyone]

In A1 I have '=Countif(B1:B500,15)'

The integer values in cells B1:B500 get incremented with each sheet recalc
due to random functions elsewhere in the sheet and therefore A1 is
incremented randomly with each recalc.

The macro is required to recalculate the sheet until A1 = 'exactly' 100. -
i.e when there are exactly 100 cells in B1:B500 with a value of 15.

So I wrote

Do Until Range("a1") = 100
' Do Stuff
Calculate
Loop

The problem is that A1 can be incremented in irregular steps - e.g. from 1
to 2 to 6 etc. and from 93 to 94 to 99 to 102 etc. and therefore a1=100 is
never true - hence a permanent loop

But I need to restart the Do Until loop if and when A1 exceeds 100.

[BTW I can handle the resetting of the values in B1:B500 with a small
routine between 'Loop' and 'End']

Thanks again
Jack

'Do Until...' Restart
 

Hi Ken,
The contents of cells B1:B500 get incremented randomly each time the sheet
is recalculated
A1 checks to see how many cells in B1:B500 = 15.
The macro must run until there are exactly 100 instances of 15 in B1:B500
If A1 does not = 100 then the macro must do a recalculation of the sheet but
the values in B1:B500 do not get reset - hence it's possible for A1 to go
beyond 100 without ever having equalled 100 ; at which point I need to reset
B1:B500 and restart the macro.

Thanks
Jack

"Ken Hudson" wrote in message
...
Hi Jack,
I am trying to understand the logic here.
If A1 is less than 100 (i.e. there are less than 100 entries in B1:B500
that
equal 15), the macro will loop. In that looping are some random actions
that
will eventually add the number 15 to the B1:B500 range enough times that
cell
A1 will equal or exceed 100.
When that happens, you want the Do Until loop to end.
But then it looks like you are asking for the Do Until loop to restart.
How
can it restart if A1 =100?

What am I missing?
--
Ken Hudson


"Jack" wrote:

Hi All,
Thanks for all the replies - but maybe I should start from scratch and
explain the question in, hopefully, a clearer way [ sorry if I confused
everyone]

In A1 I have '=Countif(B1:B500,15)'

The integer values in cells B1:B500 get incremented with each sheet
recalc
due to random functions elsewhere in the sheet and therefore A1 is
incremented randomly with each recalc.

The macro is required to recalculate the sheet until A1 = 'exactly'
100. -
i.e when there are exactly 100 cells in B1:B500 with a value of 15.

So I wrote

Do Until Range("a1") = 100
' Do Stuff
Calculate
Loop

The problem is that A1 can be incremented in irregular steps - e.g. from
1
to 2 to 6 etc. and from 93 to 94 to 99 to 102 etc. and therefore a1=100
is
never true - hence a permanent loop

But I need to restart the Do Until loop if and when A1 exceeds 100.

[BTW I can handle the resetting of the values in B1:B500 with a small
routine between 'Loop' and 'End']

Thanks again
Jack

'Do Until...' Restart
 

An IF statement in the loop will do it, with no need to restart

IF A1100 then
Reset B1:B500
end if

You haven't said HOW you'd reset B1:B500, but whatever that is put in
where I've said Reset B1:B500. This would set A1 to zero, so you don't
need to restart. IF you are worried about a never ending loop, have a
counter variable which is incremented every time the IF statement gets
executed and a further IF statement within the reset routine which
checks that value of that counter - if above a certain bound, get it to
ask (via msgbox) if you want to continue.
Jack wrote:
Hi Ken,
The contents of cells B1:B500 get incremented randomly each time the sheet
is recalculated
A1 checks to see how many cells in B1:B500 = 15.
The macro must run until there are exactly 100 instances of 15 in B1:B500
If A1 does not = 100 then the macro must do a recalculation of the sheet but
the values in B1:B500 do not get reset - hence it's possible for A1 to go
beyond 100 without ever having equalled 100 ; at which point I need to reset
B1:B500 and restart the macro.

Thanks
Jack

"Ken Hudson" wrote in message
...
Hi Jack,
I am trying to understand the logic here.
If A1 is less than 100 (i.e. there are less than 100 entries in B1:B500
that
equal 15), the macro will loop. In that looping are some random actions
that
will eventually add the number 15 to the B1:B500 range enough times that
cell
A1 will equal or exceed 100.
When that happens, you want the Do Until loop to end.
But then it looks like you are asking for the Do Until loop to restart.
How
can it restart if A1 =100?

What am I missing?
--
Ken Hudson


"Jack" wrote:

Hi All,
Thanks for all the replies - but maybe I should start from scratch and
explain the question in, hopefully, a clearer way [ sorry if I confused
everyone]

In A1 I have '=Countif(B1:B500,15)'

The integer values in cells B1:B500 get incremented with each sheet
recalc
due to random functions elsewhere in the sheet and therefore A1 is
incremented randomly with each recalc.

The macro is required to recalculate the sheet until A1 = 'exactly'
100. -
i.e when there are exactly 100 cells in B1:B500 with a value of 15.

So I wrote

Do Until Range("a1") = 100
' Do Stuff
Calculate
Loop

The problem is that A1 can be incremented in irregular steps - e.g. from
1
to 2 to 6 etc. and from 93 to 94 to 99 to 102 etc. and therefore a1=100
is
never true - hence a permanent loop

But I need to restart the Do Until loop if and when A1 exceeds 100.

[BTW I can handle the resetting of the values in B1:B500 with a small
routine between 'Loop' and 'End']

Thanks again
Jack