ElseIf into a For Next
Author Message ElseIf into a For Next

Can someone help me turn this ElseIf logic into a For Next loop:

If CTR <= 50 Then
Spinner(1) = True
ElseIf CTR > 50 And CTR <= 100 Then
Spinner(2) = True
ElseIf CTR > 100 And CTR <= 150 Then
Spinner(3) = True
ElseIf CTR > 150 And CTR <= 200 Then
Spinner(4) = True
ElseIf CTR > 200 And CTR <= 250 Then
Spinner(5) = True
ElseIf CTR > 250 And CTR <= 300 Then
Spinner(6) = True
ElseIf CTR > 300 Then
Spinner(7) = True
End If

Regards
FM

Fri, 13 Jul 2012 02:13:13 GMT  ElseIf into a For Next
I think you will need to tell us more about your set up before you will get
a "good" answer to your question. First off, I'm guessing you request for a
For..Next loop is to have the code iterate the Spinner (control?) array,
correct? Please answer all of the following questions...

1. Do you need the Spinner (control?) array to be set to False if the value
is not in the indicated range?

2. Are those ranges for CTR correct as shown, or are they just made up
example numbers (I ask because regular range intervals like you show lend
themselves to simple mathematical formulas rather than individual range
examinations)?

3. Can CTR ever be less than zero?

4. Can CTR ever be greater than 350?

While you don't seem to want to use your current If..ElseIf..Then format, I
just wanted to point out that you are doing too much testing in the majority
of your If/ElseIf statements. If..ElseIf..Then blocks execute in the order
listed; because of that, once you test for the a less than or equal
condition in the prior test, you don't have to test to see if the number is
greater than that prior test's upper range... it can't be lower or the prior
test would have trapped it. So, your posted code could be simplified to
this...

If CTR <= 50 Then
Spinner(1) = True
ElseIf CTR <= 100 Then
Spinner(2) = True
ElseIf CTR <= 150 Then
Spinner(3) = True
ElseIf CTR <= 200 Then
Spinner(4) = True
ElseIf CTR <= 250 Then
Spinner(5) = True
ElseIf CTR <= 300 Then
Spinner(6) = True
ElseIf 300 Then
Spinner(7) = True
End If

--
Rick (MVP - Excel)

Quote:
> Can someone help me turn this ElseIf logic into a For Next loop:

> If CTR <= 50 Then
>   Spinner(1) = True
> ElseIf CTR > 50 And CTR <= 100 Then
>   Spinner(2) = True
> ElseIf CTR > 100 And CTR <= 150 Then
>   Spinner(3) = True
> ElseIf CTR > 150 And CTR <= 200 Then
>   Spinner(4) = True
> ElseIf CTR > 200 And CTR <= 250 Then
>   Spinner(5) = True
> ElseIf CTR > 250 And CTR <= 300 Then
>   Spinner(6) = True
> ElseIf CTR > 300 Then
>   Spinner(7) = True
> End If

> Regards
> FM

Fri, 13 Jul 2012 02:55:05 GMT  ElseIf into a For Next
Oh, and what data type (Integer, Long, Single, Double, etc.) is CTR declared
as? If you didn't declare it, then is CTR always a whole number or can it be
a floating point number?

--
Rick (MVP - Excel)

Quote:
>I think you will need to tell us more about your set up before you will get
>a "good" answer to your question. First off, I'm guessing you request for a
>For..Next loop is to have the code iterate the Spinner (control?) array,
>correct? Please answer all of the following questions...

> 1. Do you need the Spinner (control?) array to be set to False if the
> value is not in the indicated range?

> 2. Are those ranges for CTR correct as shown, or are they just made up
> example numbers (I ask because regular range intervals like you show lend
> themselves to simple mathematical formulas rather than individual range
> examinations)?

> 3. Can CTR ever be less than zero?

> 4. Can CTR ever be greater than 350?

> While you don't seem to want to use your current If..ElseIf..Then format,
> I just wanted to point out that you are doing too much testing in the
> majority of your If/ElseIf statements. If..ElseIf..Then blocks execute in
> the order listed; because of that, once you test for the a less than or
> equal condition in the prior test, you don't have to test to see if the
> number is greater than that prior test's upper range... it can't be lower
> or the prior test would have trapped it. So, your posted code could be
> simplified to this...

