PDA

View Full Version : Solved: Search all worksheets



RonNCmale
04-14-2010, 11:44 AM
The following code was provided by lucas. It works great for Sheet1 but, could this be modified to search all worksheets?


Sub GoToFnd()
Dim c As Range, FirstFound As String
Dim FindString As String
FindString = InputBox("Enter English Search term:")
Set c = Range("A:A").Find(what:=FindString, after:=[A1])
FirstFound = c.Address
c.Activate
Do Until MsgBox("Look for next instance of: " & FindString, vbYesNo) = vbNo
Set c = Range("A:A").Find(what:=FindString, after:=c)
If c.Address = FirstFound Then
MsgBox "No more " & FindString
Exit Sub
End If
c.Activate
Loop
' Range("A1").Activate
End Sub

austenr
04-14-2010, 12:03 PM
You will need a loop to do it. For Next is probably the easiest.

Pinokkio
04-14-2010, 01:23 PM
Try this one:


Sub ZoekProjectNr()
Dim wsSheet As Worksheet
Dim rFound As Range
Dim strFind As String
strFind = InputBox(Prompt:="Typ het ProjectNr. in.")
If strFind = "" Then Exit Sub
For Each wsSheet In ThisWorkbook.Worksheets
Set rFound = wsSheet.UsedRange. _
Find(What:=strFind, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not rFound Is Nothing Then
Application.Goto rFound
End
End If
Next wsSheet
MsgBox "Niks gevonden" 'Nothing find
Range("A1").Select
End Sub

lucas
04-14-2010, 01:47 PM
For column A on all sheets:

Option Explicit
Sub LineSearchTESTA123(Optional SearchVal As String)
Dim MyValue As String
Dim MyFindNext As Long
Dim sht As Worksheet
Dim Ans As Long
Dim FirstAddress As String
Dim Cel As Range
Dim Counter As Long
If SearchVal = "" Then
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
Else
MyValue = SearchVal
End If
If MyValue = "" Then
[C3].Select
Exit Sub
End If
' For Each sht In Sheets(Array("A", "B", "C"))
For Each sht In ThisWorkbook.Sheets
With sht
sht.Activate
Set Cel = .Columns(1).Find(What:=MyValue)
' Set Cel = ActiveSheet.UsedRange.Find(What:=MyValue)
MyFindNext = vbYes
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
Counter = Counter + 1
Cel.Activate
MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
' Set Cel = .UsedRange.FindNext(Cel)
Set Cel = .Columns(1).FindNext(Cel)
Loop While Not Cel Is Nothing And _
Cel.Address <> FirstAddress And _
MyFindNext = vbYes
End If
End With
If MyFindNext = vbNo Then
Exit Sub
End If
Next
If Counter = 0 Then
Ans = MsgBox("Search could not find '" & MyValue & "'." & _
vbNewLine & " " & vbNewLine & _
"Try another search?", 4, MyValue & " not found")
' If Ans = vbYes Then
' Call LineSearchTESTA123(MyValue)
' Else
' Exit Sub
' End If
' End If
' If MyFindNext = vbYes Then
' Call LineSearchTESTA123(MyValue)
End If
' End If
End Sub

There is a commented line that allows you to add just the sheets you want to search to an array if you don't want to search all sheets.

RonNCmale
04-14-2010, 02:44 PM
I attached my workbook that I'm using, Kept getting errors.

mdmackillop
04-14-2010, 03:25 PM
A different methodology

lucas
04-14-2010, 03:53 PM
I added the word general to column A in all the sheets and it finds them all ok now.

I changed the way the variable was defined which seems to be what was causing you problems.

ps. Just noticed Malcolms attachment. Haven't had a chance to look at it but it's probably more elegant than mine.

I'm uploading this one anyway for comparison.

mdmackillop
04-14-2010, 04:07 PM
This version will find a word plus any suffix

RonNCmale
04-14-2010, 04:15 PM
Lucas, Can you get it to search Column B also?

lucas
04-14-2010, 05:33 PM
Try this: see attached

Paul_Hossler
04-14-2010, 08:03 PM
mac --
That was elegant :beerchug:

Paul

RonNCmale
04-15-2010, 01:02 AM
mdmackillop, taking my hat off and bowing, nice work, I surely wasn't expecting that but thanks a bunch.
Thanks you too lucas your search works great, but I think I'll go with mdmackillop's method. Again thanks to both of you guys.

mdmackillop
04-15-2010, 03:41 AM
Just good timing, I was working on this at the weekend to assist with a bible search. It will seach all of that in about 5 seconds, so a couple of worksheets is very quick.

mdmackillop
04-19-2010, 03:28 PM
Updated method

RonNCmale
04-19-2010, 08:54 PM
Thanks mdmackillop, i'll give it a shot.