MsOf365 [RESOLVED] If statement ignoring conditions-VBForums
Results 1 to 5 of 5

Thread: [RESOLVED] If statement ignoring conditions

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Resolved [RESOLVED] If statement ignoring conditions

    I have just realised that at least one of my If statements is ignoring its conditions.

    The statement is simplified as such:
    Code:
    If Array(i, 1) <> 1 Or 2 Then
    
        Number = Number + 1
    
    End if
    The issue is, even when Array(i, 1) = 1 or 2, Excel is still adding 1 to Number. I've tried changing the conditions, for example I've tried "If Array(i, 1) = 3 Or 4 Or 5 Then...", but this had no effect.

    The problem may stem from the fact that both the array and the Number variable are Doubles. However, I've stepped through the code and the values are as expected. If Array(i, 1) = 1, Excel still runs the code within the loop, even though the condition is false.

    Can anyone spot the issue?

    Cheers for your help again

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,251

    Re: If statement ignoring conditions

    The problem is that the OR doesn't apply the value to the previously mentioned variable, you need to write the variable out twice, eg:
    Code:
    If Array(i, 1) <> 1 Or Array(i, 1) <> 2 Then
    However, in this particular case you want to use AND instead (because if the first part isn't true, the second part will be - either way the overall result will be true):
    Code:
    If Array(i, 1) <> 1 And Array(i, 1) <> 2 Then

    Note that the explanation of what your original version actually did instead is complex, and is best to ignore.

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Re: If statement ignoring conditions

    I cannot believe this.

    My entire simulation stopped working as intended a few days back, because at some point I had started using Or rather than And in a number of the example given above. I've stressed for days wondering why I have 100 butterflies being born, but 150 dying, or none of my pupae are emerging, and it was this simple.

    Is there anything I can do to contribute to this site? Or to give individuals cheap karma?

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,251

    Re: If statement ignoring conditions

    using AND has the same problem, with just a slight variation... what was actually happening was bit-manipulation (find a tutorial if you want to be confused!), which meant that the numbers would be merged in odd (and 'unpredictable') ways, eg: "x <> 2 Or 3" would be the same as "x <> 5", but "x <> 2 And 3" would be the same as "x <> 0"

    Unfortunately you do need to take care, but at least you know what to do now

    Quote Originally Posted by AnUtterAmateur View Post
    Is there anything I can do to contribute to this site? Or to give individuals cheap karma?
    If you notice any adverts on this site that are interesting, give them a click (and if you have an ad-blocker, disable it for this site... we have very few adverts on each page, and no annoying ones).

    In terms of cheap karma, marking threads resolved is good (as you've already done ), and there is a "rate this post" link under posts, which you can click for posts you found particularly helpful (which adds to the reputation of the person who posted, which is indicated by the line of green squares under usernames). The system limits how often you can do it for each person, but most of us are just happy to know we've helped

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,877

    Re: [RESOLVED] If statement ignoring conditions

    if you do not have < 1 (zero or negative) values, you could use just if array(i, 1) >2

    if Array is the name of an array variable, you should change its name as it is also the name of an inbuilt function, which could lead to confusion, error or unexpected result
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.