> If CTR <= 50 Then
>   Spinner(1) = True
> ElseIf CTR <= 100 Then
>   Spinner(2) = True
> ElseIf CTR <= 150 Then
>   Spinner(3) = True
> ElseIf CTR <= 200 Then
>   Spinner(4) = True
> ElseIf CTR <= 250 Then
>   Spinner(5) = True
> ElseIf CTR <= 300 Then
>   Spinner(6) = True
> ElseIf 300 Then
>   Spinner(7) = True
> End If

> --
> Rick (MVP - Excel)

>> Can someone help me turn this ElseIf logic into a For Next loop:

>> If CTR <= 50 Then
>>   Spinner(1) = True
>> ElseIf CTR > 50 And CTR <= 100 Then
>>   Spinner(2) = True
>> ElseIf CTR > 100 And CTR <= 150 Then
>>   Spinner(3) = True
>> ElseIf CTR > 150 And CTR <= 200 Then
>>   Spinner(4) = True
>> ElseIf CTR > 200 And CTR <= 250 Then
>>   Spinner(5) = True
>> ElseIf CTR > 250 And CTR <= 300 Then
>>   Spinner(6) = True
>> ElseIf CTR > 300 Then
>>   Spinner(7) = True
>> End If

>> Regards
>> FM

Fri, 13 Jul 2012 03:04:58 GMT  ElseIf into a For Next
Quote:

> Can someone help me turn this ElseIf logic into a For Next loop:

...sample code elided for brevity...

Don't need either.

1) set Spinner() = False

2) algorithm

idx = Max(Min(Int(CTR/50+1),7),1)
Spinner(idx) = True

--

Fri, 13 Jul 2012 03:14:36 GMT  ElseIf into a For Next
...

oops, typo..

Quote:
> 2) algorithm

> idx = Max(Min(Int(CTR/50+1),7),1)

...

idx = Max(Min(Int(CTR/50)+1,7),1)

Note misplaced parenthesis, sorry.

--

Fri, 13 Jul 2012 03:53:42 GMT  ElseIf into a For Next

Quote:
> Can someone help me turn this ElseIf logic into a For Next loop:

> If CTR <= 50 Then
>   Spinner(1) = True
> ElseIf CTR > 50 And CTR <= 100 Then
>   Spinner(2) = True
> ElseIf CTR > 100 And CTR <= 150 Then
>   Spinner(3) = True
> ElseIf CTR > 150 And CTR <= 200 Then
>   Spinner(4) = True
> ElseIf CTR > 200 And CTR <= 250 Then
>   Spinner(5) = True
> ElseIf CTR > 250 And CTR <= 300 Then
>   Spinner(6) = True
> ElseIf CTR > 300 Then
>   Spinner(7) = True
> End If

As Rick said, you probably need to answer a few missing details.  Assuming
this code already does *exactly* what you need, I don't see why you need a
For Next loop. What would be looping? Now, if that code DOESN'T do what you
need, then you need to tell us what what it is exactly that you need to do.

If all you're wanting is to eliminate all those ElseIfs (some people really
detest a bunch of ElseIfs), then you'd use a Select Case structure instead.
Also note that you don't need an And expression with any of these (you don't
need to check any > expressions because the previous < expressions take care
of that).

Select Case CTR
Case Is <= 50
Spinner(1) = True
Case Is <= 100
Spinner(2) = True
Case Is <= 150
Spinner(3) = True
Case Is <= 200
Spinner(4) = True
Case Is <= 250
Spinner(5) = True
Case Is <= 300
Spinner(6) = True
Case Else
Spinner(7) = True
End Select

Of course, the Select Case I wrote is untried and untested air code, so
maybe I missed something.  <g>

--
Mike

Fri, 13 Jul 2012 03:56:34 GMT  ElseIf into a For Next
...

Quote:
> 1) set Spinner() = False

...

Btw, if make Spinner() fixed-size array (as opposed to dynamic) the
Erase statement is useful here to minimize LOC.

Erase Spinner  ' Set elements of Spinner() = 0 (==False)

--

