# For, Next, Loop vs. Select / Case Is

V

#### Vacuum Sealed

Hi all

Still haven't quite grasped the above, can anyone help with the correct

Essentially, I need it to loop through i until all the 4 statements are
fulfilled, then step out and end once it is achieved

I wasn't entirely sure this is the right idea, or if a Case statement may
serve better.

Sub MyValueOffset()

Dim i As Integer

Do While Cells(i, 14).Value > 0

For i = 6 To 250

If Cells(i, 14).Value >= 20 Then
Cells(i, 14).Offset(0, 21).Value = 1

If Cells(i, 14).Value < 13 > 9 Then
Cells(i, 14).Offset(0, 21).Value = 2

If Cells(i, 14).Value < 9 > 6 Then
Cells(i, 14).Offset(0, 21).Value = 3

If Cells(i, 14).Value < 5 Then
Cells(i, 14).Offset(0, 21).Value = 4

End If
End If
End If
End If

Next i

Loop

End Sub

TIA
Mick

D

#### Dom

Hi all

Still haven't quite grasped the above, can anyone help with the correct

Essentially, I need it to loop through i until all the 4 statements are
fulfilled, then step out and end once it is achieved

I wasn't entirely sure this is the right idea, or if a Case statement may
serve better.

Sub MyValueOffset()

Dim i As Integer

Do While Cells(i, 14).Value > 0

For i = 6 To 250

If Cells(i, 14).Value >= 20 Then
Cells(i, 14).Offset(0, 21).Value = 1

If Cells(i, 14).Value < 13 > 9 Then
Cells(i, 14).Offset(0, 21).Value = 2

If Cells(i, 14).Value < 9 > 6 Then
Cells(i, 14).Offset(0, 21).Value = 3

If Cells(i, 14).Value < 5 Then
Cells(i, 14).Offset(0, 21).Value = 4

End If
End If
End If
End If

Next i

Loop

End Sub

TIA
Mick

There is a lot wrong here.

1. Some of the IF statements are wrong. It should read:
"If Cells(i, 14).Value < 13 and Cells(i, 14).Value > 9 Then".

Also, it would be easier to read if you didn't make them blocks
and used parentheses, eg:
"If (Cells(i, 14).Value < 13 and Cells(i, 14).Value > 9) Cells(i,
14).Offset(0, 21).Value = 2"

2. More serious, the "DO LOOP" begins before the counter is defined.
That is, you have Do while (Cells(i, 14).Value > 0), and there is no
mention of "i". I think what you want is:

for i = 6, 250
if (Cells(i, 14).Value <= 0) exit for

If (Cells(i, 14).Value >= 20) Cells(i, 14).Offset(0, 21).Value
= 1
If (Cells(i, 14).Value < 13 and Cells(i,14) > 9) Cells(i,
14).Offset(0, 21).Value = 2
If (Cells(i, 14).Value < 9 and Cells(i, 14) > 6) Cells(i,
14).Offset(0, 21).Value = 3
If (Cells(i, 14).Value < 5) Cells(i, 14).Offset(0, 21).Value =
4
next i

G

#### GS

To add to Dom's input, you might want to use a Select Case structure so
the code is easier to understand...

Sub MyValueOffset_2()
Dim i As Integer
For i = 6 To 250
Do While Cells(i, 14).Value > 0
Select Case Cells(i, 14).Value
Case >= 20: Cells(i, 14).Offset(0, 21).Value = 1
Case 9 To 12: Cells(i, 14).Offset(0, 21).Value = 2
Case 7, 8: Cells(i, 14).Offset(0, 21).Value = 3
Case < 5: Cells(i, 14).Offset(0, 21).Value = 4
End Select 'Cells(i, 14).Value
Loop 'While Cells(i, 14).Value > 0
Next
End Sub

D

#### Dom

To add to Dom's input, you might want to use a Select Case structure so
the code is easier to understand...

Sub MyValueOffset_2()
Dim i As Integer
For i = 6 To 250
Do While Cells(i, 14).Value > 0
Select Case Cells(i, 14).Value
Case >= 20: Cells(i, 14).Offset(0, 21).Value = 1
Case 9 To 12: Cells(i, 14).Offset(0, 21).Value = 2
Case 7, 8: Cells(i, 14).Offset(0, 21).Value = 3
Case < 5: Cells(i, 14).Offset(0, 21).Value = 4
End Select 'Cells(i, 14).Value
Loop 'While Cells(i, 14).Value > 0
Next
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry, you put the DO WHILE loop within the FOR LOOP. You'll never
get out of it.

G

#### GS

Garry, you put the DO WHILE loop within the FOR LOOP. You'll never
get out of it.

Geez.., you're right! I meant to not include it because I didn't see
any reason for it being there since the only time code will execute is
if the values fall into the Case scenarios. Also, last Case scenario
needs a range...

Sub MyValueOffset_2()
Dim i As Integer
For i = 6 To 250
Select Case Cells(i, 14).Value
Case >= 20: Cells(i, 14).Offset(0, 21).Value = 1
Case 9 To 12: Cells(i, 14).Offset(0, 21).Value = 2
Case 7, 8: Cells(i, 14).Offset(0, 21).Value = 3
Case 1 To 5: Cells(i, 14).Offset(0, 21).Value = 4
End Select 'Cells(i, 14).Value
Next
End Sub

Also, I was going to offer the following cell formula that can be
copied down so values are calced automatically...

In cell AI6 enter:

=IF(\$N6>=20,1,IF(AND(\$N6<19,\$N6>12),2,IF(AND(\$N6<9,\$N6>6),3,IF(AND(\$N6>=1,\$N6<5),4,""))))

...and copy down as needed.

G

#### GS

Sorry, ..wrong formula: should be...

=IF(\$N6>=20,1,IF(AND(\$N6<13,\$N6>8),2,IF(OR(\$N6=7,\$N6=8),3,IF(AND(\$N6>=1,\$N6<=5),4,""))))

V

#### Vacuum Sealed

Garry

As always, ever reliable, and Dom thank you both for your assistance, the
code works well...

As for formulas, I have an IT Ogre who doesn't like nested formulas as he
likes to see the effect, not the cause, so to speak..

I have been bangin' on a bit lately regarding Knapsack Problems, with that

What is the maximum number of loops you can have with a maximum amount of
loops...

Loop 1 retains the first Group in Criteria = Group 1

Group 1 is then isolated, then Looped by another loop to find the next
SubSet that matches the 2nd criteria.

And this is where the Knapsack comes into play..

Those retained/grouped within the criteria then need to be grouped so that
they sum to 22, but they can't exceed 24.5..

To find a solution to this would be my Everest, and to the top of it would
be my graile...

Thx heaps again guy's