Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Loops & IF operands

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there any pattern to the excluded numbers?

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good question...

    There is no pattern.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •