| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IsError error on Search
Hi, I am using the IsError function nested in an If statement to try and get
the macro to skip over some steps if the search comes up empty. I pass the search variable (Acct) from a function based on the number of the loop (counter). Works fine if the search has a positive result, but gets an object error if search is negative. Here is the code: Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub Columns("B:B").Select If IsError(Selection.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate) Then 'skip Else....... Any help will be appreciated. Thanks Marc |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IsError error on Search
I'd use:
Dim res as variant Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub with activesheet.Columns("B:B") res = .cells.find(what:=Acct, _ After:=.cells(.cells.count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True) if iserror(res) then 'not found else 'was found end if .... MFINE wrote: Hi, I am using the IsError function nested in an If statement to try and get the macro to skip over some steps if the search comes up empty. I pass the search variable (Acct) from a function based on the number of the loop (counter). Works fine if the search has a positive result, but gets an object error if search is negative. Here is the code: Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub Columns("B:B").Select If IsError(Selection.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate) Then 'skip Else....... Any help will be appreciated. Thanks Marc -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IsError error on Search
Didn't quite work. Still get the object error
"Run-time error 91" Object variable or With block variable not set "Dave Peterson" wrote: I'd use: Dim res as variant Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub with activesheet.Columns("B:B") res = .cells.find(what:=Acct, _ After:=.cells(.cells.count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True) if iserror(res) then 'not found else 'was found end if .... MFINE wrote: Hi, I am using the IsError function nested in an If statement to try and get the macro to skip over some steps if the search comes up empty. I pass the search variable (Acct) from a function based on the number of the loop (counter). Works fine if the search has a positive result, but gets an object error if search is negative. Here is the code: Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub Columns("B:B").Select If IsError(Selection.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate) Then 'skip Else....... Any help will be appreciated. Thanks Marc -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IsError error on Search
I don't know what I was thinking in that other post. For some reason, I was
thinking that you were using application.match() instead of .find. And I mixed up everything. Sorry. Dim FoundCell as range Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub with activesheet.Columns("B:B") set foundcell = .cells.find(what:=Acct, _ After:=.cells(.cells.count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True) end with if foundcell is nothing then 'not found else 'was found end if ..... MFINE wrote: Didn't quite work. Still get the object error "Run-time error 91" Object variable or With block variable not set "Dave Peterson" wrote: I'd use: Dim res as variant Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub with activesheet.Columns("B:B") res = .cells.find(what:=Acct, _ After:=.cells(.cells.count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True) if iserror(res) then 'not found else 'was found end if .... MFINE wrote: Hi, I am using the IsError function nested in an If statement to try and get the macro to skip over some steps if the search comes up empty. I pass the search variable (Acct) from a function based on the number of the loop (counter). Works fine if the search has a positive result, but gets an object error if search is negative. Here is the code: Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub Columns("B:B").Select If IsError(Selection.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate) Then 'skip Else....... Any help will be appreciated. Thanks Marc -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IsError error on Search
Thanks Dave, this works great!
Marc "Dave Peterson" wrote: I don't know what I was thinking in that other post. For some reason, I was thinking that you were using application.match() instead of .find. And I mixed up everything. Sorry. Dim FoundCell as range Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub with activesheet.Columns("B:B") set foundcell = .cells.find(what:=Acct, _ After:=.cells(.cells.count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True) end with if foundcell is nothing then 'not found else 'was found end if ..... MFINE wrote: Didn't quite work. Still get the object error "Run-time error 91" Object variable or With block variable not set "Dave Peterson" wrote: I'd use: Dim res as variant Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub with activesheet.Columns("B:B") res = .cells.find(what:=Acct, _ After:=.cells(.cells.count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True) if iserror(res) then 'not found else 'was found end if .... MFINE wrote: Hi, I am using the IsError function nested in an If statement to try and get the macro to skip over some steps if the search comes up empty. I pass the search variable (Acct) from a function based on the number of the loop (counter). Works fine if the search has a positive result, but gets an object error if search is negative. Here is the code: Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub Columns("B:B").Select If IsError(Selection.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate) Then 'skip Else....... Any help will be appreciated. Thanks Marc -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IsError error on Search
It's easier, instead of using IsError, just create a range object and set it
to the results of the find. Dim c as range Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub Columns("B:B").Select c=Selection.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False if c is nothing then 'skip Else....... "MFINE" wrote in message ... Hi, I am using the IsError function nested in an If statement to try and get the macro to skip over some steps if the search comes up empty. I pass the search variable (Acct) from a function based on the number of the loop (counter). Works fine if the search has a positive result, but gets an object error if search is negative. Here is the code: Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub Columns("B:B").Select If IsError(Selection.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate) Then 'skip Else....... Any help will be appreciated. Thanks Marc |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IsError error on Search
Oops, forgot "Set". Should be Set c=Selection.Find.... Well, Dave's
working with you, so you're in good hands.... "Zone" wrote in message ... It's easier, instead of using IsError, just create a range object and set it to the results of the find. Dim c as range Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub Columns("B:B").Select c=Selection.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False if c is nothing then 'skip Else....... "MFINE" wrote in message ... Hi, I am using the IsError function nested in an If statement to try and get the macro to skip over some steps if the search comes up empty. I pass the search variable (Acct) from a function based on the number of the loop (counter). Works fine if the search has a positive result, but gets an object error if search is negative. Here is the code: Do Until Counter 38 ACCTNAME (Counter) ' passes Acct name back to sub Columns("B:B").Select If IsError(Selection.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate) Then 'skip Else....... Any help will be appreciated. Thanks Marc |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error.Type or IsError to trap #VALUE! and #NUM! | Excel Worksheet Functions | |||
| What does ISERROR look at besides the 7 Error Types? | Excel Worksheet Functions | |||
| How do I use ISERROR in functions to hide error values | Excel Worksheet Functions | |||
| Error handling in a search | Excel Discussion (Misc queries) | |||
| The search key was not found error | New Users to Excel | |||
Hybrid Mode
