VBA Find Lookat and Lookin as variables

The alternative is to store the literal values of the xlFormulas and xlValues constants (-4123 and -4163 respectively) and use those instead of the text.

interesting! first time I've ever heard this question asked before, on any board. and I have accounts on 20 of them. =) here is your answer, Imbizile. and thanks for asking! very intuitive.

this code of yours:

Please Login or Register  to view this content.

works because you have put what are called enumerations into function arguments. the compiler (the machine) reads those as 2 bit pieces of information, generally. But they can take other values depending on how any given program is written. see this for a relevant example of an enumeration that is important to you regarding this topic:

https://docs.microsoft.com/en-us/off...l.xlfindlookin

most functions and methods in every language take enums as arguments. especially those that are not complex inputs. complex inputs tend to be required in the form of variables because the operation conducted ON them has to be complex. the most basic example of an enum, although it is technically not one, is the boolean data type. In VBA, the values for true and false are 0 and -1. or, sometimes it may even be 0 and 1. Not sure.

so, an example as to why you're not getting certain things to work, this code of yours:

Please Login or Register  to view this content.

works fine because LookInMode is a variant, and thus can hold any data type. xlValues is not a string. It is a number. specifically, it is -4163. the humans read alpha characters, not numbers. that's why Microsoft tells you to write English instead of numbers. the compiler takes care of the rest. so in the above code example, you are actually telling the compiler this:

Please Login or Register  to view this content.

and MS says that is acceptable: https://docs.microsoft.com/en-us/off...l.xlfindlookin. But, coincidentally, many other platforms that are much better in the way they are written would never see this sort of behavior by the developer and/or the environment as acceptable.

so the answer to your question is: no, you can't workaround it. but you can get what you want by other means. perhaps putting the actual numbers in the sheet, in a hidden way so people don't see them? then putting the actual words you want to see in the same place in the visual sense? then have your code read the hidden numbers. that makes good sense.

You Omitted xlComments in your the Data Validation for D3. That would look for "Test" in a note.

Please Login or Register  to view this content.