vendredi 9 juillet 2021

if cell doesn't contain a word then display it in the end of a words range

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