Loops & IF operands

Derek_35

New Member
Joined
Apr 21, 2002
Messages
25
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
<pre/>

If num <> 1 And num <> 2 And num <> 3 And num <> 4 And num <> 4 Then
MsgBox "ok buddy"
End If
</pre>
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top