VBA Code

Clearing Data

There is a way to clear a worksheet model of data and leave the formulas intact.

Range("A1").SpecialCells(Type:=xlCellType.xlCellTypeConstants, _ 
                         Value:=xlSpecialCellsValue.xlNumbers).ClearContents. 

xlCVError.xlErrRef 

Removing a Formula

Selection.Formulas = Selection.Value 
Range("D4").Formula = "=SUM(B5:D20)" 
Range("F5").Formula = "=5-10*RAND()" 
Cells(1,2).Formula = "=G20" 

Range("E2").FormulaR1C1 = "=R[-2]C[2]-R[-2]C[3]" 
Range("E2").FormulaR1C1 = "=RC[-1]+RC[-3]-(8/24)" 

Entering dates

Range("A1").FormulaR1C1 = "=DATEVALUE(""01/07/77"")+30 
Range("A1").NumberFormat = "mmm-dd-yy" 

Converting Formulas between A1 and R1C1

Converting Formulas

Public Function FORMULAGET(ByVal rgeCell As Range, _ 
                   Optional ByVal bCellAddressPrefix As Boolean = False) As String 
   
   If VarType(rgeCell) = 8 And Not rgeCell.HasFormula Then 
      FORMULAGET = "'" & rgeCell.Formula  
   Else 
      FORMULAGET = rgeCell.Formula 
   End If 
   If rgeCell.HasArray Then 
      FORMULAGET = "{" & rgeCell.Formula & "}" 
   End If 
   
   If bCellAddressPrefix = True Then 
      FORMULAGET = rgeCell.Address(0, 0) & ":  " & FORMULAGET 
   End If 
End Function