Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find function bypass
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find function bypass
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find function bypass
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" wrote: 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 Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find function bypass
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" wrote: 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 Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find function bypass
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("somesheetnameher e") 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 carl wrote: 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" wrote: 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 Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bypass a function in a Macro | Excel Discussion (Misc queries) | |||
how to bypass password?? | Excel Programming | |||
Bypass Worksheet_Change Sub | Excel Programming | |||
Bypass an | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |