Help with Run-time error '9': Subscript out of range

[WyattEarp20]

Hey guys, below is my code and it works fine until it gets to the .Cells(ECN_Row, I + 2) = ECNCollection.Item(I) line but then it debugs and gives me the Run-time error '9': Subscript out of range. The line in red is the one that it debugs on. Any help is much appreciated.

Sub Export()
    Dim ECN As String
    Dim ECNCollection As New Collection
    ECN = Range("K3").Value
'Save values in Order of Columns to be placed in
    ECNCollection.Add Range("C5").Value
    ECNCollection.Add Range("B4").Value
    ECNCollection.Add Range("E33").Value
    ECNCollection.Add Range("D3").Value
    ECNCollection.Add Range("D21").Value
    ECNCollection.Add Range("I21").Value
'To save with correct file name
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\walkerja\Documents\ECN\" & ECN & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


'To open ECN List
   find_or_create_ECN ECN, ECNCollection, "C:\Users\walkerja\Documents\ECN\ECN 2014.xls", "C:\Users\walkerja\Documents\ECN\" & ECN & ".xlsm"
   Set ECNCollection = Nothing
End Sub




Sub find_or_create_ECN(ECN As String, ECNCollection As Collection, wb_path As String, ecn_file_path As String)
      Dim WB As Excel.Workbook
      Dim LCell As Range
      Dim L_Row As Long
      Dim ECN_Found As Boolean
      Dim ECN_Row As Long
      Dim I As Integer
      Set WB = Workbooks.Open(wb_path)
      With WB.Worksheets("CONTENTS")
          L_Row = .Cells(.Rows.Count, "A").End(xlUp).Row
          For Each LCell In .Range("$A$2", "$A$" & L_Row)
             If UCase(Trim(LCell.Value)) = UCase(Trim(ECN)) Then
                 ECN_Found = True
                 ECN_Row = LCell.Row
                 Exit For
             End If
          Next LCell
          If Not (ECN_Found) Then
             ECN_Row = L_Row + 1
          End If
          .Hyperlinks.Add .Cells(ECN_Row, 1), ecn_file_path, TextToDisplay:=ECN
          For I = 0 To ECNCollection.Count
             [COLOR=#ff0000].Cells(ECN_Row, I + 2) = ECNCollection.Item(I)[/COLOR]
          Next I
      End With
      WB.Save
      WB.Close
      Set WB = Nothing
End Sub

 

[JLGWhiz]

The Subcript out of Range message indicates that one of the variables in that line was not detected by VBA. I suspect it is the ECNcollection. When you get the error message and the line is highlighted, hover the mouse pointer over the variables to activate 'Tool Tips' which will show you the value of the variable at that point in the execution. If the value shows as empty or no value is displayed then the variable was not detected. In this case, since those values were imported from another macro, the problem would lie in the first macro.

 

[WyattEarp20]

This problem has been resolved. I am new to posting on here so I'm not sure if there is a way to delete it or not. Thanks for the help.

 

[JLGWhiz]

Only the moderators can delete postings. But the feedback is appreciated. It would also be appreciated if the resolution was posted so that others could learn from your experience.

 

[WyattEarp20]

Below is the correct code. The two lines in green are the only ones that had changes made.

Sub Export()
    Dim ECN As String
    Dim ECNCollection As New Collection
    ECN = Range("K3").Value
'Save values in Order of Columns to be placed in
    ECNCollection.Add Range("C5").Value
    ECNCollection.Add Range("B4").Value
    ECNCollection.Add Range("E33").Value
    ECNCollection.Add Range("D3").Value
    ECNCollection.Add Range("D21").Value
    ECNCollection.Add Range("I21").Value
'To save with correct file name
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\walkerja\Documents\ECN\" & ECN & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


'To open ECN List
   find_or_create_ECN ECN, ECNCollection, "C:\Users\walkerja\Documents\ECN\ECN 2014.xls", "C:\Users\walkerja\Documents\ECN\" & ECN & ".xlsm"
   Set ECNCollection = Nothing
End Sub




Sub find_or_create_ECN(ECN As String, ECNCollection As Collection, wb_path As String, ecn_file_path As String)
      Dim WB As Excel.Workbook
      Dim LCell As Range
      Dim L_Row As Long
      Dim ECN_Found As Boolean
      Dim ECN_Row As Long
      Dim I As Integer
      Set WB = Workbooks.Open(wb_path)
      With WB.Worksheets("CONTENTS")
          L_Row = .Cells(.Rows.Count, "A").End(xlUp).Row
          For Each LCell In .Range("$A$2", "$A$" & L_Row)
             If UCase(Trim(LCell.Value)) = UCase(Trim(ECN)) Then
                 ECN_Found = True
                 ECN_Row = LCell.Row
                 Exit For
             End If
          Next LCell
          If Not (ECN_Found) Then
             ECN_Row = L_Row + 1
          End If
          .Hyperlinks.Add .Cells(ECN_Row, 1), ecn_file_path, TextToDisplay:=ECN
          [COLOR=#008000]For I = 1 To ECNCollection.Count
             .Cells(ECN_Row, I + 1) = ECNCollection.Item(I)[/COLOR]
          Next I
      End With
      WB.Save
      'WB.Close
      Set WB = Nothing
End Sub

 

[JLGWhiz]

Thanks for the feedback,
Regards, JLG