VBA: Search through array's

[BIGTONE559]

Greetings,

i'm looking for help on a efficient way of searching multiple arrays for data. i've come up with the following starting point however. I'm kinda lost as to where i should begin.

All help is appreciated

Dim d as integer 'Date Counter
Dim c as integer 'Color Counter
dim i as integer 'item counter
dim L as integer 'location counter

Range("A1:A500").name="MyData"

Dim MyDates as variant
Dim MyColor as variant
Dim MyItem as variant
Dim MyLocation as variant

MyDates=Range("MyDates")
MyColor=Range("MyColor")
MyItem=Range("MyItem")
MyLocation=Range("MyLocation")


for d=1 to ubound(MyDates)
    for c=1 to ubound(MyColor)
           for i=1 to ubound(MyItem)
                    for L=1 to ubound(MyLocation)

' i would like to sort through a large data set to find values that meet the following criteria.


'if "date" and "color" and "item" and "location" matches then blah blah

                  next L
               next i
             next c
           next d

 

[Marchut]

Missing some info so I made a lot assumptions. Maybe more information as to what is being used in the search and the relationship of the different arrays.

Dim i As Integer 'item counter


Range("A1:A500").Name = "MyData"

Dim MyDates As Variant
Dim MyColor As Variant
Dim MyItem As Variant
Dim MyLocation As Variant

MyDates = Range("MyDates")
MyColor = Range("MyColor")
MyItem = Range("MyItem")
MyLocation = Range("MyLocation")



For i = 1 To UBound(MyDates)
    If MyDates(i) = SOMEDATE And MyColor(i) = SOMECOLOR _
        And MyItem(i) = SOMEITEM And MyLocation(i) = SOMELOCATION Then
        ''MATCH FOUND
    End If
Next i

 

[BIGTONE559]

Missing some info so I made a lot assumptions. Maybe more information as to what is being used in the search and the relationship of the different arrays.

Dim i As Integer 'item counter


Range("A1:A500").Name = "MyData"

Dim MyDates As Variant
Dim MyColor As Variant
Dim MyItem As Variant
Dim MyLocation As Variant

MyDates = Range("MyDates")
MyColor = Range("MyColor")
MyItem = Range("MyItem")
MyLocation = Range("MyLocation")



For i = 1 To UBound(MyDates)
    If MyDates(i) = SOMEDATE And MyColor(i) = SOMECOLOR _
        And MyItem(i) = SOMEITEM And MyLocation(i) = SOMELOCATION Then
        ''MATCH FOUND
    End If
Next i

How will i handle the other arrays? those are dynamic arrays that will need to be incremented as well.