title: Range versus Range Cells using Index Match in VBA
body: <p>I'm struggling to use the Range Cells method with Index Match in VBA. Using standard ranges works fine, but I'm having no luck with Range Cells. I must be failing to grasp something fundamental about Index Match. I have commented out the lines of code that fail. I appreciate any pointers the community can provide.</p>
  
  <pre><code>    Sub IndexMatchTroubleShooting()
  
  'dim worksheets
  Dim Source As Worksheet
  Dim Target As Worksheet
  
  'set worksheets
  Set Source = ThisWorkbook.Sheets("Source")
  Set Target = ThisWorkbook.Sheets("Target")
  
  'dim ranges
  Dim ValuesToPull As Range
  Dim TargetIDs As Range
  Dim SourceIDs As Range
  Dim MyRange As Range
  
  'using range &lt;-this works
  Set ValuesToPull = Source.Range("B1:B5682")
  Set TargetIDs = Target.Range("A1:A21")
  Set SourceIDs = Source.Range("A1:A5682")
  Set MyRange = Target.Range("B1:B21")
  
  'using range cells &lt;-this produces this error: "Run-time Error 1004 Method 'Range' of object '_Worksheet' failed"
  'Set ValuesToPull = Source.Range(Cells(1, 2), Cells(5682, 2))
  'Set TargetIDs = Target.Range(Cells(1, 1), Cells(21, 1))
  'Set SourceIDs = Source.Range(Cells(1, 1), Cells(5682, 1))
  'Set MyRange = Target.Range(Cells(1, 2), Cells(21, 2))
  
  'apply formula
  MyRange = Application.Index(ValuesToPull, Application.Match(TargetIDs, SourceIDs, 0))
  
  End Sub
  </code></pre>