Fri, 13 Jul 2012 05:06:41 GMT  ElseIf into a For Next
To clarify: There are seven animations. Each animation is a 3D dice spinning
and shows each side of the dice during its spin and 90 frames/images are a
full spin. Each animation loops at least a full 90 frames and then continues
to spin until the target frame is reached. So each will spin at least 90
frames but no more than 180 frames. Spinner is simply a boolean flag to
check when all animations have stopped (ie. Do Until AllSpinners =
False...this is a function check that looks at the state of Spinners(1 to
7)). The point of the ElseIf (that is inside the Do Until Loop) is to start
each roll of the dice sequentially so that each dice starts spinning when
the dice before it has spun 50 frames. This creates a mexican wave kind of
effect. By the time the seventh dice is spinning the first 2 or 3 dice have
normally stopped so they are not all spinning at the same time, hence
Spinners(1 to 7) are not all always true and only all always false once all
have spun a full 90 frames + frames to target.

CTR is simply a frame counter and can be greater than 300 (but never to
extreme more than 7 * 180). Ctr is a whole number currently declared as an
integer. I see now I was doing too much testing with the ElseIf.

I actually have this working nice but Im trying to make it more flexible so
that I can call any dice or any number of dice instead of running all seven
animations. Was thinking a For Next would make that more manageable. Perhaps
a better approach is needed?

Regards
FM

Fri, 13 Jul 2012 05:24:52 GMT  ElseIf into a For Next

Quote:
> To clarify: There are seven animations. Each animation is a 3D dice
> spinning and shows each side of the dice during its spin and 90
> frames/images are a full spin. Each animation loops at least a full 90
> frames and then continues to spin until the target frame is reached.
> So each will spin at least 90 frames but no more than 180 frames.
> Spinner is simply a boolean flag to check when all animations have
> stopped (ie. Do Until AllSpinners = False...this is a function check
> that looks at the state of Spinners(1 to 7)). The point of the ElseIf
> (that is inside the Do Until Loop) is to start each roll of the dice
> sequentially so that each dice starts spinning when the dice before it
> has spun 50 frames. This creates a mexican wave kind of effect. By the
> time the seventh dice is spinning the first 2 or 3 dice have normally
> stopped so they are not all spinning at the same time, hence
> Spinners(1 to 7) are not all always true and only all always false
> once all have spun a full 90 frames + frames to target.

> CTR is simply a frame counter and can be greater than 300 (but never
> to extreme more than 7 * 180). Ctr is a whole number currently
> declared as an integer. I see now I was doing too much testing with
> the ElseIf.

> I actually have this working nice but Im trying to make it more
> flexible so that I can call any dice or any number of dice instead of
> running all seven animations. Was thinking a For Next would make that
> more manageable. Perhaps a better approach is needed?

> Regards
> FM

My guess is something like this air-code will do it.....

For x = LBound(Spinner) To UBound(Spinner)
If CTR > ((x - 1) * 50) Then
Spinner(x) = True
End If
Next

If the array was zero-based, this would need to be accounted for, but the
above should work with a 1-based array.

Fri, 13 Jul 2012 06:15:55 GMT  ElseIf into a For Next
To rephrase this question further. Bear with me :) Im passing the number of
dice to spin and which dice to spin. It could be one or seven. The Spinning
(D parameter) below refers to the particular dice to spin. In the following
code four dice will roll: D1 could be passed for example as 1, D2 as 3, D3
as 5, D4 as 7, so the 1st, 3rd, 5th and 7th dice will spin. Each starts
spinning sequentially like a mexican wave as explained in my earlier post
using the ElseIf. It works perfectly well but as can be seen it gets a bit
drawn out. Is there a more efficient way to do this:

Select Case NumDiceToSpin
Case 1 'Only roll one Dice
Spinning(D1) = True 'D1 refers to the dice to roll between 1 and 7
Case 2 'Only Roll two Dice
If CTR <= 50 Then
Spinning(D1) = True
ElseIf CTR <= 100 Then
Spinning(D2) = True
End If
Case 3 'Only roll three Dice
If CTR <= 50 Then
Spinning(D1) = True
ElseIf CTR <= 100 Then
Spinning(D2) = True
ElseIf CTR <= 150 Then
Spinning(D3) = True
End If
Case 4 'Only roll four dice
If CTR <= 50 Then
Spinning(D1) = True
ElseIf CTR <= 100 Then
Spinning(D2) = True
ElseIf CTR <= 150 Then
Spinning(D3) = True
ElseIf CTR <= 200 Then
Spinning(D4) = True
End If
'and so on...through to case 7 if more dice are to spin
End Select
~
Regards
FM

