| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Handle missing values | Charts and Charting in Excel | |||
| Handle missing values | Excel Programming | |||
| How to handle automation error? | Excel Programming | |||
| Error Handle | Excel Programming | |||
| How to handle error 8007000e Memory Error | Excel Programming | |||
Linear Mode
