VBA Array error

Question

I have the following code which uses two for loops (Prod and Dev)
There are many values in the array but i have taken only two for the example What it does is, it copies the value from one excel to the other.

Now, there is a probability that file NSA_103_B_Roles.xls doesnot exist In that case, i dont want the code to take any action, so i have put on error resume next

But still it is printing the value in the excel which doesnot exist, What is the reason?

Private Sub CommandButton1_Click()

Prod = Array("ZS7_656", "PCO_656")
Dev = Array("NSA_103", "DCA_656")

For lngCounter1 = LBound(Dev) To UBound(Dev)
    For lngCounter = LBound(Prod) To UBound(Prod)

        On Error Resume Next
        Set Zz2 = Workbooks.Open("C:\Users\*****\Desktop\New folder\" & 
        Dev(lngCounter1) & "_B_Roles.xls")
        Zz2.Sheets(Dev(lngCounter1) & "_B_Roles").Range("A1").Value = "anirudh"

        ThisWorkbook.Sheets(Prod(lngCounter)).Range("A2").Value = 
        Zz2.Sheets(Dev(lngCounter1) & "_B_Roles").Range("A1").Value

        On Error GoTo 0
    Next lngCounter
Next lngCounter1

End Sub

Do not use On Error Resume Next. Check for the error first, and take action according to that check. In your case: check first if the file exists. This can easily be done with the Dir function. — user7857211
– user7857211, Commented Aug 29, 2017 at 12:29

Shai Rado · Accepted Answer · 2017-08-29 10:58:52Z

Try the code below, explanation inside the code's comments :

Private Sub CommandButton1_Click()

Dim Zz2 As Workbook

Prod = Array("ZS7_656", "PCO_656")
Dev = Array("NSA_103", "DCA_656")

For lngCounter1 = LBound(Dev) To UBound(Dev)
    For lngCounter = LBound(Prod) To UBound(Prod)

        ' ==== this section starts the error handling ===
        On Error Resume Next
        Set Zz2 = Workbooks.Open("C:\Users\*****\Desktop\New folder\" & _
        Dev(lngCounter1) & "_B_Roles.xls")
        On Error GoTo 0
        If Zz2 Is Nothing Then ' <-- unable to find the file
            MsgBox "unable to find the specified file", vbCritical
            Exit Sub
        End If
        ' === Up to Here ===

        Zz2.Sheets(Dev(lngCounter1) & "_B_Roles").Range("A1").Value = "anirudh"

        ThisWorkbook.Sheets(Prod(lngCounter)).Range("A2").Value = Zz2.Sheets(Dev(lngCounter1) & "_B_Roles").Range("A1").Value
    Next lngCounter
Next lngCounter1

End Sub

Collectives™ on Stack Overflow

VBA Array error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related