For, Next, Loop vs. Select / Case Is
 

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

For, Next, Loop vs. Select / Case Is
 

On Jun 16, 10:41*am, "Vacuum Sealed" wrote:
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

For, Next, Loop vs. Select / Case Is
 

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

For, Next, Loop vs. Select / Case Is
 

On Jun 16, 11:27*am, GS wrote:
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.

For, Next, Loop vs. Select / Case Is
 

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,$N612),2,IF(AND($N6<9 ,$N66),3,IF(AND($N6=1,$N6<5),4,""))))

...and copy down as needed.

--
Garry

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

For, Next, Loop vs. Select / Case Is
 

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


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

--
Garry

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

For, Next, Loop vs. Select / Case Is
 

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