VBA- Find Errors

[jordanctc]

I have a weekly shipment checking macro and in it I use *Find* to loo
for specific shipment numbers. From time to time the number I'
looking for isn't there so I'm left inserting a temporary hack to ge
through that week.

Being self-taught with tons of help from this group there are definit
gaps in the VBA education (like error-handling). What is the best wa
to avoid these errors? Do I need to count the number of times th
number comes up in the column and if it's >0 then exectute else skip?

There are usually some good functions or tricks and I'm about to ad
some more finds (since they're so useful) but only if I can keep th
'ol macro running

Thanks,
Jordan

PS. Any good links to sites/pages with programming advise for VB
error handling of any kind

 

[Bob Kilmer]

Direct solution; avoid the error issue by checking if a range was returned:

Dim rng as Range
Set rng = Range(blahblah).Find("may_not_be_found")

If Not rng is Nothing Then
'do something with rng
Else
'don't, cause you didn't find it
End If

'or

If rng Is Nothing Then
msgbox "nothing found"
Else
msgbox "something found"
End If

For error trapping, look up On Error Statement in online Help. There is a
time for error trapping and a time to ignore or proactively avoid certain
errors. Errors propagate also.

 

[Bob Kilmer]

Here is a simple example of inline error handling. Notice the line "On
Error Resume Next ' ignore errors." In the loop, I am ignoring the error
raised by trying to add duplicate keys, so the collection ends up containing
a unique set of values, but ignores the error I know I want ignored. Be
warned: Do not take this and add it indiscriminately to your code and begin
ignoring all errors. You may mask errors you want to know about. Even here,
I probably should put the Error statements in the loop so I surround only
the Add operation.

Function UniqueNames() As Collection
'Returns a unique set of text values from rngNames.
'It depends on the property of the Collection
'object to require unique keys (the second arg to
'col.Add). Trying to add a duplicate throws an
'error which we conveniently ignore. This is a
'common technique.
Dim col As Collection 'we'll return this
Dim wks As Worksheet 'the raw data sheet
Dim rngNames As Range 'the names
Dim cell As Range

Set wks = Workbooks("Book1.xls").Worksheets("Sheet0")
Set rngNames = Intersect(wks.Columns("A"), wks.UsedRange)
Set col = New Collection

On Error Resume Next ' ignore errors
For Each cell In rngNames
col.Add cell.Text, cell.Text
Next cell
On Error GoTo 0 'un-ignore errors

Set UniqueNames = col
Set col = Nothing

End Function