Charles Chickering
12/14/2006 7:01:00 PM
I'm not sure why the error trapping is not working, however I can suggest a
better method. Dim a Range object and set that to the .Find method, then test
it to see if it is nothing. If you are using this in a loop be sure to set it
to nothing before to try to set it with the .Find method.
Sub TestFind
Dim rFind As Range
Set rFind = Nothing
Set rFind = Cells.Find(What:="1 total", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rFind Is Nothing Then 'Was Not Found
MsgBox "Not Found!"
Exit Sub
Else
'Do your code
End If
End Sub
--
Charles Chickering
"A good example is twice the value of good advice."
"HamishMcT" wrote:
> Hi there,
>
> If you can help me on what may seem like a trivial matter I would be
> very much obliged - I am tearing my hair out!
>
> I have a fairly simple formatting and sorting macro which handles a
> specific error first time but the next time the same error appears in
> the same module , it doesn't handle it at all!
>
>
>
> On Error GoTo error1
>
> Cells.Find(What:="1 total", After:=ActiveCell, LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByColumns,
> SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Activate
>
>
> is the first error handler (the label error1: is at the end of the
> module after exit sub...). This works fine - the code jumps over this
> bit as "1 total does not exist.
>
> However, later on in the code, for example, I need to find the same
> situation again so I have produced exactly the same code
>
>
> On Error GoTo error1
>
> Cells.Find(What:="1 total", After:=ActiveCell, LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByColumns,
> SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Activate
>
>
> This time it throws a run time error! Aaaargh.
>
> If anybody can advise why this might be the case I would be very
> grateful.
>
> Thanks and regards
>
> Hamish
>
>