handle error in cell values

[Junior728]

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

 

[Bob Phillips]

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)