VBA match 4 conditional values at once with Select Case.

[digibay]

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 (my attached example is available here VBA match 4 conditional values at once with Select Case. ).


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'm looking for the output to be similar to this example http://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

<tbody>
</tbody>

Would match a row containing:

Length	Width	Height	Mass
250.5	35.40	20	187.4

<tbody>
</tbody>

But not be a match for:

Length	Width	Height	Mass
250.5	35.40	20	150

<tbody>
</tbody>

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 ?




Sub findpackage()


Dim sheet As String


Range("B3").Value = ComboBox1
sheet = ComboBox1.Value
Worksheets(sheet).Activate






Dim length As Double
Dim height As Double
Dim width As Double
Dim mass As Double


(Declare Array)?


(Select Case Arguments)?




End Sub


Am I barking up the right tree?


Any help would be greatly appreciated!

 

[Jerry Sullivan]

This code in this thread which addresses a similar problem might help you get started...

http://www.mrexcel.com/forum/excel-...tion-across-multiple-sheets-return-match.html

 

[skywriter]

Cross posted.

 

[digibay]

Cross posted here:

http://http://www.excelforum.com/excel-prog...ml#post4222527

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


http://http://www.excelguru.ca/foru...-Select-Case-Loop-across-4-conditional-values


Code below for a working solution from Skywriter (preserving the simple non VBA dropdown list)

[COLOR=#333333]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Sub SearchPackagesByCategory()
Dim wsSE As Worksheet, rngInputs As Range, rngData, c As Long
Set wsSE = Worksheets("Search Example")
Set rngInputs = wsSE.Range("B6:E6")
     With Worksheets(wsSE.Range("B3").Value).Range("A1").CurrentRegion
        Set rngData = .Offset(1).Resize(.Rows.Count - 1, 5)
        For c = 1 To rngData.Rows.Count
           If rngData(c, 2).Value >= rngInputs(1, 1).Value And rngData(c, 3).Value >= rngInputs(1, 2).Value _
               And rngData(c, 4).Value >= rngInputs(1, 3).Value And rngData(c, 5).Value >= rngInputs(1, 4).Value Then
               wsSE.Cells(Rows.Count, "A").End(xlUp)(2).Resize(1, 5).Value = rngData.Rows(c).Value
           End If
        Next c
    End With
End Sub</code>[/COLOR]