Error Trapping question....

[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!!!!

[Brian Taylor]

Check out this thread:

http://groups.google.com/group/micro...bbeb85f902c740

[Celt]

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.

[Kevin B]

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


--
Kevin Backmann


"Celt" wrote:

>
> 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!!!!
>
>
> --
> Celt
> ------------------------------------------------------------------------
> Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> View this thread: http://www.excelforum.com/showthread...hreadid=543810
>
>

[Kevin B]

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

--
Kevin Backmann


"Kevin B" wrote:

> 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
>
>
> --
> Kevin Backmann
>
>
> "Celt" wrote:
>
> >
> > 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!!!!
> >
> >
> > --
> > Celt
> > ------------------------------------------------------------------------
> > Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> > View this thread: http://www.excelforum.com/showthread...hreadid=543810
> >
> >