Loops & IF operands

[Derek_35]

I am running a for loop from 2000-2616

For ID=2000 to 2616 Step 1

There are 116 #'s between 2000 & 2616 that I do not want to run the for loop.

Is there any way to do this other than having one long IF statement?

ie) If (ID<>2008)...

Also, what is the syntax for using the 'and' & 'or' operands in an IF statement? (VBA, not a cell =IF)

Thanks, Derek

[ This Message was edited by: Derek_35 on 2002-04-23 17:12 ]

[Jay Petrulis]

Is there any pattern to the excluded numbers?

[Derek_35]

Good question...

There is no pattern.

[John McGraw]

if there is no pattern I think you just have to spell it out. If there are "chunks" of numbers like 2000 to 2020, you could use a select case with the "case 2000 to 2020" specification. And the rest of the non-chunked number you can just use if's.

As for and & or. You just do "and" and "or". nothing special.

If num <> 1 And num <> 2 And num <> 3 And num <> 4 And num <> 4 Then

  MsgBox "ok buddy"

End If

[Derek_35]

Thanks for the replies. I couldnt find that simple IF operands question in any of my Excel VBA books and it is so simple.

I am so brainwashed in C++ its sickening...

[Jay Petrulis]

On 2002-04-23 17:07, Derek_35 wrote:
I am running a for loop from 2000-2616

For ID=2000 to 2616 Step 1

There are 116 #'s between 2000 & 2616 that I do not want to run the for loop.

Is there any way to do this other than having one long IF statement?

ie) If (ID<>2008)...

Also, what is the syntax for using the 'and' & 'or' operands in an IF statement? (VBA, not a cell =IF)

Thanks, Derek

[ This Message was edited by: Derek_35 on 2002-04-23 17:12 ]

Hi Derek,

1. Throw your list of *excluded* numbers into an array.

2. In your loop, match the ID value with the array of excluded numbers. If they match, do nothing. If there is an error (no match) run your code.

Here is an example...

--------------------
Sub tester()
Dim MyArr, ID, z

MyArr = Array(2001, 2404, 2050, 2597)

On Error Resume Next
For ID = 2000 To 2616
z = ""
z = WorksheetFunction.Match(ID, MyArr, 0)
If z = "" Then
'your code
End If
Next ID
On Error GoTo 0

End Sub
--------------------

If the excluded numbers are not static, then you can accomplish the same thing by reading them into the array at runtime and doing the above.

HTH,
Jay

[ This Message was edited by: Jay Petrulis on 2002-04-24 13:39 ]