VBA match 4 conditional values at once with Select Case.

[digibay]

Hi,

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). 14624


The search is based on 4 input criteria (length, width, height and mass) and returns the record containing all of 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 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.4



But not be a match for:



Length
Width
Height
Mass


250.5
35.40
20
150






The rough 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!

[SamT]

Why do I get the feeling that those product tables are greatly truncated Column wise?


Instead of "SheetDirectory," use "CrossIndex." Set it up with all three tables in the first five columns like so


Length

Width
Height
Mass
Package ID



Then sort the entire table sort L+W+H+M

In VBA, Assign the Table to an Array varible (Ex: PackageSpecs or arrCrossIndex)


For i = 0 to UBound(PackageSpecs)
If PackageSpecs(1, i) > L Then
For i = i to UBound(PackageSpecs)
If PackageSpecs(2, i) >W Then
For i = i to UBound(PackageSpecs)
If PackageSpecs(3, i) > H Then
For i = i to UBound(PackageSpecs)
If PackageSpecs(4, i) > M Then
PackageID = PackageSpecs(5, i)
GoTo Finished
End If
Next i
'End all loops and Ifs
Finished:

[mancubus]

using http://www.snb-vba.eu/VBA_Arrays_en.html - 6.5.2.1 Excel function Index
and using a helper worksheet (i named it as 'Consolidated') which holds the all products' data



Sub vbax_54060_return_row_with_5_conditions()

Dim a_sn, a_sp, cat, L#, W#, H#, M#

With Worksheets("Search Example")
cat = .Range("B3").Value
L = .Range("B6").Value
W = .Range("C6").Value
H = .Range("D6").Value
M = .Range("E6").Value
End With

With Worksheets("Consolidated").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A2")
.SortFields.Add Key:=Range("C2")
.SortFields.Add Key:=Range("D2")
.SortFields.Add Key:=Range("E2")
.SortFields.Add Key:=Range("F2")

.SetRange Range("A1").CurrentRegion
.Header = xlYes
.Apply
End With

'from: http://www.snb-vba.eu/VBA_Arrays_en.html - 6.5.2.1 Excel function Index
a_sn = Worksheets("Consolidated").Cells(1).CurrentRegion.Value

For j = 1 To UBound(a_sn)
If a_sn(j, 1) = cat And a_sn(j, 3) >= L And a_sn(j, 4) >= W And a_sn(j, 5) >= H And a_sn(j, 6) >= M Then c00 = c00 & "_" & j
Next

a_sp = Application.Index(a_sn, Application.Transpose(Split(Mid(c00, 2), "_")), Array(2, 3, 4, 5, 6))

Worksheets("Search Example").Range("A12").Resize(, UBound(a_sp)) = a_sp

End Sub

[SamT]

Excellent!

[mancubus]

Thanks SamT.

credits go to snb :)

[digibay]

Thanks everyone for your awesome responses!

My apologies first for cross posting this question (lesson learned):

mrexcel.com/forum/excel-questions/896052-visual-basic-applications-match-4-conditional-values-once-select-case.html
excelforum.com/excel-programming-vba-macros/1110146-vba-match-4-conditional-values-at-once-with-select-case.html
excelguru.ca/forums/showthread.php?5045-Select-Case-Loop-across-4-conditional-values


The solution from Skywriter at excelforum.com/excel-programming-vba-macros/1110146-vba-match-4-conditional-values-at-once-with-select-case.html#post4224114
is exactly what I need as the product categories need to remain on separate sheets and be individually searchable, consolidating the data to one tab is not an option.

See attached current version. 14635




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

[SamT]

consolidating the data to one tab is not an option. Please don't tell us that you have reached the memory limit on the number of sheets in a workbook. Other wise, adding an extra sheet is always an option, even if you have to hide the sheet.