Fri, 13 Jul 2012 08:57:56 GMT  ElseIf into a For Next

Quote:
> To rephrase this question further. Bear with me :) Im passing the number of
> dice to spin and which dice to spin. It could be one or seven. The Spinning
> (D parameter) below refers to the particular dice to spin. In the following
> code four dice will roll: D1 could be passed for example as 1, D2 as 3, D3
> as 5, D4 as 7, so the 1st, 3rd, 5th and 7th dice will spin. Each starts
> spinning sequentially like a mexican wave as explained in my earlier post
> using the ElseIf. It works perfectly well but as can be seen it gets a bit
> drawn out. Is there a more efficient way to do this:

> Select Case NumDiceToSpin

So using what we know from above...
Erase spinning()
Spinning(Max(Min(Int(CTR/50)+1,NumDiceToSpin),1) )
is what you want, then there is a free "for loop" to use for something
else *smile*

but I still dont get it.. .

Dim test As Integer
For test = 0 To 350 Step 50 ' as testin 222 is the same as 200 and 249
Debug.Print test, Max(Min(Int(test / 50) + 1, 7), 1)
Next test
Stop

something like this must be there somewhere too, oh where to put is I
dont know ;)

Function Max(ParamArray TheValues() As Variant) As Variant
Dim intLoop As Integer
Dim varCurrentMax As Variant
varCurrentMax = TheValues(LBound(TheValues))
For intLoop = LBound(TheValues) + 1 To UBound(TheValues)
If TheValues(intLoop) > varCurrentMax Then
varCurrentMax = TheValues(intLoop)
End If
Next intLoop
Max = varCurrentMax
End Function
Function Min(ParamArray TheValues() As Variant) As Variant
Dim intLoop As Integer
Dim varCurrentMin As Variant
varCurrentMin = TheValues(LBound(TheValues))
For intLoop = LBound(TheValues) + 1 To UBound(TheValues)
If TheValues(intLoop) < varCurrentMin Then
varCurrentMin = TheValues(intLoop)
End If
Next intLoop
Min = varCurrentMin
End Function

would get you

1             1
51            2
101           3
151           4
201           5
251           6
301           7

or if starting at 0

0             1
50            2
100           3
150           4
200           5
250           6
300           7
350           7

I could read what you wrote but no....

Fri, 13 Jul 2012 22:14:31 GMT  ElseIf into a For Next
It is unclear to me if you need to reset all the Spinning array elements to False before setting the "correct" one to True... if you do, then you will need to run this loop first...

For X = 1 To 7
Spinning(X) = False
Next

Next, take your D1, D2, D3 etc. variables and make them into an array named D (Dim'med from 1 to 7) so that whatever you assign to D1 would be assigned to D(1) and whatever you would assign to D2 would be assigned to D(2) and so on. Then, after you have done that, and if I did this right, you can replace **all** of the code you posted with this single line of code...

Spinning(D(WorksheetFunction(Min(NumDiceToSpin, Int((CTR + 49.99999999) / 50))))) = True

--
Rick (MVP - Excel)

Quote:

> To rephrase this question further. Bear with me :) Im passing the number of
> dice to spin and which dice to spin. It could be one or seven. The Spinning
> (D parameter) below refers to the particular dice to spin. In the following
> code four dice will roll: D1 could be passed for example as 1, D2 as 3, D3
> as 5, D4 as 7, so the 1st, 3rd, 5th and 7th dice will spin. Each starts
> spinning sequentially like a mexican wave as explained in my earlier post
> using the ElseIf. It works perfectly well but as can be seen it gets a bit
> drawn out. Is there a more efficient way to do this:

