handle error in cell values

J

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
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top