Hi I'm trying to figure out a way to make this code execute my range search. Im intrested to know if my Range ( S:column) contains Banana1 or Orange2 or if the range contains both Banana1 or Orange 2. Furthermore if Banana1 or Orange2 is found I store the row number which contains Banana1 or Orange2 ( in S198 or S230 for example. Finally I also mark each case if true x=1 if range contain Banana1, x2=2 if range contain Orange2 and x3 if range contains both.

Well....It doesnt work and I would like some guidance.

Grateful for any help!

Kind regards,




Sub SearchProducts()
Dim RowNumber As Long
Dim RowNumber2 As Long
Dim RowNumber3 As Long
Dim RowNumber4 As Long
Dim rFind As Range
Dim rFind2 As Range
Dim rFind3 As Range
Dim rFind4 As Range
Dim lRW As Long
Dim rng As Range

Application.AutoCorrect.AutoFillFormulasInLists = False


lRW = Worksheets("Fruits").Cells(Rows.Count, 1).End(xlUp).Row
     
  Set rng = Worksheets("Fruits").Range("S1", "S" & lRW).Selection
     
     For Each cell In Selection
     
     If cell.Value = "Banana1" Then
     
        With Worksheets("Fruits").Range("S1", "S" & lRW)
         Set rFind = .Find(What:="Banana1", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
         RowNumber = rFind.Row
         x = 1
        End With
     
     ElseIf cell.Value = "Orange2" Then
        
        With Worksheets("Fruits").Range("S1", "S" & lRW)
         Set rFind2 = .Find(What:="Orange2", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
         RowNumber2 = rFind2.Row
         x2 = 2
        End With
    
    ElseIf cell.Value = "Banana1" And "Orange2" Then
        With Worksheets("Fruits").Range("S1", "S" & lRW)
         Set rFind3 = .Find(What:="Banana1", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
         RowNumber3 = rFind3.Row
         Set rFind4 = .Find(What:="Orange2"", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
         RowNumber4 = rFind4.Row
         x3 = 3
        End With
    Else
      
    End If
Next cell