Macro Error Handling

[Greg]

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]

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.

 

[OssieMac]

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.

 

[OssieMac]

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

 

[Greg]

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]

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

 

[Patrick Molloy]

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.

 

[keiji kounoike]

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