Try with this code

Sub FindValue()

Dim formu As String
Dim out As String
Dim str, str2 As String

For Sheet = 2 To 3
For Row = 1 To 3
str = Worksheets(Sheet).Cells(Row, 1).Value
str2 = Worksheets(Sheet).Cells(Row, 2).Value

formu = "=IFERROR(IF(FIND(UPPER(B2),UPPER(""" & str & """))0,""" &
str2 & """,""""),"""")"
Worksheets(1).Select
Worksheets(1).Cells(4, 2).Formula = formu

If (Len(Worksheets(1).Cells(4, 2).Value) 0) Then
out = out & Worksheets(1).Cells(4, 2).Value & vbLf
End If

Next
Next Sheet
Worksheets(1).Cells(4, 2).Value = out
End Sub

Regards


elMedex

"Bob" wrote:

Search several worksheets.

This what I would like to do, (so any thought and suggestions greatly
accepted)

In a workbook I have 3 worksheets

Sheet2,
Column A
R1 Lemon,
R2 Apples,
R3 Pears,
Column B
R1 Ref_L
R2 Ref_A
R3 Ref_P

Sheet3,
Column A
R1 Grapes,
R2 Lemon,
R3 Nuts,
Column B
R1 Ref_G
R2 Ref_L
R3 Ref_N

On sheet1 I want to put a search box and a button, and underneath that
a results box. So if I type in Ap* (or indeed the usual search
criteria) then it would search sheets 1 and 2 and list the following
in the results box

Sheet2 Apples Ref_A
Sheet3 Grapes Ref_G

If I then search again, the result box would clear and the new search
results appear, so a fresh search box each time, rather than have
results appended under each other.

It may be there is already a set €œmodel€� that will achieve the above,
if so perhaps you could point me in the right direction. Otherwise all
help and comments gratefully received.
.