title: VBA Dictionary with Dynamic Arrays
body: <p>I'm trying to create A dynamic dictionary that contains dynamic arrays.</p>
  
  <p>Sample Row from spreadsheet:</p>
  
  <pre><code>Facility Name|Contact Name|Contact Role
  </code></pre>
  
  <p>The relationship between facilities and contacts are M2M. I would like to recreate a sheet that looks like this:</p>
  
  <pre><code>Contact Name| Facility1 - role, Facility2 - role
  </code></pre>
  
  <p>What I would like to do is create a dictionary of names with unique names serving as keys</p>
  
  <pre><code>New Dictionary  Names(name)
  </code></pre>
  
  <p>The values for Names(name) will be an array of all the row numbers where this name appears. For instance, say "Joe Rose" appears in rows 3, 7 and 9:</p>
  
  <pre><code>names("Joe Rose") = [3,7,9]
  </code></pre>
  
  <p>I know how I could do this in JS, Python, PHP, but VBA is driving me crazy!</p>
  
  <p>Here is what I kind of got so far:</p>
  
  <pre><code>Dim names As Dictionary
  Set names = New Dictionary
  
  Dim name
  
  For i=1 To WorkSheets("Sheet1").Rows.Count
    name = WorkSheets("Sheet1").Cells(i,2)
    If Not names(name) Then
      names(name) = i
    Else
      'help!
      'names(name)) push new i, maybe something with redim preserve?
    End If
  Next i
  </code></pre>
  
  <p>Even just pointing me to some article that I could reference would be great! VBA has been so frustrating coming from a PHP background!</p>
  
  <p>Thank you</p>