Find multiple values

[atroul]

Hello,
I have a workbook with multiple sheets. In the first sheet there are (among others) some values (~500) which I need to search if they exist and where in the other sheets.
In a few words I need the "Find" function for multiple values.

 

[Rick Rothstein]

I think that the algorithm I posted in my previous post make it easier to understand....
The values are in the sheets in the C column from 23 row to ~200 (the ending row its not the same in every sheet).

The result data can be in a new sheet.

See if this macro does what you want...

[table="width: 500"]
[tr]
	[td]Sub ListSheetsValuesAreOn()
  Dim X As Long, Data As Variant, Uniques As String, SH As Worksheet, NewSH As Worksheet
  With CreateObject("Scripting.Dictionary")
    For Each SH In Worksheets
      Data = Application.Transpose(SH.Range("C23", SH.Cells(Rows.Count, "C").End(xlUp)))
      For X = 1 To UBound(Data)
        If IsEmpty(.Item(Data(X))) Then
          .Item(Data(X)) = Data(X) & "|" & SH.Name
        ElseIf Data(X) = Split(.Item(Data(X)), "|")(0) And _
               Not .Item(Data(X)) Like "*|*" & SH.Name & "*" Then
          .Item(Data(X)) = .Item(Data(X)) & ", " & SH.Name
        End If
      Next
    Next
    Sheets.Add After:=Sheets(Sheets.Count)
    Set NewSH = ActiveSheet
    NewSH.Range("A1").Resize(.Count) = Application.Transpose(.Items)
  End With
  NewSH.Name = "Result Sheet"
  NewSH.Columns("A").TextToColumns , xlDelimited, , , 0, 0, 0, 0, 1, "|"
  NewSH.Columns("A:B").AutoFit
End Sub[/td]
[/tr]
[/table]

 

[atroul]

Super!!! Thank you! Works as expected