Find...

Wil Moosa

Well-known Member
Joined
Aug 11, 2002
Messages
882
Well, not really a new topic but... what is the quickest way to look for specific text in ONE SPECIFIC CELL ON 100 sheets in ONE WORKBOOK...

I want to check cell B5 in all 100 sheets of the appearance of "Dog" as a single word but also as part of a phrase...

An inputbox so I can look for more than one word is appreciated...

Suggestions?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
Hoi!

A suggestion! Saved this a while ago but don't know who wrote it so no credit to me.

Code:
Sub SearchWorkbook()
    Dim What As String
    Dim sht As Worksheet
    Dim Found As Range
    Dim FirstAddress As String
    Dim Response As Boolean
    What = InputBox("Search for :")
    If What = "" Then Exit Sub
    For Each sht In Worksheets
        sht.Activate
        Set Found = sht.Cells.Find(What)
        If Not Found Is Nothing Then
            FirstAddress = Found.Address
            Do
                Found.Activate
                Response = MsgBox("Continue?", vbYesNo + vbQuestion)
                If Response = vbNo Then Exit Sub
                Set Found = Cells.FindNext(After:=ActiveCell)
                If Found.Address = FirstAddress Then Exit Do
            Loop
        End If
    Next sht
    MsgBox "Search Ended!"
End Sub

Will search the entire workbook though rather than just one cell in particular

HTH
 

Wil Moosa

Well-known Member
Joined
Aug 11, 2002
Messages
882
I know this one. As I am well informed it comes from John Walkenbach.

I have been fiddling around with it the last two nights but I do not get it doing what I want. The code looks for a word in all cells in all sheets... and I want it to look in one specific cell on each worksheet. I do think this code is the basis for the code I'm looking for but I do not get it adjusted the way I want. So I desperately need help!
 

Wil Moosa

Well-known Member
Joined
Aug 11, 2002
Messages
882
I guess I'm asking the impossible. Working around the problem might be an option...

Cell A1, B2 and C3 contain text data. I want to use the above code to find all the sheets that meet the criteria in the inputbox. Sometimes this is only one word, sometimes it are three... The code above only looks in single cells with at least all the critetia. But the criteria are spread over three cells.

To work around the problem I have created a cell (E1) with the following code: =(A1&""&B2&""&C3). The code SHOWS the total text of all three the cells. So far so good.

When I run the above search code and enter the text of all three the cells in the inputbox... it does not find the sheet where all the text is in one cell. It might be that the code looks for the text while the cell contains formula. Propably the cell needs to be formatted... but in what way?

Did I manage to create another unsolvable problem :oops: or do I getting closer..? :p
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

=COUNTIF.3D(First:Last!B5,"*Dog*")

if you can install the morefunc.xll add-in.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

Wil Moosa said:
Aladin, can you explain what it does?

Counts the occurrences of dog and dog as part of a string like dogged. First and Last are empy workshets in between which you are supposed to have those 100 worksheets.
 

Wil Moosa

Well-known Member
Joined
Aug 11, 2002
Messages
882
I installed the addin and I was able to translate the code into Dutch. It indeed counts the number of times the word occurs but... in what way brings this me closer to an answer to the question of mine??? :eek:
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
Try this:

Code:
Sub SearchWorkbook()
    Dim What, Where As String
    Dim sht As Worksheet
    Dim Found As Range
    Dim FirstAddress As String
    Dim Response As Boolean
    What = InputBox("Search for :")
    Where = InputBox("Cell to search:  i.e. B5")
    If What = "" Then Exit Sub
    If Where = "" Then Exit Sub
    For Each sht In Worksheets
        sht.Activate
        Set Found = sht.Range(Where).Find(What, LookAt:=xlPart)
        If Not Found Is Nothing Then
            FirstAddress = Found.Address
            Do
                Found.Activate
                Response = MsgBox("Continue?", vbYesNo + vbQuestion)
                If Response = vbNo Then Exit Sub
                Set Found = Cells.FindNext(After:=ActiveCell)
                If Found.Address = FirstAddress Then Exit Do
            Loop
        End If
    Next sht
    MsgBox "Search Ended!"
End Sub
 

Wil Moosa

Well-known Member
Joined
Aug 11, 2002
Messages
882
This looks and works more than great. I customised the code a bit. I set a fixed cell to look in and I added LookIn:=xlValues. The latter for looking not only text but also on text as a result from a formula. The new code is as follows...

Sub SearchWorkbook()
Dim What, Where As String
Dim sht As Worksheet
Dim Found As Range
Dim FirstAddress As String
Dim Response As Boolean
What = InputBox("Search for :")
If What = "" Then Exit Sub
For Each sht In Worksheets
sht.Activate
Set Found = sht.Range("A1").Find(What, LookIn:=xlValues, LookAt:=xlPart)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
Found.Activate
Response = MsgBox("Continue?", vbYesNo + vbQuestion)
If Response = vbNo Then Exit Sub
Set Found = Cells.FindNext(After:=ActiveCell)
If Found.Address = FirstAddress Then Exit Do
Loop
End If
Next sht
MsgBox "Search Ended!"
End Sub

I want to add some IF statements and do not really know where to place them. The idea is that an inputbox is added that allows me to fill in a number up to 31 (days). When 1 is added it should look first if cell C15 <>"Not" AND if Cell D15 <>"X". If this is not true it should look in the next sheet. When 2 is added it should look first if cell C16 <>"Not" AND if Cell D16 <>"X" and again if this is not true it should look in the next sheet. Where in the code I make adjustments?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,493
Messages
5,764,698
Members
425,231
Latest member
mramb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top