Instr() function

Hello,

I have two columns that require dropdown lookup and I would like excel to tell me if the string is found in the dropdown.

here is the code:

Please Login or Register  to view this content.

No matter what values I select from the dropdown, col C won't give me anything. The instr() functions above will check to see if the string is found in the dropdown, correct?

Can anybody point me in the right direction?

Thank you

Hi Lifeseeker,

I downloaded your file, enabled macros and when I select options from the drop-downs in columns A/B on the MAIN worksheet, column C fills in accordingly. Are your Events or macros disabled prior to the triggering of the code? To make sure they're on, open a blank workbook and add the following code to a module. Run it, then open your workbook (enable macros if necessary) and try again.

Please Login or Register  to view this content.

You shouldn't have to run that code unless macros have been disabled via code previously.

I simply download your file, open it, and I am prompted to enable macros. I do so, then select a value in cell A5 and C5 is then populated. Nothing else to do, it just works. You may want to check that you don't have macros disabled without notification in Excel Options > Trust Center > Trust Center Settings... > Macro Settings. A safe setting is "Disable all macros with notification". That will prompt you with a message beneath the ribbon when you open the workbook to enable macros. If it's set to "Disable all macros without notification", no macros will run and you won't be prompted to enable them anyway.

Don't set it to "Enable all macros..." to prevent a malicious macro from running on your PC.

Add one line of code to your If InStr function to exit the For/Next loop if a match is found ("Yes"). Without exiting the For/Next loop, the value in the cell you just changed is then sequentially compared with the rest of the cells in the dropdowns column, almost certainly resulting in "No".

Please Login or Register  to view this content.