Search Multiple Worksheets

[Bob]

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.

 

[FSt1]

hi
there is already a seard "model" in the built in find.
press cntl+F
press the options button.
in the within box, select workbook (sheet is default)
press find all.
the find function wil display a box at the bottom showing all the sheets and
address the serch criteria was found.

regards
FSt1

 

[JB]

http://boisgontierjacques.free.fr/fichiers/Matriciel/Mat3DAnswerMult.xls

=INDEX(INDIRECT("'"&INDEX(nf,Small(If((CountIf(INDIRECT("'"&nf&"'!
a2:a100"),$A$2)>0)=True,Row($1:$2)),Rows($1:2)))&"'!b2:b100"),Match($A
$2,INDIRECT("'"&
INDEX(nf,Small(If((CountIf(INDIRECT("'"&nf&"'!a2:a100");$A
$2)>0)=True,Row($1:$2)),Rows($1:2)))&"'!a2:a100"),0))
Valid with shift+ctrl+enter

JB
http://boisgontierjacques.free.fr/

 

[Alejandro Medinilla elMedex]

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