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?
 
Wil Moosa said:
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...
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?


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("Day #:")

    If What = "" Then Exit Sub
    If Where= "" Then Exit Sub
    For Each sht In Worksheets
        sht.Activate

    If Cells(Where + 14, 3) = "Not" Then
    If Cells(Where + 14, 4) = "X" Then
    End If

        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
End If
    Next sht
    MsgBox "Search Ended!"
End Sub

I believe this is what you asked. If cells C15="NOT" and c16="x" it will skip to the next worksheet. If that is backwards, change the = to <>.

HTH
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
It took me a while to get it running but after figuring out a few things -I'm still learning too- I must admit that the code is very sleek. :devilish: sleek in such a way that is isn't the code only that's well thought of but also the idea behind is quite smart.

Thanks, you made life for me and my colleagues a bit easier. :biggrin:

Just one small question left. The "Not" and "X" seem to be case sensitive. As some coworkers might fill in "not" and "x" instead... the macro does not work as intented. Adding "MatchCase:=False" to the search code has only effect to the word to be searched for and not on the IF-statement. Is there a way around?
 
Upvote 0
Try this (notice the word Not was changed to NOT in the code line!)

Code:
    If UCase(Cells(Where + 14, 3)) = "NOT" Then 
    If UCase(Cells(Where + 14, 4)) = "X" Then
 
Upvote 0
I try this out....

I might be overdoing my questioning but…

The code so far is intented for use in a nursing home. When there is not enough regular staff we use workers who can be contracted on a non regular base. The code so far allows us to look for available non regular staff in a workbook. Every non regular worker has his or her own sheet with information. A1 contains the name of the work unit while the further code refers to a list with availability (Not) and already placed (X) staff. I want to add also a function name (we have nurses, food assistents etc.) as a criteria. Is it possible to add a extra criteria (like the Not and X)? This criteria normally can be found in cell d7.


Again, sorry I keep bothering you…
 
Upvote 0
Wil:

Bother? When an opportunity to think is a bother, I'm in trouble :wink:

The answer to your question is yes, you can add another parameter by adding another input box asking for the additional critera and another if statement qualifying the entry (just like we added the 2nd one). Try coding it yourself and if you have a problem, post the code and we'll get it working.

Personally, with the knowlege I've gained here lately, if I have realized the scope of what you were trying to do, I would have suggested a userform to enter all the critera on one form to start. To accomplish this you would just need to insert a userform with 3 textboxes (and labels), ok & cancel buttons and link the already built code to the OK button. The only change to the code would be linking the textbox.text to WHAT, WHERE and the new WHO instead of the input box values. Not nearly as intimidating as it sounds you've done it once!
 
Upvote 0
Good approach… (y) As to get the code and the idea right I like to stick first to the inputbox-code. I will give the –more prefered- userform idea some thoughts later on.

As to the code looked for… I was thinking of something like this.
Sub SearchWorkbook()

Dim What, When, Who As String
Dim sht As Worksheet
Dim Found As Range
Dim FirstAddress As String
Dim Response As Boolean

What = InputBox("Vul hieronder de naam van de werkeenheid/afdeling in waarvoor je vervang nodig hebt:")
When = InputBox("Vul hier de dag van de maand in (enkel het nummer van de dag) waarvoor je vervang zoekt:")
Who = InputBox("Vul hier de functie in die benodigd is")
If What = "" Then Exit Sub
If When = "" Then Exit Sub
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)) <> "X" Then
If Ucase(Cells(4, 7))= Who

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

I tried a few things regarding the line > If Ucase(Cells(4, 7))= Who Then
< and it seems that the code regarding the Who-input is ignored whatever I try. Another thing is that as soon as I add another if statement I get a message “Next without for”. I could solve this by adding another “end If” but I’m not sure if that’s right.

Finally the line > If Response = vbNo Then Exit Sub< does not seem to work and could be a language conflict???!!!
 
Upvote 0
Try this:

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)) <> "X" Then
                If UCase(Cells(4, 7)) = Who Then  ' missed the THEN on this statement
                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

tried a few things regarding the line > If Ucase(Cells(4, 7))= Who Then
< and it seems that the code regarding the Who-input is ignored whatever I try.

there was no THEN in the line

Another thing is that as soon as I add another if statement I get a message “Next without for”. I could solve this by adding another “end If” but I’m not sure if that’s right.

It is correct, you need an End If for every If, but you need to be careful where you put it in relationship to the NEXT

finally the line > If Response = vbNo Then Exit Sub< does not seem to work and could be a language conflict???!!!

it was DIM as Boolean which is TRUE or FALSE but testing for vbyes or vbno is not boolean.

HTH
 
Upvote 0
Sorry I missed the "Then" on the forum. In fact I did not miss it in the trials I was running... still it seems that I do something wrong somewhere.

I reran the previous code with only the two If UCase(Cells(When... lines. I did not notice before but just like in the most recent code where the third line is ignored the second line is ignored in the two liner too.

By checking, rechecking and rerechecking the first If UCase line seems to be the dominant one. No further IF statements after the first IF statement are executed. Does this make sense? :confused: And what can "If And" and "If Or" statements do here?

Just to make sure where I’m after:
*If cell When +14, 3 is not equal to “Niet”,
*And/Or cell When +14, 4 is not equal to “X” (better is is equal to an empty cell!)
*And cell D7 is equal to the input in the Who-inputbox
*THEN the page should come to a halt. In all other cases where there is no match the page should be skipped.
 
Upvote 0
Wil Moosa said:
Just to make sure where I’m after:
*If cell When +14, 3 is not equal to “Niet”,
*And/Or cell When +14, 4 is not equal to “X” (better is is equal to an empty cell!)
*And cell D7 is equal to the input in the Who-inputbox
*THEN the page should come to a halt. In all other cases where there is no match the page should be skipped.


Sorry Wil, not sure I've understood what you're after then. Does this look like what you're after?

If cells(14,3) <> Niet ( AND (cells(14,4) = vbnullstring OR cells(14,4) = X) And D7=Who). then stop
 
Upvote 0
*And/Or cell When +14, 4 is not equal to “X” (better is is equal to an empty cell!)

Or does this mean that ANY charcter in this cell means to skip the sheet.

*If cell When +14, 3 is not equal to “Niet”,

Can there be any other value in this cell?
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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