Search in all worksheets

[MPullen]

Help!
I would like a macro that prompts for a variable, and then search's across all worksheets within my workbook, and then selects and shows the found variable.

So far I got:
Worksheets.Select
testValue = InputBox("Enter Search Value : ")

But I don't know what the search/find command is!

I know I can select all worksheets and find a variable in Excel, but I'd like to automate it with a macro.

I'm new to VB and any help is much appricated.

[Ricky Morris]

On 2002-05-22 10:52, MPullen wrote:
Help!
I would like a macro that prompts for a variable, and then search's across all worksheets within my workbook, and then selects and shows the found variable.

So far I got:
Worksheets.Select
testValue = InputBox("Enter Search Value : ")

But I don't know what the search/find command is!

I know I can select all worksheets and find a variable in Excel, but I'd like to automate it with a macro.

I'm new to VB and any help is much appricated.

you can search all the sheets by doing:

Dim ws As Worksheet
For Each ws In Worksheets
ws.Select
Cells.Find(What:=testValue).Activate
Next

[NateO]

Bonjour MPullen.

See:

http://perso.wanadoo.fr/frederic.sig...nsClasseur.txt

See:

Sub RechercheMot()


Does exactly want you want, looping through the sheets selecting matches.

Bon Chance.

Edit: I noticed you were from Vancouver and figured the French wouldn't confuse the issue. If so, I apologize & please post back.
_________________
Cheers, NateO

[ This Message was edited by: nateo on 2002-05-22 11:26 ]

[Paul B]

This code,In English, came off this board, don't remember who to give credit to.

Sub FindAcrossAll()

Dim DoIt As Boolean

Dim What As String



DoIt = True

While DoIt

What = InputBox("What are you looking for?")

For Each Sht In Worksheets

Sht.Activate

Set Found = Sht.UsedRange.Find(What)

If Not Found Is Nothing Then ' The value has been found.

FirstAddress = Found.Address

Do

Found.Activate

Msg = "Continue the search ?"

Title = "Continue ?"

Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then ' Doesn't want to continue

MsgBox "Search cancelled by user."

Exit Sub ' Quit the macro

End If

Set Found = Cells.FindNext(After:=ActiveCell)

If Found.Address = FirstAddress Then Exit Do

Loop

End If

Next Sht

If Found Is Nothing Then ' Nothing found

Msg = "Not found! Do you want to start a new search?"

Style = vbYesNo + vbCritical + vbDefaultButton2

Else

Msg = "Search complete. Do you want to start a new search?"

Style = vbYesNo + vbDefaultButton2

End If

Title = "Search Complete"

Response = MsgBox(Msg, Style, Title)

If Response <> vbYes Then DoIt = False

Wend

MsgBox ("Search has ended.")

End Sub

[MPullen]

Thank you all for your help...
Works great!