title: Referring to Dynamic Named Ranges in VBA
body: <p>I'm having troubling referring to a Dynamic Name Range in VBA.<br>
  My ranges are defined as</p>
  
  <pre>    =OFFSET(Sheet!$B$2,0,0,COUNTA(Sheet!$B:$B)-1,1)</pre>
  
  <p>My code should search one range for all entries in another range, the intention being that any missing entries will be added. So far I have</p>
  
  <pre>
      Sub UpdateSummary()
      Dim Cell As Range
      Dim rngF As Range
      Set rngF = Nothing
  
      ' Step through each cell in data range
      For Each Cell In Worksheets("Aspect").Range("A_Date")
          ' search Summary range for current cell value
          Set rngF = Worksheets("Summary").Range("Sum_Date").Find(Cell.Value) // Does not work
          If rngF Is Nothing Then
              ' Add date to Summary
          End If
          Set rngF = Nothing
      Next Cell
      End Sub </pre>
  
  <p>The For loop seems to work ok. However, using the .Find method is giving me an error message.</p>
  
  <pre>    Application-defined or object-defined error</pre>
  
  <p>It does work if I replace the named range with a specific range ($B$2:$B$5000), so it seems to be down to how the named range is being passed.<br>
  Any ideas would be appreciated.<br><br>
  Thanks.</p>