Dim FoundCell as range

set foundcell = cells.find(....) 'no .activate here!

if foundcell is nothing then
'not found, do nothing or whatever you want
else
'found it, do the real work.
end if



carl wrote:

I have a macro that requires copying and pasting data from a pivot table in
to another workbook. I need to copy data in certain cells whenever there is
data available. So some months certain products will not have any data so
the pivot table will not even show them.

I have written some script in to a macro so that once the macro has chosen
the correct pivot table variables it then searches for the product. When it
finds the product it goes to the last cell in the row with data in it and
copies and pastes it in to the other work book. The problem is that when it
searches for something not there it stops the macro.

How do I write allow for times when excel cannot find the data and returns
the cannot find message? Basically, I want the macro to run normally when it
can find it but when it can't I want it to move on to the next search. So
this would be the normal script:

Cells.Find(What:="Toys", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Selection.End(xlToRight).Select

Is there something I can put after the Cells.Find bit that allows it to
ignore the rest of the script and move on to the next search if it can't find
it?

--

Dave Peterson