> Select Case NumDiceToSpin
> Case 1 'Only roll one Dice
>   Spinning(D1) = True 'D1 refers to the dice to roll between 1 and 7
> Case 2 'Only Roll two Dice
>   If CTR <= 50 Then
>      Spinning(D1) = True
>   ElseIf CTR <= 100 Then
>      Spinning(D2) = True
>   End If
> Case 3 'Only roll three Dice
>   If CTR <= 50 Then
>      Spinning(D1) = True
>   ElseIf CTR <= 100 Then
>      Spinning(D2) = True
>   ElseIf CTR <= 150 Then
>      Spinning(D3) = True
>   End If
> Case 4 'Only roll four dice
>   If CTR <= 50 Then
>      Spinning(D1) = True
>   ElseIf CTR <= 100 Then
>      Spinning(D2) = True
>   ElseIf CTR <= 150 Then
>      Spinning(D3) = True
>   ElseIf CTR <= 200 Then
>      Spinning(D4) = True
>   End If
> 'and so on...through to case 7 if more dice are to spin
> End Select
> ~
> Regards
> FM

Sat, 14 Jul 2012 06:21:20 GMT  ElseIf into a For Next
Sorry, I forgot that I was not in an Excel newsgroup, so you do not have access to the WorksheetFunction's (even though my syntax for it was incorrect). Everything about my previous post applies here except for the "single line of code" I posted at the end of my message... it needs to be these two lines of code instead (Dim Index as Long and still use an array named D in place of your D1, D2, etc.)...

Index = Int((CTR + 49.99999999) / 50)
Spinning(D(NumDiceToSpin, IIf(Index > 7, 7, Index))) = True

--
Rick (MVP - Excel)

Quote:
> It is unclear to me if you need to reset all the Spinning array
> elements to False before setting the "correct" one to True...
> if you do, then you will need to run this loop first...

> For X = 1 To 7
>   Spinning(X) = False
> Next
> Next, take your D1, D2, D3 etc. variables and make them into
> an array named D (Dim'med from 1 to 7) so that whatever you
> assign to D1 would be assigned to D(1) and whatever you
> would assign to D2 would be assigned to D(2) and so on.
> Then, after you have done that, and if I did this right, you can
> replace **all** of the code you posted with this single line of code...

> Spinning(D(WorksheetFunction(Min(NumDiceToSpin, Int((CTR + 49.99999999) / 50))))) = True

--
Rick (MVP - Excel)
Quote:

> To rephrase this question further. Bear with me :) Im passing the number of
> dice to spin and which dice to spin. It could be one or seven. The Spinning
> (D parameter) below refers to the particular dice to spin. In the following
> code four dice will roll: D1 could be passed for example as 1, D2 as 3, D3
> as 5, D4 as 7, so the 1st, 3rd, 5th and 7th dice will spin. Each starts
> spinning sequentially like a mexican wave as explained in my earlier post
> using the ElseIf. It works perfectly well but as can be seen it gets a bit
> drawn out. Is there a more efficient way to do this:

> Select Case NumDiceToSpin
> Case 1 'Only roll one Dice
>   Spinning(D1) = True 'D1 refers to the dice to roll between 1 and 7
> Case 2 'Only Roll two Dice
>   If CTR <= 50 Then
>      Spinning(D1) = True
>   ElseIf CTR <= 100 Then
>      Spinning(D2) = True
>   End If
> Case 3 'Only roll three Dice
>   If CTR <= 50 Then
>      Spinning(D1) = True
>   ElseIf CTR <= 100 Then
>      Spinning(D2) = True
>   ElseIf CTR <= 150 Then
>      Spinning(D3) = True
>   End If
> Case 4 'Only roll four dice
>   If CTR <= 50 Then
>      Spinning(D1) = True
>   ElseIf CTR <= 100 Then
>      Spinning(D2) = True
>   ElseIf CTR <= 150 Then
>      Spinning(D3) = True
>   ElseIf CTR <= 200 Then
>      Spinning(D4) = True
>   End If
> 'and so on...through to case 7 if more dice are to spin
> End Select
> ~
> Regards
> FM

Sat, 14 Jul 2012 12:01:58 GMT  ElseIf into a For Next
Actually, we can still make it a one-liner. Instead of these two lines of
code...

