probelm with a loop

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,
I've got a problem with a loop: sometimes it gives me
wrong results.
My code follows:

For i = 2 To WB.Worksheets.Count

If i <= 5 Then
ActiveSheet.Range("E57:E67").Select
ElseIf i = 6 Then
ActiveSheet.Range("E82:E94").Select
ElseIf 6 < i < 10 Then
ActiveSheet.Range("E57:E67").Select
ElseIf i = 10 Then
ActiveSheet.Range("E82:E94").Select
ElseIf i = 11 Then
ActiveSheet.Range("E57:E67").Select
ElseIf i = 12 Then
ActiveSheet.Range("E82:E94").Select
ElseIf 13 < i < 14 Then
ActiveSheet.Range("E57:E67").Select
Else
ActiveSheet.Range("E82:E94").Select
End If

Next i

for value between 2 and 9 (involved), it selects the correct range;
from 10 to the end I've got problems
for example, when i = 10, loop selects range("E57:E67") and not
the correct range("E82:E94").
and this is the same thing when i is equal to 12.

Then I tried to compile a easier code:

If i <= 5 Then
ActiveSheet.Range("E57:E67").Select
ElseIf 6 < i < 10 Then
ActiveSheet.Range("E57:E67").Select
ElseIf i = 11 Then
ActiveSheet.Range("E57:E67").Select
ElseIf 13 < i < 14 Then
ActiveSheet.Range("E57:E67").Select
Else
ActiveSheet.Range("E82:E94").Select
End If

But I've got the same problem...
I don't understand where I'm wrong ..
could you help me please ?
Any help very appreciated !

sincerely,
AP98
 
Hi

try

For i = 2 to WB.Worksheets.Count
Select Case i
Case Is <= 5
ActiveSheet.Range("E57:E67").Select
Case Is = 6
ActiveSheet.Range("E82:E94").Select
Case Is < 10
ActiveSheet.Range("E57:E67").Select
Case Is = 10
ActiveSheet.Range("E82:E94").Select
Case Is = 11
ActiveSheet.Range("E57:E67").Select
Case Is = 12
ActiveSheet.Range("E82:E94").Select
Case Is < 14
ActiveSheet.Range("E57:E67").Select
Case Else
ActiveSheet.Range("E82:E94").Select
End Select

Next i

cheers
JulieD
 
AP,

Your statement
ElseIf 6 < i < 10 Then
Click to expand...
Works like this:
6<i evaluates to TRUE, so it's
TRUE<10, which evaluates to TRUE (since TRUE is 1). So the IF is satisfied,
and it performs the following statement.

Try
Elseif 6<i and i<10 then
 
ElseIf 13 < i < 14 Then
ActiveSheet.Range("E57:E67").Select
Click to expand...

In your code above, be advised that there is no integer that is greater than
13, and less than 14.
Here's a general idea similar to Julie's. I'm not sure how you want it set
up, but the idea is to group them if you can.

Dim i As Long
For i = 2 To WB.Worksheets.Count
Select Case i
Case 6, 10, 12, Is >= 14
ActiveSheet.Range("E82:E94").Select
Case Else
ActiveSheet.Range("E57:E67").Select
End Select
Next i
 
thanx a lot guys !

Dana DeLouis said:
In your code above, be advised that there is no integer that is greater than
13, and less than 14.
Here's a general idea similar to Julie's. I'm not sure how you want it set
up, but the idea is to group them if you can.

Dim i As Long
For i = 2 To WB.Worksheets.Count
Select Case i
Case 6, 10, 12, Is >= 14
ActiveSheet.Range("E82:E94").Select
Case Else
ActiveSheet.Range("E57:E67").Select
End Select
Next i
Click to expand...
 
Back
Top