InStr not working properly.

I have a variable (string) as below

DevelopmentStageSF1 - "Implementable, In Planning, Owned, Conditional/In Negotiation, Target"

I am using an 'if' query to determine which words are in this variable. However it is not really working.


Why does this pass the 'if' query.

If InStr(DevelopmentStageSF1, "Implementable") And InStr(DevelopmentStageSF1, "Owned") Then ...


And this does not...?

If InStr(DevelopmentStageSF1, "Implementable") And InStr(DevelopmentStageSF1, "Conditional") Then ...

Without my crystal ball, I fear I won't be able to help ...

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)


Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.


Which version of Excel are you using? If Excel 2016, then please make this clear in your profile. Thanks.

It appears to me that the Instr() functions are working just fine -- returning the appropriate position number for the in string search. The problem appears to be in the conversion from integer/long to boolean. From VBA's help file for the boolean data type

Originally Posted by VBA help

Boolean values are not stored as numbers, and the stored values are not intended to be equivalent to numbers. You should never write code that relies on equivalent numeric values for True and False. Whenever possible, you should restrict usage of Boolean variables to the logical values for which they are designed. -- https://docs.microsoft.com/en-us/dot...lean-data-type

The problem seems to be that the conversion from integer/long to boolean coupled with the And operator causes two non-zero integers to return a False boolean result (I couldn't find anything to explain why, but that seems to be the problem).

As suggested by the help file, the solution would be to not trust VBA's built in conversion from numeric integer/long to boolean and write the code so that the boolean test/conversion is explicit. I would expect something like

Please Login or Register  to view this content.

so that VBA cannot misinterpret the boolean logic you want.

And is a bitwise operator, so unless there are bits that are 1 in the same position in both numbers, you’ll end up with 0 as a result, and thus False when converted to Boolean