VBA match 4 conditional values at once with Select Case.

I am building a VBA search function across 3 product sheets (one at a time) for determining the next available size of packaging for a part (see attached example). Package chooser Rev5.xlsx


The search is based on 4 input criteria (length, width, height and mass) and returns the record containing the next largest dimensions across each of these values (being >= to the input values).

The combined part volume or aggregate measurements are not a decision criteria. The product sheets need to remain clean and be update-able by users.

I would like the output to be similar to this example https://www.youtube.com/watch?v=QOxhRSCfHaw ("Excel VBA Loop to Find Records Matching Search Criteria") where a matching row is copied and pasted to the search page.

I have setup a (non-VBA) drop-down combobox with a data validation list (Sheet Directory) for the the search page which should activate the chosen worksheet. (thus allowing users to add new product sheets without modifying VBA code).

I am unclear on the best way to manage this sheet activation in VBA and then the procedure for looping through the variables and only return records which meet all of the criteria at once. Any record containing any value not meeting the search condition would not be considered a match.

The inputs:

Length Width Height Mass
240, 33, 18, 160

Would match a row containing:

Length Width Height Mass
250.5, 35.40, 20, 187.44

But not be a match for:

Length Width Height Mass
250.5, 35.40, 20, 150



The procedure I have so far is possibly declaring a multidimensional array for the L,W,H,M column variables and creating a conditional operator of >= all "L,W,H,M" input values. Can I simplify things with a Select Case statement ? Or Select Case Is ?

Please Login or Register  to view this content.

Am I barking up the right tree?

Any help would be greatly appreciated!

Cross Posted Here:

http://www.mrexcel.com/forum/excel-q...lect-case.html

http://www.excelguru.ca/forums/showt...itional-values

http://www.vbaexpress.com/forum/show...th-Select-Case

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Cross posting.

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).