Find the "False" words in the worksheet

[Ann_BBO]

I write the below vba command to find the "False" word in the worksheet in the Range(D42:N1911).
[VBA]Sub FindFail()

Cells.Find(What:="Fail", After:=Range("d42"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
End Sub[/VBA]
However, i only find the "False" word once. (i.e range(d43) has the false word first time). Another "False" word cannot find it.

Moreover, can i create the dynamic button to run the marco which can refer the range step-down.

Thanks

[Bob Phillips]

Look at FindNext in VBA help.

[YellowLabPro]

What is it you want to do after you find the next occurrence of "False"?
What do you mean by "dynamic button" and range step down?

[Ann_BBO]

Ref to YellowLabPro. i want to do after i find the next occurenece of "False"
Ref to FindNext method reference, i try to modify the vba command as below:
[VBA]With Range("d42:d1711")
Set c = .Find("Fail", LookIn:=xlFormulas)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.SmallScroll Down:=0
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With[/VBA]

But it still not work to find the "False" word. Also the dynamic button means that the floating match botton. For example, if the marco find the Range "d1100" has the False word, then the marco button will move down the corresponding range say around Range"H1060" area.

Thanks

[Ann_BBO]

Now, i modify the program again as shown below:
[VBA]Sub FindFail()
Dim B As Range
Dim SheetNum As Integer
SheetNum = 1
Do While SheetNum <= Sheets.Count

Set B = Sheets(SheetNum).Cells.Find("Fail", Range("d41"), SearchOrder:=xlByColumns, LookIn:=xlValues)
If Not B Is Nothing Then
B.Activate
FindAddress = B.Address
Do
Set B = Sheets(SheetNumb.Cells.FindNext(B)
Loop While Not B Is Nothing And B.Address <> FindAddress
End If
SheetNumb= SheetNum+ 1
Loop
End Sub[/VBA]
But it still only find the 1st "Fail" word. What problem is occur here

Thanks

[Bob Phillips]

If you want to find False, you have to use False, not Fail.

[Ann_BBO]

As my english is poor, i want to restate my action again.
In the worksheet, it may have the "Fail" word in the cells. Now, i need write the marco to find out the "Fail" word. The action are:
1st press the marco button to move the 1st "Fail" word in corrsponding cell
2nd press the marco button to move the 2nd "Fail" word
3rd press the marco butto to move the 3rd "Fail" word
.
.
Now, i only can find the 1st "Fail" word even i press the marco button many times
The second problem is if we find "Fail" word in the very bottom side, but the marco button still in the upper side. If i want to find the next "Fail" word, it only press the shortkey to find out. Therefore, i want to find the "Fail" word that the marco button can also move down.

Thanks

[Bob Phillips]

Both of those bits of code find all occurrences of Find, or at least they both do when you correc t the second because sometimes you use the variable Sheetnum, and sometimes Sheetnumb (you don't use Option Explicit do you?)

[Ann_BBO]

Is Fail, not False. Typing error

Thanks

[rory]

Within your FindNext loop, you never actually do anything with the found cells (B):
[VBA] Do
Set B = Sheets(SheetNumb.Cells.FindNext(B)
Loop While Not B Is Nothing And B.Address <> FindAddress
[/VBA]
but anyway, that won't help if you want to find the next occurrence each time you click the button. You would be better off changing the search range each time.

May I ask why are you going to all this trouble to reproduce a function that is built in to Excel? Seems like a huge waste of effort to me.

Regards,
Rory

[Ann_BBO]

To xld, Sheetnum and Sheetnumb also typing error. It is Sheetnum
To rory, the above action is my boss requirement.(Boss always True )

[rory]

You need to train your boss better!
I think you would be better off doing this with a userform so that you can simply select the relevant cells in each given sheet without having to worry about moving a button around from cell to cell and sheet to sheet. (Did you actually want one button to search all sheets?)
Rory

[Ann_BBO]

Moving button is also my boss requirement. Actually, i want to know that is it possible to do this action??

[rory]

Yes, it's possible, but a userform would achieve the same effect and be, IMHO, easier to do and neater to implement. (Of course, saying "Press Ctrl+F, you muppet" is even easier, but I appreciate we can't all get away with that...)
A simple alternative would be to code the Find dialog for him?
Regards,
Rory

[Ann_BBO]

If ignore the moving buton problem.
How to modify my vba command which can be find the nth "Fail" word that i press the marco button nth times.

Thanks

[rory]

Do you want the code to search all worksheets, or just the activesheet? If all of them, do you want to start from the activesheet and loop back round from the last sheet to the first, or start from the first sheet?
Regards,
Rory

[Ann_BBO]

I suppose the search in the activesheet.
Thanks rory!!

Basically, i think it will very easy for me before. But now, it make me spend much time for this part.(Whole day!!!)

Cheer

[rory]

You could use something like this, which will loop through all the Fail cells in the whole workbook. You will need a separate macro to reset the search though (by setting the collection to Nothing):
[VBA]Dim colRanges As Collection
Sub FindFail2()
Static B As Range
Dim rngCheck As Range
Dim wks As Worksheet
Dim FindAddress As String
Static lngindex As Long
If colRanges Is Nothing Then Set colRanges = New Collection
If B Is Nothing Then
For Each wks In ActiveWorkbook.Worksheets
Set rngCheck = wks.Cells
Set B = rngCheck.Find("Fail", wks.Range("d41"), SearchOrder:=xlByColumns, LookIn:=xlValues)
If Not B Is Nothing Then
FindAddress = B.Address
Do
colRanges.Add B, B.Address(external:=True)
Set B = rngCheck.FindNext(B)
Loop While Not B Is Nothing And B.Address <> FindAddress
End If
Next wks
Set B = colRanges(lngindex + 1)
B.Activate
Else
With colRanges(lngindex + 1)
.Parent.Activate
.Activate
End With
End If
lngindex = (lngindex + 1) Mod colRanges.Count
End Sub
[/VBA]

Regards,
Rory

[Ann_BBO]

It works. But i don't fully understand "You will need a separate macro to reset the search though (by setting the collection to Nothing)"

Thank you
rory

[rory]

All I mean is that the code creates a collection of all the Fail cells the first time it is run, and then each subsequent time it just moves through the collection. If you run it once and then change a cell so that it contains "Fail", the code will not pick up this new cell unless you run some code to set the collection to Nothing. You just need a macro like:
Sub Reset()
Set colRanges = Nothing
End Sub

Regards,
Rory