I have a function that returns a dictionary that is made up of 2 columns from a workbook, one for key & the other the value. I'm able to loop through the dictionary unless I close the workbook using ActiveWindow.Close. Once I do that I'm able to confirm that the count of the dictionary is still correct using .Count, but when I loop through the dict I receive the 424 error. Object Required.
Sub Main()
Dim trackingNumbers As Scripting.Dictionary
Set trackingNumbers = GetTrackingNumbers
Dim key As Variant
For Each key In trackingNumbers.Keys
Debug.Print key, trackingNumbers(key)
Next key
End Sub
Function GetTrackingNumbers() As Scripting.Dictionary
Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary
Dim wb As Workbook
Dim ws As Worksheet
Dim lastRow As Integer
Dim i As Integer
Application.DisplayAlerts = False
Set wb = Workbooks.Open("C:\\Path.xls", True, True)
Application.DisplayAlerts = True
Set ws = Sheets(wb.Worksheets(1).Name)
ws.Activate
lastRow = ws.Cells(ws.Rows.Count, 1) _
.End(xlUp).Row ' Getting # of rows
Do Until i > lastRow ' iterating over each row
If Cells(i, indexArr(0)) <> "" Then
dict.Add Cells(i, 1), _
Cells(i, 10)
End If
i = i + 1
Loop
ActiveWindow.Close
Set GetTrackingNumbers = dict
End Function
dict.Add Cells(i, 1), _
Cells(i, 10)
is using Range
objects, which no longer exist when their parent workbook is closed.
You likely want:
dict.Add Cells(i, 1).Value, _
Cells(i, 10).Value