dj44
03-04-2023, 08:09 AM
Hi folks,
I am trying to match a set of items
For each fruit in the array if the fruit has 2 ids its a matching set.
Eg in this range > if Apple has both "SS" and "PP" then this is a set.
I have tried a number of things and my logic is not working.
Sub Set_Patterns()
Dim i As Long
Dim d As Range
Dim Source As Worksheet
Dim fruit As Variant
Set Source = ActiveWorkbook.Worksheets("Test_Data")
fruit = Array("Apple", "Banana", "Plum")
For i = 0 To UBound(fruit)
For Each d In Source.Range("A2:A10")
' I did not know how to create another AND condition
If d = fruit(i) And d.Offset(0, 1) = "SS" And "PP" Then
d.Interior.ColorIndex = 3 'red
d.Offset(0, 1).Interior.ColorIndex = 3 'red
End If
' Maybe an inner loop through the rows again? to identify the second ID
Next d
Next i
30592
My goal is to match ONLY the sets.
A fruit must match 2 conditions - SS & PP
As you can see only Apple and Banana matched the 2 conditions - Apple has SS & PP, Banana has SS & PP.
I would like to get the names of the fruits that have double conditions met.
I will then put those into an array called matching_fruit_sets = array ("Apple", "Banana")
Do i have to loop through the rows twice?
please do take a look and thank you
I am trying to match a set of items
For each fruit in the array if the fruit has 2 ids its a matching set.
Eg in this range > if Apple has both "SS" and "PP" then this is a set.
I have tried a number of things and my logic is not working.
Sub Set_Patterns()
Dim i As Long
Dim d As Range
Dim Source As Worksheet
Dim fruit As Variant
Set Source = ActiveWorkbook.Worksheets("Test_Data")
fruit = Array("Apple", "Banana", "Plum")
For i = 0 To UBound(fruit)
For Each d In Source.Range("A2:A10")
' I did not know how to create another AND condition
If d = fruit(i) And d.Offset(0, 1) = "SS" And "PP" Then
d.Interior.ColorIndex = 3 'red
d.Offset(0, 1).Interior.ColorIndex = 3 'red
End If
' Maybe an inner loop through the rows again? to identify the second ID
Next d
Next i
30592
My goal is to match ONLY the sets.
A fruit must match 2 conditions - SS & PP
As you can see only Apple and Banana matched the 2 conditions - Apple has SS & PP, Banana has SS & PP.
I would like to get the names of the fruits that have double conditions met.
I will then put those into an array called matching_fruit_sets = array ("Apple", "Banana")
Do i have to loop through the rows twice?
please do take a look and thank you