Find function bypass

[Guest]

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]

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

 

[Guest]

Hi Dave,

Thanks for your help. Being a bit of a beginner I can't get this to work.
I changed it because I assume I couldn't just copy and paste it in to my
script. Either way it's not worked. I changed it to this:

With Cells.Find(What:="Toys", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim FoundCell As Range
Set FoundCell = Cells.Find(Toys) 'no .activate here!
If FoundCell Is Nothing Then End
'not found, do nothing or whatever you want
Else
Selection.End(xlToRight).Select
ActiveCell.Select
Selection.Copy
Windows("Toys.xls").Activate
Range("A23").Select
Selection.PasteSpecial Paste:=xlPasteValues
'found it, do the real work.
End With

It stops at the "Else" part saying that it needs "If" after "Else". Am I
doing this all wrong?

 

[Guest]

Hi Dave,

Thanks for your help. Being a bit of a beginner I can't get this to work.
I changed it because I assume I couldn't just copy and paste it in to my
script. Either way it's not worked. I changed it to this:

With Cells.Find(What:="Toys", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim FoundCell As Range
Set FoundCell = Cells.Find(Toys) 'no .activate here!
If FoundCell Is Nothing Then End
'not found, do nothing or whatever you want
Else
Selection.End(xlToRight).Select
ActiveCell.Select
Selection.Copy
Windows("Toys.xls").Activate
Range("A23").Select
Selection.PasteSpecial Paste:=xlPasteValues
'found it, do the real work.
End With

It stops at the "Else" part saying that it needs "If" after "Else". Am I
doing this all wrong?

 

[Dave Peterson]

I don't like relying on the activesheet of a different workbook.

If you know the name of the worksheet in toys.xls, you might as well specify it:

Dim FoundCell As Range
Dim ToysWks As Worksheet

Set ToysWks = Workbooks("Toys.xls").Worksheets("somesheetnamehere")

Set FoundCell = Cells.Find(What:="Toys", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If FoundCell Is Nothing Then End
'not found, do nothing or whatever you want
MsgBox "Toys not found!"
Else
ToysWks.Range("a23").Value = FoundCell.End(xlToRight).Value
End With

 

[Dave Peterson]

I copied from your ammended code and missed changing this back.

this:
If FoundCell Is Nothing Then End
should be:
If FoundCell Is Nothing Then
(drop that End)