Find...

Wil Moosa

Well-known Member
Joined
Aug 11, 2002
Messages
893
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?
 
If cells(14,3) <> Niet ( OR (cells(14,4) = vbnullstring ) And D7=Who) then show sheet.

Cool you stick with me. I'm about quitting! :oops:
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
If C14 = Niet, don't show sheet regardless of anything else
If C14 <> Niet then
if D14= vbnullstring AND D7=Who then show sheet.
This is what I understand you are trying to do.

Code:
Sub findit()
    Dim What, When, Who As String
    Dim sht As Worksheet
    Dim Found As Range
    Dim FirstAddress As String
    Dim Response

    What = InputBox("Vul hieronder de naam van de werkeenheid/afdeling in waarvoor je vervang nodig hebt:")
    If What = "" Then Exit Sub
    When = InputBox("Vul hier de dag van de maand in (enkel het nummer van de dag) waarvoor je vervang zoekt:")
    If When = "" Then Exit Sub
    Who = InputBox("Vul hier de functie in die benodigd is")
    If Who = "" Then Exit Sub

    For Each sht In Worksheets
        sht.Activate

        If UCase(Cells(When + 14, 3)) <> "NIET" Then
            If UCase(Cells(When + 14, 4)) <> vbNullString Then
                If UCase(Cells(4, 7)) = Who Then  
              End If
           End If
            Set Found = sht.Range("D6").Find(What, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
            If Not Found Is Nothing Then
                FirstAddress = Found.Address
                Do
                    Found.Activate
                    Response = MsgBox("Doorgaan?", vbYesNo + vbQuestion)
                    If Response = vbNo Then Exit Sub
                    Set Found = Cells.FindNext(After:=ActiveCell)
                    If Found.Address = FirstAddress Then Exit Do
                Loop
            End If
        End If
    Next sht
    MsgBox "Het zoeken is beëindigd!"
End Sub



That should accomplish my understanding of the task.

I am not sure what the line

Set Found = sht.Range("D6").Find(What, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

is supposed to be doing.

Hope we're on the right track this time.
 
Upvote 0
TryingToLearn

When the mountain does not come to Mohammed… :( Mohammed needs to come to the mountain :cool: And after a two day break I finally got the breakthrough… :oops:

Somehow… I did not get the code fixed in such a way that it did what it was suppose to do. Unintented you gave me a clue that finally led to a working macro.

In the message from 24 Jan 2004 16:29 you wrote out in formula what you thought I was trying to achieve. That helped me to think in formula instead of code.

I created formula in 31 cells of each sheet similar to the following: =IF(AND(C3=A1,C15<>"NOT",C15<>"SICK",D15=""),"Match","NoMatch"). The formula where put in place on all sheets by a macro.

The vba-code for the search macro finally looked like this:

Sub Zoeken()
Dim What, When, Who As String
Dim sht As Worksheet
Dim Found As Range
Dim FirstAddress As String
Dim Response
What = InputBox("Vul hieronder de naam van de werkeenheid/afdeling in waarvoor je vervang nodig hebt:")
If What = "" Then Exit Sub
When = InputBox("Vul hier de dag van de maand in (enkel het nummer van de dag) waarvoor je vervang zoekt:")
If When = "" Then Exit Sub
Who = InputBox("Vul hier de functie in die benodigd is")
If Who = "" Then Exit Sub
Range("Toetsen!A200") = Who
For Each sht In Worksheets
sht.Activate
If UCase(Cells(When + 1, 1)) = "MATCH" Then
Set Found = sht.Cells.Find(What)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
Found.Activate
Response = MsgBox("Doorgaan met zoeken?", vbYesNo)
If Response = vbNo Then Exit Sub
Set Found = Cells.FindNext(After:=ActiveCell)
On Error Resume Next

If Found.Address = FirstAddress Then Exit Do
Loop

End If
End If
Next sht
MsgBox "Zoeken afgerond!"
End Sub

When lookin for the string What ánd the word "MATCH" I put into use the problem that only one If Ucase line was executed.

You mentioned that you did not know what the line Set Found = sht.Range("D6").Find(What, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) meant. Well, the idea was to narrow down the search to one specific cell. This worked as long as it was indeed one cell. In the proces however D6 was merged with some other cells and the code –even after some changes- did not work appropriate anymore. Changing the line back to Set Found = sht.Cells.Find(What) AND adding the line On Error Resume Next finally gave a working code-formula combination. I guess there is another way instead of the On Error Resume Next-line but for the moment it works well enough.

TryingToLearn, (y) thanks for the help and ideas. I would not have managed without your help.
 
Upvote 0
Glad it worked out. I learned some new stuff along the way. And I'm sure you would have gotten it, after all, where there's a Wil, theres a way!

(y)
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top