title: VBA-excel dictionary
body: <p>Im copying cells from one sheet to another, finding and matching column header names and pasting to the correct cell. These column header names differ slightly per sheet, altough they contain the same data. My working code has a lot of repetition:</p>
  
  <pre><code>' sub that finds head in a specified worksheet and sets rngCol variable
  Sub rngByHead(Sheet As Worksheet, head As String)
  ' sub for copying data
  With Source1
       ' find and set producer, note name difference)
       Call rngByHead(Source1, "bedrijfsnaam")
       Dim producent As String
       producent = .Cells(docSource1.Row, rngCol).Value
       ' find and set Fase
       Call rngByHead(Source1, "Fase")
       Dim fase As String
       fase = .Cells(docSource1.Row, rngCol).Value
       ' find and set Status
       Call rngByHead(Source1, "Status")
       Dim status As String
       status = .Cells(docSource1.Row, rngCol).Value
       ' find and set versionnumber, note name difference
       Call rngByHead(Source1, "Wijziging")
       Dim versienummer As String
       versienummer = .Cells(docSource1.Row, rngCol).Value
  End With
  With Target
       ' find and write all variables to uploadlijst
       Call rngByHead(Target, "bestandsnaam")
       .Cells(cell.Row, rngCol).Value = bestand
       Call rngByHead(Target, "producent")
       .Cells(cell.Row, rngCol).Value = producent
       Call rngByHead(Target, "fase")
       .Cells(cell.Row, rngCol).Value = LCase(fase)
       Call rngByHead(Target, "status")
       .Cells(cell.Row, rngCol).Value = LCase(status)
       Call rngByHead(Target, "versienummer")
       .Cells(cell.Row, rngCol).Value = versienummer
  End With
  </code></pre>
  
  <p>I was trying a more cleaner option with a dictionary for matching the different header names in target and data sheets. I also created a secong dictionary to store those values under the specific keys. I keep getting errors on this code, both 424 object missing as ByRef argument type mismatch.</p>
  
  <pre><code>' Create dict
  Dim dict As Scripting.Dictionary
  ' Create dictValues
  Dim dictValues As Scripting.Dictionary
  Dim key As Object
      ' Add keys to dict
      dict("producent") = "Bedrijfsnaam"
      dict("fase") = "Fase"
      dict("status") = "Status"
      dict("versienummer") = "Wijziging"
      dict("documentdatum") = "Datum"
      dict("omschrijving1") = "Omschrijving 1"
      dict("omschrijving2") = "Omschrijving 2"
      dict("omschrijving3") = "Omschrijving 3"
      dict("discipline") = "Discipline"
      dict("bouwdeel") = "Bouwdeel"
      dict("labels") = "Labels"
  ' store values of sheet Source 1
  With Source1
      ' create second dictValues to store values for each key
      Set dictValues = New Scripting.Dictionary
      ' loop through keys in dict, this line gives error 424
      For Each key In dict.Keys
            ' use dict to pass right value to rngByHead sub
            Call rngByHead(Target, dict(key))
            ' store value of cell to dictValues under same key
            dictValues(key) = .Cells(cell.Row, rngCol).Value
      Next key
  End With
  ' set values to sheet Target
  With Target
      ' loop through keys in dict
      For Each key In dict.Keys
            ' use dict to pass value of key item to rngByHead sub
            Call rngByHead(Target, key)
            ' set value of cell to dictValues
            .Cells(cell.Row, rngCol).Value = dictValues(key)
      Next key
  End With
  </code></pre>
  
  <p>What am I doing wrong? I'm new to vba dictionary and can't figure this one out. Thanks for your help!</p>