title: Range.Find method returning unexpected value
body: <p>This is strange. According to MSDN (and several questions on StackOverflow), the Range.Find method in VBA is supposed to return <code>Nothing</code> when the specified value is not found in the specified range. However, when I run the following code, if the value is not found, the Range.Find method doesn't return <code>Nothing</code>. It doesn't return <em>anything</em>.</p>
  
  <p>Please assume all variables are properly defined. VBA is in an Access 2013 module with Microsoft Office 15.0 Object Library referenced.</p>
  
  <pre><code>Dim rng As Excel.Range
  Dim rngFound As Excel.Range
  ' ...
  For i = 0 To UBound(strHeader)
      Set rngFound = rng.Find( _
          What:=strHeader(i), _
          After:=rng(rng.Cells.Count), _
          LookAt:=xlWhole, _
          MatchCase:=True _
      )
      If rngFound Is Nothing Then
          bolValid = False
          Exit For
      ElseIf i = UBound(strHeader) Then
          bolValid = True
      End If
  Next i
  </code></pre>
  
  <p>If the string in <code>strHeader(i)</code> isn't found in range <code>rng</code>, then the variable <code>rngFound</code> should be set to <code>Nothing</code>. But, it's not.</p>
  
  <p>Suppose the value of <code>rngFound.Address</code> from an earlier iteration is "$J$1". Now suppose that the value of the current iteration is not found in <code>rng</code>. Range.Find <em>should</em> return <code>Nothing</code>, but when queried in the Immediate window, <code>rngFound.Address</code> is still "$J$1".</p>
  
  <p>Any idea what might be causing this odd behaviour?</p>
  
  <p><strong>Edit:</strong> Full function code, as requested.</p>
  
  <pre><code>Private Function CheckIntegrity(FileName As String) As Boolean
      Dim wb As Excel.Workbook
      Dim ws As Excel.Worksheet
      Dim rng As Excel.Range
      Dim rngFound As Excel.Range
      Dim strHeader() As String
      Dim i As Integer
      Dim bolValid As Boolean
  
      On Error GoTo PROC_ERR
  
      ReDim strHeader(9)
      strHeader(0) = "String1"
      strHeader(1) = "String2"
      strHeader(2) = "String3"
      strHeader(3) = "String4"
      strHeader(4) = "String5"
      strHeader(5) = "String6"
      strHeader(6) = "String7"
      strHeader(7) = "String8"
      strHeader(8) = "String9"
      strHeader(9) = "String10"
  
      Set wb = OpenWorkbook(FileName, True)
      If Not wb Is Nothing Then Set ws = wb.Worksheets(1)
      If Not ws Is Nothing Then Set rng = ws.Range("A1:Z1")
  
      If Not rng Is Nothing Then
          For i = 0 To Ubound(strHeader)
              Set rngFound = rng.Find( _
                  What:=strHeader(i), _
                  After:=rng(rng.Cells.Count), _
                  LookAt:=xlWhole, _
                  MatchCase:=True _
              )
              If rngFound Is Nothing Then
                  bolValid = False
                  Exit For
              ElseIf i = Ubound(strHeader) Then
                  bolValid = True
              End If
          Next i
      End If
  
      wb.Close SaveChanges:=False
  
      CheckIntegrity = bolValid
      PROC_EXIT:
      Set wb = Nothing
      Set ws = Nothing
      Set rng = Nothing
      Set rngFound = Nothing
      Erase strHeader
      i = 0
      bolValid = False
      Exit Function
  
      PROC_ERR:
      MsgBox "Error encountered. " &amp; Err.Number &amp; " " &amp; Err.Description
      Resume PROC_EXIT
  End Function
  </code></pre>
  
  <p>The value of rngFound is not being set to <code>Nothing</code> if the element of strHeader is not found.</p>