|
handle error in cell values
Hi,
I have encounter #N/A error in rows(left undetected), that when i execute macro , it does not proceed. So i try to put the "On Error" command , the macro runs as usual even if it has a #N/A error! Is there any way i can contain the error and exit Sub(not run the macro), while if there is no error found, then macro will run? It will be good if other types of cell error like #REF# can also be contained. e.g. below: Range("A1").Select NumOfRows = Cells(Rows.Count, 1).End(xlUp).Row For CurrentRow = 4 To NumOfRows WH$ = ActiveSheet.Cells(CurrentRow, 1) PART$ = Trim(ActiveSheet.Cells(CurrentRow, 2)) 'VEND$ = Trim(ActiveSheet.Cells(CurrentRow, 3)) CODE$ = Trim(ActiveSheet.Cells(CurrentRow, 3)) RMA$ = Trim(ActiveSheet.Cells(CurrentRow, 4)) QTY$ = Trim(ActiveSheet.Cells(CurrentRow, 5)) PRICE$ = ActiveSheet.Cells(CurrentRow, 6) BUYER$ = ActiveSheet.Cells(CurrentRow, 7) ACCT$ = ActiveSheet.Cells(CurrentRow, 8) VA$ = ActiveSheet.Cells(CurrentRow, 9) VA2$ = ActiveSheet.Cells(CurrentRow, 10) FUNCT$ = ActiveSheet.Cells(CurrentRow, 11) PL_NO$ = ActiveSheet.Cells(CurrentRow, 21) On Error Resume Next If FUNCT = "NEW" Then Print #2, "FunctionKey (CLEAR)" Print #2, "WaitFor (UNLOCK)" Print #2, "WaitForCursorPos (1,10)" Print #2, "FunctionKey (HOME)" Print #2, "TypeString ("; Chr$(34); "ENTROE"; ACCT; ","; WH; Chr$(34); ")" Print #2, "FunctionKey (ERASETOEOF)" Print #2, "FunctionKey (ENTER)" Print #2, "WaitFor (UNLOCK)" Print #2, "WaitForCursorPos (1,10)" Print #2, "FunctionKey (NEWLINE)" Print #2, "FunctionKey (NEWLINE)" Print #2, "FunctionKey (NEWLINE)" Print #2, "FunctionKey (NEWLINE)" Print #2, "FunctionKey (NEWLINE)" Print #2, "FunctionKey (NEWLINE)" Print #2, "WaitFor (UNLOCK)" Print #2, "WaitForCursorPos (8, 6)" Print #2, "TypeString ("; Chr$(34); RMA$; Chr$(34); ")" Print #2, "FunctionKey (TAB)" Print #2, "TypeString ("; Chr$(34); BUYER$; Chr$(34); ")" Print #2, "FunctionKey (DOWN)" Print #2, "FunctionKey (TAB)" Print #2, "TypeString ("; Chr$(34); CODE$; Chr$(34); ")" Print #2, "FunctionKey(TAB)" Print #2, "FunctionKey (TAB)" Print #2, "WaitForCursorPos (13, 9)" Print #2, "TypeString ("; Chr$(34); PART$; Chr$(34); ")" Print #2, "WaitForCursorPos (13, 37)" Print #2, "TypeString ("; Chr$(34); QTY$; Chr$(34); ")" Print #2, "FunctionKey (TAB)" Print #2, "FunctionKey (TAB)" Print #2, "WaitForCursorPos (13, 56)" Print #2, "TypeString ("; Chr$(34); PRICE$; Chr$(34); ")" 'Print #2, "FunctionKey (TAB)" 'Print #2, "FunctionKey (TAB)" 'Print #2, "WaitForCursorPos (14, 09)" 'Print #2, "TypeString ("; Chr$(34); PL_NO$; Chr$(34); ")" 'Print #2, "FunctionKey (ERASETOEOF)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "WaitForCursorPos (23, 09)" 'Print #2, "TypeString ("; Chr$(34); VA; Chr$(34); ")" 'Print #2, "FunctionKey (TAB)" 'Print #2, "WaitForCursorPos (23,40)" 'Print #2, "TypeString ("; Chr$(34); VA2; Chr$(34); ")" Print #2, "FunctionKey (PF12)" End If If FUNCT = "ADD" Then Print #2, "WaitFor (UNLOCK)" Print #2, "WaitForCursorPos (8,5)" Print #2, "FunctionKey (TAB)" Print #2, "TypeString ("; Chr$(34); PART$; Chr$(34); ")" Print #2, "WaitForCursorPos (8, 37)" Print #2, "TypeString ("; Chr$(34); QTY$; Chr$(34); ")" Print #2, "FunctionKey (TAB)" Print #2, "FunctionKey (TAB)" Print #2, "WaitForCursorPos (8, 56)" Print #2, "TypeString ("; Chr$(34); PRICE$; Chr$(34); ")" Print #2, "FunctionKey (TAB)" Print #2, "FunctionKey (TAB)" Print #2, "WaitForCursorPos (09, 09)" Print #2, "TypeString ("; Chr$(34); PL_NO$; Chr$(34); ")" Print #2, "FunctionKey (ERASETOEOF)" Print #2, "FunctionKey (PF12)" End If On Error GoTo ErrHandler ErrHandler: MsgBox ("Something is wrong with" & Err.Number) Exit Sub Next |
handle error in cell values
Test the offending cells using IsError
If IsError(cell-ref) Then Exit Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Junior728" wrote in message ... Hi, I have encounter #N/A error in rows(left undetected), that when i execute macro , it does not proceed. So i try to put the "On Error" command , the macro runs as usual even if it has a #N/A error! Is there any way i can contain the error and exit Sub(not run the macro), while if there is no error found, then macro will run? It will be good if other types of cell error like #REF# can also be contained. e.g. below: Range("A1").Select NumOfRows = Cells(Rows.Count, 1).End(xlUp).Row For CurrentRow = 4 To NumOfRows WH$ = ActiveSheet.Cells(CurrentRow, 1) PART$ = Trim(ActiveSheet.Cells(CurrentRow, 2)) 'VEND$ = Trim(ActiveSheet.Cells(CurrentRow, 3)) CODE$ = Trim(ActiveSheet.Cells(CurrentRow, 3)) RMA$ = Trim(ActiveSheet.Cells(CurrentRow, 4)) QTY$ = Trim(ActiveSheet.Cells(CurrentRow, 5)) PRICE$ = ActiveSheet.Cells(CurrentRow, 6) BUYER$ = ActiveSheet.Cells(CurrentRow, 7) ACCT$ = ActiveSheet.Cells(CurrentRow, 8) VA$ = ActiveSheet.Cells(CurrentRow, 9) VA2$ = ActiveSheet.Cells(CurrentRow, 10) FUNCT$ = ActiveSheet.Cells(CurrentRow, 11) PL_NO$ = ActiveSheet.Cells(CurrentRow, 21) On Error Resume Next If FUNCT = "NEW" Then Print #2, "FunctionKey (CLEAR)" Print #2, "WaitFor (UNLOCK)" Print #2, "WaitForCursorPos (1,10)" Print #2, "FunctionKey (HOME)" Print #2, "TypeString ("; Chr$(34); "ENTROE"; ACCT; ","; WH; Chr$(34); ")" Print #2, "FunctionKey (ERASETOEOF)" Print #2, "FunctionKey (ENTER)" Print #2, "WaitFor (UNLOCK)" Print #2, "WaitForCursorPos (1,10)" Print #2, "FunctionKey (NEWLINE)" Print #2, "FunctionKey (NEWLINE)" Print #2, "FunctionKey (NEWLINE)" Print #2, "FunctionKey (NEWLINE)" Print #2, "FunctionKey (NEWLINE)" Print #2, "FunctionKey (NEWLINE)" Print #2, "WaitFor (UNLOCK)" Print #2, "WaitForCursorPos (8, 6)" Print #2, "TypeString ("; Chr$(34); RMA$; Chr$(34); ")" Print #2, "FunctionKey (TAB)" Print #2, "TypeString ("; Chr$(34); BUYER$; Chr$(34); ")" Print #2, "FunctionKey (DOWN)" Print #2, "FunctionKey (TAB)" Print #2, "TypeString ("; Chr$(34); CODE$; Chr$(34); ")" Print #2, "FunctionKey(TAB)" Print #2, "FunctionKey (TAB)" Print #2, "WaitForCursorPos (13, 9)" Print #2, "TypeString ("; Chr$(34); PART$; Chr$(34); ")" Print #2, "WaitForCursorPos (13, 37)" Print #2, "TypeString ("; Chr$(34); QTY$; Chr$(34); ")" Print #2, "FunctionKey (TAB)" Print #2, "FunctionKey (TAB)" Print #2, "WaitForCursorPos (13, 56)" Print #2, "TypeString ("; Chr$(34); PRICE$; Chr$(34); ")" 'Print #2, "FunctionKey (TAB)" 'Print #2, "FunctionKey (TAB)" 'Print #2, "WaitForCursorPos (14, 09)" 'Print #2, "TypeString ("; Chr$(34); PL_NO$; Chr$(34); ")" 'Print #2, "FunctionKey (ERASETOEOF)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "FunctionKey (NEWLINE)" 'Print #2, "WaitForCursorPos (23, 09)" 'Print #2, "TypeString ("; Chr$(34); VA; Chr$(34); ")" 'Print #2, "FunctionKey (TAB)" 'Print #2, "WaitForCursorPos (23,40)" 'Print #2, "TypeString ("; Chr$(34); VA2; Chr$(34); ")" Print #2, "FunctionKey (PF12)" End If If FUNCT = "ADD" Then Print #2, "WaitFor (UNLOCK)" Print #2, "WaitForCursorPos (8,5)" Print #2, "FunctionKey (TAB)" Print #2, "TypeString ("; Chr$(34); PART$; Chr$(34); ")" Print #2, "WaitForCursorPos (8, 37)" Print #2, "TypeString ("; Chr$(34); QTY$; Chr$(34); ")" Print #2, "FunctionKey (TAB)" Print #2, "FunctionKey (TAB)" Print #2, "WaitForCursorPos (8, 56)" Print #2, "TypeString ("; Chr$(34); PRICE$; Chr$(34); ")" Print #2, "FunctionKey (TAB)" Print #2, "FunctionKey (TAB)" Print #2, "WaitForCursorPos (09, 09)" Print #2, "TypeString ("; Chr$(34); PL_NO$; Chr$(34); ")" Print #2, "FunctionKey (ERASETOEOF)" Print #2, "FunctionKey (PF12)" End If On Error GoTo ErrHandler ErrHandler: MsgBox ("Something is wrong with" & Err.Number) Exit Sub Next |
| All times are GMT +1. The time now is 09:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com