Option Explicit

Sub ReplaceWithValue()
    'Replace formulas in selection with values, ignore cells with errors
    ' 2006-05-24 David McRitchie,  for Brian Desmond in  MVP group
    ' /code/makevalues.txt   when and if documented
    Dim rng As Range, cell As Range
    Dim buggers As Long
    Set rng = Nothing
    On Error Resume Next
    buggers = Selection.SpecialCells(xlCellTypeFormulas, 16).Count 'errors
    Set rng = Selection.SpecialCells(xlCellTypeFormulas, 7)
    On Error GoTo 0
    If rng Is Nothing Then
       MsgBox "No formulas w/o errors found in selection " _
          & "for conversion, found " & buggers & " cells with errors"
       Exit Sub
    End If
    rng.Select   '-- if you want selection to change
    buggers = rng.Areas.Count
    If buggers = 1 And rng.Count > 8199 Then
      MsgBox "sorry not worth the risk of your data, nor the length of " _
        & "time to check each cell, " _
        & "possibly KB 832293 more than 8,192 non-contiguous cells"
      GoTo bugout
    End If
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Each cell In rng
       cell.Value = cell.Value
    Next cell
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
bugout:
End Sub
Sub Test_SP_create_crap()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   For Each cell In Range("A1:F3097")
     cell.Formula = "=EVEN(ROW())/(ROW()-EVEN(ROW())-COLUMN()+ODD(COLUMN()))"
   Next cell
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

'Testing of Special Cells, following forula produces 50% errors
' note of this test has been added to  /dmcritchie/excel/proper.htm
Sub Test_SP_Areas()
  'with the above preparation, you can select 2 columns run this okay
  'but one more column or one more area and it will fail to make
  'a correct specialcells due to KB http://support.microsoft.com/?kbid=832293
  Selection.SpecialCells(xlCellTypeFormulas, 7).Select
End Sub