Find Just Doesn't Work....

[cicconmr]

Does any one else have this problem where your find routine works some times but not others....even when you test it by searching for the same thing? Is this a common error...I could not find anything on it.

This sheet is for other people to use and it really is sad when I get emails that the find doesn't work all the time.....

 

[Scott Huish]

xlPart is not a duct tape fix job. xlPart works when you want to search part of a value in a cell. xlWhole works when you want to match the entire contents of the cell.

I want to find, say T, using LookAt:= xlWhole

This is because some tickers for stocks have T's in them, but aren't ATT the company I want. However....even though I'm sitting here looking at letter T it skips to my Error Sub.

If you want ATT followed by a space search for that with xlPart if the cell contains other text with it.

 

[cicconmr]

Having the Problem again, there are no trailing spaces so I'm guessing hte problem is with my for loop and not my Find Method. Any thought?? This is really frustrating becuase there's no reason this should not work from what I can see.....

Search:
    Symbol = InputBox("Please Enter the company symbol WITHOUT spaces.")
    On Error GoTo Error
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
                Set Found = ws.Columns("A").Cells.Find(What:=Symbol, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
                    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                    False, SearchFormat:=False)
                If Not Found Is Nothing Then
                    Application.Goto Reference:=Found
                    Exit Sub
                End If
        End If
    Next ws

 

[jmthompson]

What if you remove "After:=ActiveCell," Perhaps the ActiveCell is not what you expect...

 

[cicconmr]

No Luck, it simply does not find anything, goes straight to my error handler, is my error handler in the wrong place?

 

[cicconmr]

Okay I think that must have been it, I moved the error handler to the bottom, before it went back through the loop, and now it works. Does that make sense? I don't know why it would have been registering an Error right after the InputBox?