VBA Dictionary Run Time Error 1004

Question

I am trying to sort rows based on names and put them in their own worksheets. I am using a Dictionary in order to keep track of the current row for each. I have included a reference to the Microsoft Scripting Runtime. Every time I run, it gets to the line indicated in the code and then throws a Run-Time Error 1004, Application-defined or object defined error. In addition, when I watch nameDict The value is: <Object variable or With block variable not set> however the type is Dictionary. Is this because it's not being initialized properly? I've read several different ways to initialize a dictionary and am confused on the best way. All other variables and functions work correctly as they've been tested independently and in other subs.

Private Sub sortTeamWork()
Dim nameDict As New Scripting.Dictionary
Dim index As Integer
    index = getTargetColumn("Last Name")
    For i = CInt(getUsedRows()) To 2 Step -1
        name = Cells(i, index - 1) + " " + Cells(i, index)
        'The below line is where the error occurs
        If nameDict.Exists(name) Then
            nameDict.Items(name) = nameDict.Items(name) + 1
            Cells(i, index).EntireRow.Copy Destination:=Worksheets(name).Range("A" & nameDict(name))
        Else
            nameDict.Items(name) = 2
            Cells(i, index).EntireRow.Copy Destination:=Worksheets(name).Range("A" & nameDict(name))
        End If
    Next i

End Sub

There is a header on the worksheet I'm trying to copy the row to so it should start at row 2 and increment from there. Any help would be appreciated.

Dim ... As New can cause unexpected problems if you're not aware of all the implications. I can't fathom a reason for it to be Nothing with such code, but try splitting the declaration and the assignment into two instructions. — Mathieu Guindon
Same issue, Dim nameDict As Variant Set nameDict = CreateObject("Scripting.Dictionary") is how I split it and it still had the 1004 error. — CS2016
You have a reference to the scripting runtime, do Dim nameDict As Scripting.Dictionary, then Set nameDict = New Scripting.Dictionary. Don't introduce Variant there, you'll only lose intellisense and turn perfectly valid early-bound code into late-bound code for no reason. That said, are you sure the error is thrown on the line you think it is? nameDict being Nothing with Dim...As New is literally impossible, and would be throwing error 91, not 1004. — Mathieu Guindon
What's getUsedRows doing? Also consider using the & string concatenation operator instead of +, and try to work off an explicit worksheet object rather than implicitly off ActiveSheet. — Mathieu Guindon
name, a reserved word? try using sName instead, I'm assuming it is meant to be a string. To ensure the code is doing what you expect use an asserrtion, so Debug.Assert TypeName(sName) = "String" might shed light. — S Meaden

Engineer Engineer · Accepted Answer · 2018-05-15T18:45:06

You must either add reference by going to your VBA project -> Tools -> References: Tick Microsoft Scripting Runtime. and use Dictionary types

or you can do a latebinding like

Dim nameDict As Object
set nameDict = createObject("Scripting.Dictionary")

make sure you have Option Explicit is set on top of the module. This will point out all compiler errors.

VBA Dictionary Run Time Error 1004

1 Answers