Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
why doesn't on error work?
I have the following code with an on error statement:
For i = 1 To zz search_term = Workbooks("search.xls").Worksheets("Search Terms").Cells(i, 1) On Error GoTo not_found search_column = Cells.Find(What:=search_term, After:=[A1], LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Column [Do Stuff if search_term is found on worksheet] not_found: On Error GoTo 0 Next i The for loop should go through 155 terms. The first time the search_term is not found the on error statement works as it should and the code is bypassed. But the second time the search_term is not found I get an error message on the code line with the Find function. What is going on with my error handling? Is there a better way to control my flow? John Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
why doesn't on error work?
Hi John,
The following is the usual method of using find. You don't need on error. You set a range variable to the found cell and if it is nothing then it is not found but if it is Not nothing then it is found. Also include the sheet name with ranges either using With or as I have doen in the example. That way if your code ever makes another worksheet the active sheet, the code still works. You can discard the msgboxes and also the else in the If/Then/Else. they are only there for testing purposes. Dim search_term Dim rngFound As Range Dim search_column As Long Dim i As Long Dim zz As Long zz = 5 'Used for testing For i = 1 To zz search_term = Worksheets("Search Terms").Cells(i, 1) 'Edit "Sheet2" in following line 'to match your worksheet name Set rngFound = Sheets("Sheet2") _ .Cells.Find(What:=search_term, _ After:=[A1], _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rngFound Is Nothing Then search_column = rngFound.Column MsgBox "found " & search_term & " at column " & search_column '[Do Stuff if search_term is found on worksheet] Else MsgBox "not found" End If Next i -- Regards, OssieMac "John Keith" wrote: I have the following code with an on error statement: For i = 1 To zz search_term = Workbooks("search.xls").Worksheets("Search Terms").Cells(i, 1) On Error GoTo not_found search_column = Cells.Find(What:=search_term, After:=[A1], LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Column [Do Stuff if search_term is found on worksheet] not_found: On Error GoTo 0 Next i The for loop should go through 155 terms. The first time the search_term is not found the on error statement works as it should and the code is bypassed. But the second time the search_term is not found I get an error message on the code line with the Find function. What is going on with my error handling? Is there a better way to control my flow? John Keith . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
why doesn't on error work?
forgot to say re-insert your workbook name in the following line of your
code. I took it out because I tested in a single workbook. search_term = Workbooks("search.xls").Worksheets("Search Terms").Cells(i, 1) -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
why doesn't on error work?
I would highly recommend you not use On Error Resume Next in your code.
Usually 99.99% of the time you can develop some code in order to not us this statement. You are certainly asking for trouble with that statement. Now that I am done punishing you I would alter your code just a bit, like below. Let me know if this helps! If so, let me know, click "YES" below. Sub OnError() Dim i As Long Dim Search_Range As Range Dim myColumn As Long For i = 1 To zz search_term = Workbooks("search.xls").Worksheets("SearchTerms ").Cells(i, 1) Set Search_Range = Cells.Find(What:=search_term, _ After:=[A1], _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Search_Range Is Nothing Then myColumn = Search_Range.Column 'Do Stuff if search_term is found on worksheet End If Next i End Sub -- Cheers, Ryan "John Keith" wrote: I have the following code with an on error statement: For i = 1 To zz search_term = Workbooks("search.xls").Worksheets("Search Terms").Cells(i, 1) On Error GoTo not_found search_column = Cells.Find(What:=search_term, After:=[A1], LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Column [Do Stuff if search_term is found on worksheet] not_found: On Error GoTo 0 Next i The for loop should go through 155 terms. The first time the search_term is not found the on error statement works as it should and the code is bypassed. But the second time the search_term is not found I get an error message on the code line with the Find function. What is going on with my error handling? Is there a better way to control my flow? John Keith . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
why doesn't on error work?
When an error occurs, VBA code execution goes into "error mode" an no
subsequent error handling can occur until the code exits error mode and resumes normal mode. You can exit error mode by one of (1) exiting the procedure, (2) using Resume to continue code execution at the line that cause the error, or (3) using Resume Next to continue execution at the line following the line that caused the error, or (4) Resume <Label to continue execution at a code label. If your error handling doesn't do one of the above, it remains in error mode and any error will break the code, regardless of the On Error setting. Note that using Resume when the code is not running in error mode will cause an error. The following code illustrates various aspects error handling: Sub AAA() Dim X As Long Dim Y As Long Dim N As Long On Error GoTo EndLoop: For N = 1 To 10 X = N If X = 4 Or X = 5 Then X = 0 End If ResumeHe Debug.Print N, X, N / X EndLoop: If Err.Number < 0 Then X = 1 Resume ResumeHere End If Next N End Sub For an extensive discussion of error hanlding, see http://www.cpearson.com/Excel/ErrorHandling.htm. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Sat, 19 Dec 2009 14:53:57 -0700, John Keith wrote: I have the following code with an on error statement: For i = 1 To zz search_term = Workbooks("search.xls").Worksheets("Search Terms").Cells(i, 1) On Error GoTo not_found search_column = Cells.Find(What:=search_term, After:=[A1], LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Column [Do Stuff if search_term is found on worksheet] not_found: On Error GoTo 0 Next i The for loop should go through 155 terms. The first time the search_term is not found the on error statement works as it should and the code is bypassed. But the second time the search_term is not found I get an error message on the code line with the Find function. What is going on with my error handling? Is there a better way to control my flow? John Keith |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
why doesn't on error work?
On Sat, 19 Dec 2009 15:37:01 -0800, OssieMac
wrote: The following is the usual method of using find. You don't need on error. You set a range variable to the found cell and if it is nothing then it is not found but if it is Not nothing then it is found. OssieMac, Thank for the more usual method! When I first ran into the need to handle the situation I tried to implement this method but I'm still enough of a beginner that I couldn't do it right so I treid the "on error" method and still failed with that! I look forward to the day when I'll be more capable like you and many of the other experts lurking in this group! John Keith |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
why doesn't on error work?
On Mon, 21 Dec 2009 05:55:01 -0800, Ryan H
wrote: I would highly recommend you not use On Error Resume Next in your code. Usually 99.99% of the time you can develop some code in order to not us this statement. You are certainly asking for trouble with that statement. Now that I am done punishing you I would alter your code just a bit, like below. Ryan, The punishment was not too harsh, I'm still learning, thank you for the suggestion. John Keith |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
why doesn't on error work?
On Mon, 21 Dec 2009 09:18:17 -0600, Chip Pearson
wrote: When an error occurs, VBA code execution goes into "error mode" an no subsequent error handling can occur until the code exits error mode and resumes normal mode. Chip, Thank you for the quick tutorial, I haven't been to your website on error handling but will do so after the holiday. But from what I read I guess that "On Error GoTo 0" is not one of the methods to reset the mode. John Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro error - cant work it out... | Excel Worksheet Functions | |||
DSUM gives a #VALUE! error and I can't work out why! | Excel Worksheet Functions | |||
VBA ON ERROR does not work with SPECIALCELLS | Excel Discussion (Misc queries) | |||
Error control no work :( | Excel Programming | |||
'Help on this error' does not work? | Excel Programming |