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

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

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

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.

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

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

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