|
Error Trapping question....
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 |
Error Trapping question....
|
Error Trapping question....
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 |
Error Trapping question....
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 |
Error Trapping question....
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 |
| All times are GMT +1. The time now is 12:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com