Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error Handling
I have recorded a Macro and I am modifying it(easiest way for me to learn).
However I have run into a situation I can not find a solution in any documentation. Basically I have series of "cells.find" in sheet 1 of a workbook followed by a copy and paste to sheet 2 in the workbook. All is well if I find all the information I am trying to find with the cells.find. Before each "cells.find" I have inserted a statement "On Error goto Label1" or Label2, etc. Basically I am skipping the copy and paste and looking for the next information I need with the next "cells.find" . The first time I perform a "cells.find" and do not find the required information I branch correctly to the next part of the macro that does another "cells.find". If I encounter another error condition the macro does not take the branch to On Error GoTo Label20. It generates a run time error (91). I have added "err.Clear" and "On Error GoTo 0" but I still get a runtime error on the second failure of the "cells.find". I can not get the Macro to reset the "On Error GoTo Labeln". Does anyone have any suggestions on how to remedy this situation. I believe there may be some Pure VB solutions but I don't think I am ready to tackle them yet when I can't even get this "dumb" macro to work. Thank you in advance for your assistance. Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error Handling
My bet is that your find looks something like:
somerange.find(What:="something", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate And if that "something" is not found, it's not the .find portion that's causing the error. It's the .activate. I'd drop the error checking and use something like: Dim FoundCell as Range .... with someRangeHere set foundcell = .cells.find(What:="something", After:=.cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) End with If foundcell is nothing then 'not found, what should happen else 'found it. Do what you want to FoundCell here foundcell.offset(0,1).clearconents 'whatever end if ======== Then I'd either repeat this or turn it into a loop: dim myArr as variant dim iCtr as long dim FoundCell as range myarr = array("word1","word2","word3") for ictr = lbound(myarr) to ubound(myarr) with somerangehere set foundcell = .cells.find(What:=myarr(ictr), After:=.cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) end with If foundcell is nothing then 'not found, what should happen else 'found it. Do what you want to FoundCell here foundcell.offset(0,1).clearconents 'whatever end if next ictr end if ======== Untested, uncompiled. Watch for typos. Greg wrote: I have recorded a Macro and I am modifying it(easiest way for me to learn). However I have run into a situation I can not find a solution in any documentation. Basically I have series of "cells.find" in sheet 1 of a workbook followed by a copy and paste to sheet 2 in the workbook. All is well if I find all the information I am trying to find with the cells.find. Before each "cells.find" I have inserted a statement "On Error goto Label1" or Label2, etc. Basically I am skipping the copy and paste and looking for the next information I need with the next "cells.find" . The first time I perform a "cells.find" and do not find the required information I branch correctly to the next part of the macro that does another "cells.find". If I encounter another error condition the macro does not take the branch to On Error GoTo Label20. It generates a run time error (91). I have added "err.Clear" and "On Error GoTo 0" but I still get a runtime error on the second failure of the "cells.find". I can not get the Macro to reset the "On Error GoTo Labeln". Does anyone have any suggestions on how to remedy this situation. I believe there may be some Pure VB solutions but I don't think I am ready to tackle them yet when I can't even get this "dumb" macro to work. Thank you in advance for your assistance. Greg -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error Handling
Hi Greg,
There is quite a good example of Find Method in help that handles Not found. If you can't follow it well enough to implement then post the code you recorded for the find and I will edit it for you and include some comments as to what is occurring. -- Regards, OssieMac "Greg" wrote: I have recorded a Macro and I am modifying it(easiest way for me to learn). However I have run into a situation I can not find a solution in any documentation. Basically I have series of "cells.find" in sheet 1 of a workbook followed by a copy and paste to sheet 2 in the workbook. All is well if I find all the information I am trying to find with the cells.find. Before each "cells.find" I have inserted a statement "On Error goto Label1" or Label2, etc. Basically I am skipping the copy and paste and looking for the next information I need with the next "cells.find" . The first time I perform a "cells.find" and do not find the required information I branch correctly to the next part of the macro that does another "cells.find". If I encounter another error condition the macro does not take the branch to On Error GoTo Label20. It generates a run time error (91). I have added "err.Clear" and "On Error GoTo 0" but I still get a runtime error on the second failure of the "cells.find". I can not get the Macro to reset the "On Error GoTo Labeln". Does anyone have any suggestions on how to remedy this situation. I believe there may be some Pure VB solutions but I don't think I am ready to tackle them yet when I can't even get this "dumb" macro to work. Thank you in advance for your assistance. Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error Handling
Dave and OssieMac,
Thank you for your response. Dave, I am reviewing what you suggested in your response. OssieMac, I am pasting a small portion of the recorded Macro that I have modified. I still do not understand why I take the On Error branch once but not the second time. Sub MacroTestErrorHandling() ' Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False On Error GoTo BillingDate Cells.Find(What:="Account Number", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True).Activate ActiveCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("A2").Select ActiveSheet.Paste ' BillingDate: On Error GoTo 0 Err.Clear Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False On Error GoTo PaymentsReceived Cells.Find(What:="Billing Date", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True).Activate ActiveCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("B2").Select ActiveSheet.Paste ' PaymentsReceived: On Error GoTo 0 Err.Clear Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False On Error GoTo CorporateName Cells.Find(What:="Payment Received", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True).Activate ActiveCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("H2").Select ActiveSheet.Paste ' CorporateName: End Sub Thank you both for your replies. OssieMac any suggestions would be appreciatted. Greg "Greg" wrote in message ... I have recorded a Macro and I am modifying it(easiest way for me to learn). However I have run into a situation I can not find a solution in any documentation. Basically I have series of "cells.find" in sheet 1 of a workbook followed by a copy and paste to sheet 2 in the workbook. All is well if I find all the information I am trying to find with the cells.find. Before each "cells.find" I have inserted a statement "On Error goto Label1" or Label2, etc. Basically I am skipping the copy and paste and looking for the next information I need with the next "cells.find" . The first time I perform a "cells.find" and do not find the required information I branch correctly to the next part of the macro that does another "cells.find". If I encounter another error condition the macro does not take the branch to On Error GoTo Label20. It generates a run time error (91). I have added "err.Clear" and "On Error GoTo 0" but I still get a runtime error on the second failure of the "cells.find". I can not get the Macro to reset the "On Error GoTo Labeln". Does anyone have any suggestions on how to remedy this situation. I believe there may be some Pure VB solutions but I don't think I am ready to tackle them yet when I can't even get this "dumb" macro to work. Thank you in advance for your assistance. Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error Handling
Hi Greg,
I only edited the code a minimal amount. There are better ways of codeing without the necessity of selecting but I won't confuse you with that. I tested your code and sure enough it stops on the find if more than one not found. I have no idea why. Anyway the modification fixes it. I just comment out the on error lines but you can delete them. Also used a few more line breaks so that the code does not break up in this post. Sub MacroTestErrorHandling() Dim foundCell As Range Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False 'On Error GoTo BillingDate Set foundCell = Cells.Find(What:="Account Number", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True) If foundCell Is Nothing Then GoTo BillingDate Else foundCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("A2").Select ActiveSheet.Paste End If ' BillingDate: 'On Error GoTo 0 'Err.Clear Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False 'On Error GoTo PaymentsReceived Set foundCell = Cells.Find(What:="Billing Date", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True) If foundCell Is Nothing Then GoTo PaymentsReceived Else foundCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("B2").Select ActiveSheet.Paste End If ' PaymentsReceived: 'On Error GoTo 0 'Err.Clear Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False 'On Error GoTo CorporateName Set foundCell = Cells.Find(What:="Payment Received", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True) If foundCell Is Nothing Then GoTo CorporateName Else foundCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("H2").Select ActiveSheet.Paste End If ' CorporateName: End Sub -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error Handling
OssieMac,
Thank you I am going to try your code in the next couple of hours. I am still curious about why the second failure does not take the error branch but I am not going to lose any sleep over it. Thanks Again Greg "OssieMac" wrote in message ... Hi Greg, I only edited the code a minimal amount. There are better ways of codeing without the necessity of selecting but I won't confuse you with that. I tested your code and sure enough it stops on the find if more than one not found. I have no idea why. Anyway the modification fixes it. I just comment out the on error lines but you can delete them. Also used a few more line breaks so that the code does not break up in this post. Sub MacroTestErrorHandling() Dim foundCell As Range Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False 'On Error GoTo BillingDate Set foundCell = Cells.Find(What:="Account Number", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True) If foundCell Is Nothing Then GoTo BillingDate Else foundCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("A2").Select ActiveSheet.Paste End If ' BillingDate: 'On Error GoTo 0 'Err.Clear Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False 'On Error GoTo PaymentsReceived Set foundCell = Cells.Find(What:="Billing Date", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True) If foundCell Is Nothing Then GoTo PaymentsReceived Else foundCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("B2").Select ActiveSheet.Paste End If ' PaymentsReceived: 'On Error GoTo 0 'Err.Clear Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False 'On Error GoTo CorporateName Set foundCell = Cells.Find(What:="Payment Received", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True) If foundCell Is Nothing Then GoTo CorporateName Else foundCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("H2").Select ActiveSheet.Paste End If ' CorporateName: End Sub -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error Handling
Hi Greg,
I trolled the internet looking for an answer to the On Error problem but did not find anything. However, I did see a couple of suggestions that the below example of code is the better way of handling errors and it works for your code. (I am not advocating the use of the example code for Find. If I can write code without using On Error then I prefer it and keep the On Error for real errors because I have an extensive error routine that writes all the info to a text file so that the user does not have to tell me what has occurred.) If you look up On Error Statement in VBA help it also says "The On Error Resume Next construct may be preferable to On Error GoTo when handling errors". Anyway thought you might be interested. On Error Resume Next Cells.Find(What:="Account Number", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True).Activate If Err.Number 0 Then MsgBox "Account Number not found." Err.Clear On Error GoTo 0 'Required here GoTo BillingDate End If On Error GoTo 0 'Also required here -- Regards, OssieMac |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error Handling
sometimes it better to handle an expected error outside of the main routine
and inside a function for example, if you use the worksheet function VLOOKUP in VBA, it raises an error if there's no match. This is 'expected' so it can be more easily handled eg Sub Main() x = SafeLookup("something") 'safelookup returns 0 if it fails to match if x = 0 then {handle the error} else {something } End if End Sub Function SafeLookup(what as string) as long on error resume next SafeLookup = WorksheetFunction.VLookup(what, range("table"),2,flase) on error goto 0 'reset error End Function if there's no match, VLOOKUP raises an error, which resumes at the next line, switching off the error trap condition and a zero is safely returned to the calling routine I've seen code where the expected returns are positive integers, so the developers use a set of negative integers -- each of which describes a different error condition. The calling routine can thus more easily check and handle these. "Greg" wrote in message ... I have recorded a Macro and I am modifying it(easiest way for me to learn). However I have run into a situation I can not find a solution in any documentation. Basically I have series of "cells.find" in sheet 1 of a workbook followed by a copy and paste to sheet 2 in the workbook. All is well if I find all the information I am trying to find with the cells.find. Before each "cells.find" I have inserted a statement "On Error goto Label1" or Label2, etc. Basically I am skipping the copy and paste and looking for the next information I need with the next "cells.find" . The first time I perform a "cells.find" and do not find the required information I branch correctly to the next part of the macro that does another "cells.find". If I encounter another error condition the macro does not take the branch to On Error GoTo Label20. It generates a run time error (91). I have added "err.Clear" and "On Error GoTo 0" but I still get a runtime error on the second failure of the "cells.find". I can not get the Macro to reset the "On Error GoTo Labeln". Does anyone have any suggestions on how to remedy this situation. I believe there may be some Pure VB solutions but I don't think I am ready to tackle them yet when I can't even get this "dumb" macro to work. Thank you in advance for your assistance. Greg |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error Handling
I think there is no need to handle errors in your case as Dave said and
i think Dave's is the right way to go. I think the cause of a fail to handle error second time is you can not allocate errors to another handler within a active handler. if you want to use "On Error GoTo" statement, something like this might work. but i wouldn't use this code. Sub MacroTestErrorHandling() Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False On Error GoTo BillingDate Cells.Find(What:="Account Number", After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True).Activate ActiveCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("A2").Select ActiveSheet.Paste Re1: Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False On Error GoTo PaymentsReceived Cells.Find(What:="Billing Date", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True).Activate ActiveCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("B2").Select ActiveSheet.Paste Re2: Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False On Error GoTo CorporateName Cells.Find(What:="Payment Received", After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True).Activate ActiveCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("H2").Select ActiveSheet.Paste Re3: Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False Exit Sub BillingDate: On Error GoTo 0 '<<==No need but just for testing Resume Re1 PaymentsReceived: On Error GoTo 0 '<<==No need but just for testing Resume Re2 CorporateName: On Error GoTo 0 '<<==No need but just for testing Resume Re3 End Sub Keiji Greg wrote: Dave and OssieMac, Thank you for your response. Dave, I am reviewing what you suggested in your response. OssieMac, I am pasting a small portion of the recorded Macro that I have modified. I still do not understand why I take the On Error branch once but not the second time. Sub MacroTestErrorHandling() ' Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False On Error GoTo BillingDate Cells.Find(What:="Account Number", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True).Activate ActiveCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("A2").Select ActiveSheet.Paste ' BillingDate: On Error GoTo 0 Err.Clear Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False On Error GoTo PaymentsReceived Cells.Find(What:="Billing Date", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True).Activate ActiveCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("B2").Select ActiveSheet.Paste ' PaymentsReceived: On Error GoTo 0 Err.Clear Sheets("Source").Select Range("A1").Select Application.CutCopyMode = False On Error GoTo CorporateName Cells.Find(What:="Payment Received", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True).Activate ActiveCell.Select ActiveCell.Offset(1, 0).Copy Sheets("Target").Select Range("H2").Select ActiveSheet.Paste ' CorporateName: End Sub Thank you both for your replies. OssieMac any suggestions would be appreciatted. Greg "Greg" wrote in message ... I have recorded a Macro and I am modifying it(easiest way for me to learn). However I have run into a situation I can not find a solution in any documentation. Basically I have series of "cells.find" in sheet 1 of a workbook followed by a copy and paste to sheet 2 in the workbook. All is well if I find all the information I am trying to find with the cells.find. Before each "cells.find" I have inserted a statement "On Error goto Label1" or Label2, etc. Basically I am skipping the copy and paste and looking for the next information I need with the next "cells.find" . The first time I perform a "cells.find" and do not find the required information I branch correctly to the next part of the macro that does another "cells.find". If I encounter another error condition the macro does not take the branch to On Error GoTo Label20. It generates a run time error (91). I have added "err.Clear" and "On Error GoTo 0" but I still get a runtime error on the second failure of the "cells.find". I can not get the Macro to reset the "On Error GoTo Labeln". Does anyone have any suggestions on how to remedy this situation. I believe there may be some Pure VB solutions but I don't think I am ready to tackle them yet when I can't even get this "dumb" macro to work. Thank you in advance for your assistance. Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a Error Handling for this Macro | Excel Discussion (Misc queries) | |||
Error Handling Problem calling Macro from Other Worksheet | Excel Programming | |||
Error handling in macro | Excel Discussion (Misc queries) | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error handling with a handling routine | Excel Programming |