Search Multiple Worksheets

B

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

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top