Error Trapping question....

  • Thread starter Thread starter Celt
  • Start date Start date
C

Celt

TIA

I have a macro that searches a "data dump" from my general ledger and
basically organizes it for review. I am trying to trap an error when
my macro encounters a range that does not exist. This is my code:

-code for the range (for which there is no data):-
ActiveWorkbook.Names.Add Name:="SVC", RefersToR1C1:= _

"=OFFSET(INDIRECT(ADDRESS(MATCH(40510,ALL!C5,0),5)),0,-4,COUNTIF(ALL!C5,40510),COUNTA(ALL!R4))"

-this is the error trap I am using:-
On Error GoTo skipSVC
Sheets("ALL").Select
Range("SVC").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Contrib Svc").Select
Range("A4").Select
ActiveSheet.Paste
skipSVC:

I was thinking that when the macro encountered a range for which there
was no data, it would just skip over this section.

I have quite a few of these in my macro (the others happen to have data
present)...

Can anyone shed a little light on my mistake and point me in the right
direction?

Thanks any help offered!!!!
 
Thanks Brian.

I know the range doesn't exist. The account I am asking it to find
40510 has no transactions in it.

So the macro is correctly telling me there is nothing to find, which is
creating the error. I am just looking for a way to have the macro skip
over this step if it encounters an error.

I thought my coding would do it, but it's not.
 
You can use an On Error statement to overlook all errors by saying

On Error Resume Next

However, if the error produces a specific error number you can use something
like the following:

On error GoTo Err_Trap

do something here...

Err_Trap:
'#### being the specific error number produced by
'the missing range
If Err.Number = #### then
Resume Next
Else
msgbox Err.Number & vbcrlf & vbcrlf & _
Err.Description
Exit Sub
End If
 
I forgot to add something in the prior post.

I would change the error trap If statement to read:

If Err.Number = #### then
Err.Clear
Resume next
Else
msgbox Err.Number & vbcrlf & vbcrlf & _
Err.Description
Exit Sub
End If
 
Back
Top