Looking for duplicates/FindAll

[meridius10]

I am looking to make amendments to the following code so that it will find multiple duplicate numbers, similar to FindAll, and then move from one number to the next.

So for example if the number '201' appears twice this is reported, or preferably moves in sequence down twice.

Sub findobjectnumber()
    On Error GoTo z
    Dim x As Integer
    Dim y As Range
    Set y = Range("A:A")
    Set w = Range("A2")
    w.Select
    x = InputBox("Enter the object number below")
    y.Find(What:=x, _
    After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
    Exit Sub
z:
    MsgBox ("Object number does not exist. Try again.")
End Sub

 

[venkat1926]

"preferably moves in sequence down twice.
explain with example

you can use "findnext" to get all the x(s)
see excel help
Pearson has also written a function for "findall"

http://www.cpearson.com/excel/FindAll.aspx

if problem post back

 

[meridius10]

OK - I have found a solution with help from our friend the macro recorder:

Sub findonumbtest()
Dim x As Integer
Selection.AutoFilter Field:=1
x = InputBox("Enter the object number below")
    Selection.AutoFilter Field:=1, Criteria1:=x, Operator:=xlAnd
End Sub

However I am getting Runtime error 1004, "You cannot use this command on an unprotected sheet".

My other code, which I initially wrote in Module1, still runs fine without this error message and with protection switched on. I must keep protection on this sheet. Is there a workaround?