title: Runtime error '13': Type Mismatch in Excel VBA
body: <p>I have some VBA code which compares a column in two worksheets. When it finds a difference, it copies the entire row to a third worksheet. It works as I want it to, except that I often (but not always) get a Type Mismatch error. </p>
  
  <p>The columns I'm comparing in each sheet are helper columns formatted as text, created by first joining all of the row's contents into one helper column (via =A1&amp;B1&amp;C1&amp;...), and then copying the results of that formula and pasting the VALUES into a second helper column which is also formatted as text. Then I compare the 2nd helper column of each sheet.</p>
  
  <p>The values of the helper columns are long strings of letters and numbers (names,phone numbers, addresses, etc.), but since they're formatted as text, shouldn't the types be the same? The error occurs on the line:</p>
  
  <pre><code>Set fValue = .Find(cell.Value, LookIn:=xlValues)
  </code></pre>
  
  <p>but I don't know why. Can anyone shed some light?</p>
  
  <pre><code>Sub CompareRosters()
  Dim lRow, lrow2 As Long
  Dim fValue As Range
  
  Sheets("RosterChanges").Range("RosterChanges").Clear
  Sheets("NewRoster").Select
  lRow = Range("CK1").End(xlDown).Row
  lrow2 = Sheets("LastRoster").Range("CK1").End(xlDown).Row
  
  For Each cell In Range("CK2:CK" &amp; lRow)
      With Sheets("LastRoster").Range("CK2:CK" &amp; lrow2)
  
          Set fValue = .Find(cell.Value, LookIn:=xlValues)
          If fValue Is Nothing Then
              cell.EntireRow.Copy Sheets("RosterChanges").Range("A" &amp; Rows.Count).End(xlUp).Offset(1, 0)
          End If
      End With
  
  Next cell
  
  End Sub
  </code></pre>
  
  <p>A clue: The Mismatch Error only occurs when the text I'm comparing exceeds 255 characters. I assume it's because a #VALUE error is returned, which in turn, triggers the Mismatch Error. How can I avoid this?</p>