Attribute VB_Name = "Module1"
Option Explicit

Sub findCalcBlunder()
   '// courtesy of www.codematic.net - no warranties blah blah...
   '// feel free to modify adapt and improve - post better code at smurfonspreadsheets.co.uk
   '// checks displayed no == cell value.
   '// rushed and flaky - likely to fail and or be slow on big sheets
   Dim rFormulas As Range
   Dim clTest As Range
   Dim ws As Worksheet
   Dim lBlundercount As Long
   Dim bHash As Boolean
   Dim sText As String
   
   On Error GoTo err_h
   Application.Calculation = xlCalculationManual

   For Each ws In ActiveWorkbook.Worksheets
      Set rFormulas = safewrapFormulas2(ws) 'limit of 8000 ranges?
      If Not rFormulas Is Nothing Then
         For Each clTest In rFormulas.Cells
            sText = clTest.Text
            If InStr(1, sText, "#", vbTextCompare) = 0 Then
               If IsNumeric(sText) Then
                  If CLng(sText) <> CLng(clTest.Value) Then
                     lBlundercount = lBlundercount + 1
                     Debug.Print "sht: " & ws.Name, _
                                 "cell: " & clTest.Address, _
                                 "formula: " & clTest.Formula, _
                                 "text val: " & sText, _
                                 "real val: " & clTest.Value2
                  Else
                     'leave it
                  End If
               Else
                  'nan leave it
               End If
            Else
               bHash = True ' col width
            End If
         Next clTest
      Else
         'blank sheet
      End If
   Next ws
   
   If lBlundercount > 0 Then
      MsgBox lBlundercount & " potential errors were found" & vbCrLf & _
      "Check the immediate window for more info", vbExclamation
   Else
      MsgBox "no obvious errors were found this time", vbExclamation
   End If
   
   If bHash Then
      MsgBox "Some cells could not be checked as the values were not displayed" & vbCrLf & _
      "This is probably due to some columns being so narrow they display '###'", vbExclamation
   Else
      'no hash probs spotted
   End If

Exit_proc:
   Application.Calculation = xlCalculationAutomatic
   Exit Sub

err_h:
   MsgBox "Error " & Err.Number & vbCrLf & _
      " (" & Err.Description & ") " & vbCrLf & "in findCalcBlunder"
   Resume Exit_proc
   
End Sub

Private Function safewrapFormulas2(s As Worksheet) As Range
   On Error GoTo err_h
   Set safewrapFormulas2 = s.Cells.SpecialCells(xlCellTypeFormulas)
exit_here:
   Exit Function
err_h:
Set safewrapFormulas2 = Nothing
End Function