on error.....

[ermccarthy]

I have a macro that runs and looks for an entered "account number". If the user mistypes the account number it search and returns a Run time error # 9: subscript out of range.

I would like to put in the macro, if this error occurs to exit the loop, and display a message box that says "Please check your account number", and continues on with the rest of the macro....

Any Ideas.....Here is the Macro...

acct1 = Range("acct1").Value
acct2 = Range("acct2").Value
acct3 = Range("acct3").Value
acct4 = Range("acct4").Value
path1 = Range("path1").Value
path2 = Range("path2").Value
path3 = Range("path3").Value
path4 = Range("path4").Value
Sht1 = Range("Sheet1").Value
Sht2 = Range("Sheet2").Value
Sht3 = Range("Sheet3").Value
Sht4 = Range("Sheet4").Value
workbk1 = Range("FileName").Value
acct = ActiveCell.Value
Sheets("Matrix").Visible = True
Worksheets("matrix").Select
Range("D4").Select
Sheets("PO").Select
Range("G49:G52").Select
Selection.ClearContents
Sheets("Matrix").Select
Range("G4:G7").Select
Selection.ClearContents
Sheets("PO").Select
Range("F48").Select
Worksheets("matrix").Select
Range("D4").Select
If ActiveCell.Value <> "" Then
Workbooks.Open FileName:=path1
Worksheets(Sht1).Select
Range("F11").Select
Do Until ActiveCell.Value = acct1
ActiveCell.Offset(0, 1).Select 'this is where the error pops in....
Loop
ActiveCell.Offset(100, 0).Select
Do Until ActiveCell <> ""
ActiveCell.Offset(-1, 0).Select
Loop
Selection.Copy
'close the workbook

Workbooks(workbk1).Activate
ActiveCell.Offset(0, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(1, -3).Select
Windows("Current year.xls").Activate
ActiveWorkbook.Close
Workbooks(workbk1).Activate
Else: End If

'''''''I want the macro to restart here when the error occurs!!!!!!!!!

'start second account
Range("acct2").Select

If ActiveCell.Value <> "" Then
Workbooks.Open FileName:=path2
Worksheets(Sht2).Select
Range("F11").Select
Do Until ActiveCell.Value = acct2
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Offset(100, 0).Select
Do Until ActiveCell <> ""
ActiveCell.Offset(-1, 0).Select
Loop
Selection.Copy
'close the workbook

Workbooks(workbk1).Activate
ActiveCell.Offset(0, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(1, -3).Select
Windows("Current year.xls").Activate
ActiveWorkbook.Close
Workbooks(workbk1).Activate
Else: End If

'start third account
If ActiveCell.Value <> "" Then
Workbooks.Open FileName:=path3
Worksheets(Sht1).Select
Range("F11").Select
Do Until ActiveCell.Value = acct3
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Offset(100, 0).Select
Do Until ActiveCell <> ""
ActiveCell.Offset(-1, 0).Select
Loop
Selection.Copy
'close the workbook

Workbooks(workbk1).Activate
ActiveCell.Offset(0, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(1, -3).Select
Windows("Current year.xls").Activate
ActiveWorkbook.Close
Workbooks(workbk1).Activate
Else: End If


'start fourth account
If ActiveCell.Value <> "" Then
Workbooks.Open FileName:=path4
Worksheets(Sht1).Select
Range("F11").Select
Do Until ActiveCell.Value = acct4
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Offset(100, 0).Select
Do Until ActiveCell <> ""
ActiveCell.Offset(-1, 0).Select
Loop
Selection.Copy
'close the workbook
Workbooks(workbk1).Activate
ActiveCell.Offset(0, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(1, -3).Select
Windows("Current year.xls").Activate
ActiveWorkbook.Close
Workbooks(workbk1).Activate
Else: End If
'paste the prev balances to the Purchase order

Sheets("Matrix").Select
Range("G4:G7").Select
Selection.Copy
Sheets("PO").Select
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 31
ActiveWindow.SmallScroll Down:=3
Range("G49").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F49").Select

Sheets("Matrix").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("PO").Select
End Sub

[kkknie]

Somewhere near the beginning of your code place this line:

On Error Goto ErrHnd

Where you want the code to return place this line:

ResumePlace:

Right before the End Sub line, place this code

Exit Sub

ErrHnd:

msgbox "Please check your account number"
resume ResumePlace

End Sub

You may want to enhance the ErrHnd section by checking for the correct error number (i.e. if err.Number = 9 then...)

K

[ermccarthy]

GREAT!!!!!!!!!!!!!

But I have one more little item....
if you look at my code it is basically the same code running four times with different values (acct1, acct2, etc). therefore if the wrong account number is inputted on the 1st account, it works, but if it is on the second account, it jumps it back to the ResumePlace: (back in the first part of the code)...any ideas short of breaking the code up into 4 different routines and running them consectively........but if this is the only way, I am not opposed to it