I'd like to display bellow a range of datas specific words if they don't exist in this specific range, based on an array list.
1- For example.Let's say I've got an array list in which fruits name are located;
Fruits = Array("apple","cherry","banana","peach","pomegranate","mango")
2- And that I have the following column(A) in which each cells are filled by multiple fruits words...
| Fruits |
|---|
| cherry |
| cherry |
| banana |
| banana |
| banana |
| apple |
| apple |
The objective is to :
- display bellow the range of words (up above) every words from the array list that aren't in this column.
For instance "peach","pomegranate","mango" are not in this list so it the code must return the following value;
| Fruits |
|---|
| cherry |
| cherry |
| banana |
| banana |
| banana |
| apple |
| apple |
| peach |
| pomegranate |
| mango |
I tried to code it but as soon as I run my code every fruits are getting listed regardless if there are existing in the column or not.
Sub Fruties()
Dim LR1 , j, i ,fruits as long
Dim Good As Integer
Fruits = Array("apple","cherry","banana","peach","pomegranate","mango")
Good = 0
Worksheets("Fruit list").Activate
LR1 = Range("A" & rows.Count).End(xlUp).row
For i = 1 To LR1
With Range("A" & i)
For j = LBound(Fruits) To UBound(Fruits)
If .Value Like "*" & Fruits(j) & "*" Then
Good = Good + 1
Else
Range("A1").End(xlDown)).Offset(1).Select
Range("A20:A" & UBound(Fruits) + 1) _
= WorksheetFunction.Transpose(Fruits)
Exit For
End If
Next j
End With
Next i
I would heavily appreciate your help.
Aucun commentaire:
Enregistrer un commentaire