For, Next, Loop vs. Select / Case Is

[Vacuum Sealed]

Hi all

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

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

 

[Dom]

Hi all

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

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

 

[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

 

[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.

 

[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.

 

[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,""))))

 

[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
in mind, an added Rider:

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