I am writing a script that runs a Database search. It does so with an IF statement that bears several conditions, which are dependent on user input in three different fields ('country', 'category' and 'subcategory').
I have defined these previously.Different combinations of the input by the user in the three fields will produce different outcomes. As such, for instance, if the user does not provide a 'country', then the search will not run and an error message will pop up, as follows: For i = 2 To finalrowIf country = ' ThenSheets('Results').Range('B10:J200000').ClearMsgBox 'You must select a country in order to search the database. Please do so in the drop-down list provided.' Where is the code where you initiate country? Is seems that this is merely a Sting containing one country. If that's correct then you are pretty much allowing for no more than one country in your code: ElseIf Sheets('Database').Cells(i, 1) = country And.
So, Cells(i, 1) must contain exactly what is in country otherwise the If statement will not work (especially since it is logically combined with an AND). Hence, you will (should) always get the no such country error message.–Nov 18 '16 at 11:32. When you only have the two first If/ ElseIf blocks, the loop runs until it gets to a line where the second condition evaluates to True and executed the second block. All the other times nothing is executed.When you include the third block, the country will (most likely) not match in the first row so it will give you the message that the country is not in the database (although it actually only is not in the current row).I would search the country using.Find first and exiting if no match was found and then do the search. The ideal way would depend on your data (are there many expected matches etc)The same logic applies to the last block although this will only cause a problem once the country is found but with a different category.
It is probably the easiest to just do the search and check if there were any matches afterwards. If not, you can give the message.
Conditionally executes a group of statements, depending on the value of an expression. You can use the single-line syntax for a single condition with code to execute if it's true. However, the multiple-line syntax provides more structure and flexibility and is easier to read, maintain, and debug. Excel VBA IF THEN Statement is one of the most useful statements in VBA. In this tutorial, you’ll quickly learn how to work with the IF, THEN, ELSE, ELSEIF as well as the AND statements. This way you can write Excel macros that are dependent on multiple conditions.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |