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.

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


"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.
.

http://boisgontierjacques.free.fr/fi...AnswerMult.xls

=INDEX(INDIRECT("'"&INDEX(nf,Small(If((CountIf(IND IRECT("'"&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:a 100");$A
$2)0)=True,Row($1:$2)),Rows($1:2)))&"'!a2:a100"), 0))
Valid with shift+ctrl+enter

JB
http://boisgontierjacques.free.fr/


On 21 mar, 18:43, 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.

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.
.