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

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext