Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA- Find Errors
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA- Find Errors
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. "jordanctc " wrote in message ... I have a weekly shipment checking macro and in it I use *Find* to look for specific shipment numbers. From time to time the number I'm looking for isn't there so I'm left inserting a temporary hack to get through that week. Being self-taught with tons of help from this group there are definite gaps in the VBA education (like error-handling). What is the best way to avoid these errors? Do I need to count the number of times the 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 add some more finds (since they're so useful) but only if I can keep the 'ol macro running Thanks, Jordan PS. Any good links to sites/pages with programming advise for VBA error handling of any kind? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA- Find Errors
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 "jordanctc " wrote in message ... I have a weekly shipment checking macro and in it I use *Find* to look for specific shipment numbers. From time to time the number I'm looking for isn't there so I'm left inserting a temporary hack to get through that week. Being self-taught with tons of help from this group there are definite gaps in the VBA education (like error-handling). What is the best way to avoid these errors? Do I need to count the number of times the 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 add some more finds (since they're so useful) but only if I can keep the 'ol macro running Thanks, Jordan PS. Any good links to sites/pages with programming advise for VBA error handling of any kind? --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find errors in documents | Excel Discussion (Misc queries) | |||
Find and Replace Lookup errors | Excel Discussion (Misc queries) | |||
To find errors | New Users to Excel | |||
Find errors | Excel Programming | |||
Find finds nothing, and errors | Excel Programming |