Index = Int((CTR + 49.99999999) / 50)
Spinning(D(NumDiceToSpin, IIf(Index > 7, 7, Index))) = True

use this single line of code in place of them...

Spinning(D(NumDiceToSpin, Int(IIf(CTR > 300, 301, CTR) + 49.99999999) / 50))
= True

--
Rick (MVP - Excel)

Quote:
> Sorry, I forgot that I was not in an Excel newsgroup, so you
> do not have access to the WorksheetFunction's (even
> though my syntax for it was incorrect). Everything about
> my previous post applies here except for the "single line
> of code" I posted at the end of my message... it needs to
> be these two lines of code instead (Dim Index as Long
> and still use an array named D in place of your D1, D2, etc.)...

> Index = Int((CTR + 49.99999999) / 50)
> Spinning(D(NumDiceToSpin, IIf(Index > 7, 7, Index))) = True

> > It is unclear to me if you need to reset all the Spinning array
> > elements to False before setting the "correct" one to True...
> > if you do, then you will need to run this loop first...

> > For X = 1 To 7
> >   Spinning(X) = False
> > Next

> > > Next, take your D1, D2, D3 etc. variables and make them into
> > > an array named D (Dim'med from 1 to 7) so that whatever you
> > > assign to D1 would be assigned to D(1) and whatever you
> > > would assign to D2 would be assigned to D(2) and so on.
> > > Then, after you have done that, and if I did this right, you can
> > > replace **all** of the code you posted with this single line of
> > > code...

> > > Spinning(D(WorksheetFunction(Min(NumDiceToSpin, Int((CTR +
> > > 49.99999999) / 50))))) = True

> > > > To rephrase this question further. Bear with me :) Im passing the
> > > > number of
> > > > dice to spin and which dice to spin. It could be one or seven. The
> > > > Spinning
> > > > (D parameter) below refers to the particular dice to spin. In the
> > > > following
> > > > code four dice will roll: D1 could be passed for example as 1, D2 as
> > > > 3, D3
> > > > as 5, D4 as 7, so the 1st, 3rd, 5th and 7th dice will spin. Each
> > > > starts
> > > > spinning sequentially like a mexican wave as explained in my earlier
> > > > post
> > > > using the ElseIf. It works perfectly well but as can be seen it gets
> > > > a bit
> > > > drawn out. Is there a more efficient way to do this:

> > > > Select Case NumDiceToSpin
> > > > Case 1 'Only roll one Dice
> > > >   Spinning(D1) = True 'D1 refers to the dice to roll between 1 and 7
> > > > Case 2 'Only Roll two Dice
> > > >   If CTR <= 50 Then
> > > >      Spinning(D1) = True
> > > >   ElseIf CTR <= 100 Then
> > > >      Spinning(D2) = True
> > > >   End If
> > > > Case 3 'Only roll three Dice
> > > >   If CTR <= 50 Then
> > > >      Spinning(D1) = True
> > > >   ElseIf CTR <= 100 Then
> > > >      Spinning(D2) = True
> > > >   ElseIf CTR <= 150 Then
> > > >      Spinning(D3) = True
> > > >   End If
> > > > Case 4 'Only roll four dice
> > > >   If CTR <= 50 Then
> > > >      Spinning(D1) = True
> > > >   ElseIf CTR <= 100 Then
> > > >      Spinning(D2) = True
> > > >   ElseIf CTR <= 150 Then
> > > >      Spinning(D3) = True
>> > >    ElseIf CTR <= 200 Then
> > > >      Spinning(D4) = True
> > > >   End If
> > > > 'and so on...through to case 7 if more dice are to spin
> > > > End Select

Sat, 14 Jul 2012 12:12:46 GMT  ElseIf into a For Next

Ive got this up and running perfectly with the total control and flexibility
I wanted. I couldnt get any of the formulas to work right so have decided to
stick with the ElseIf structure. It does the job and its only 70 odd lines
of code so I can live with that. As always appreciate your help and
assistance.

Regards
FM

Sun, 15 Jul 2012 09:51:40 GMT

 Page 1 of 2 [ 16 post ] Go to page:  

Relevant Pages

Powered by phpBB® Forum Software