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

Check out this thread:

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

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.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=543810

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

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