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
|