For, Next, Loop vs. Select / Case Is

  • Thread starter Thread starter Vacuum Sealed
  • Start date Start date
V

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
 
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
Click to expand...

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
 
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
 
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
Click to expand...

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

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.
 
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,""))))
 
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
 
Back
Top