RyanDev.com

Here is some sample code that will document the formulas you have in your worksheet.  It will create a separate sheet named Formulas that will list all of the formulas being used.

---

Public Sub DocumentFormulas()
‘——————————————————————————————
‘Created by Ryan Brown
‘This is a sample that will document the formulas from the sheet that it is run on
‘It will create a sheet called Formulas and you will get an error if a sheet named formulas
‘already exists.
‘It will put both the cell address and the cell formula into 2 separate columns
‘——————————————————————————————
Dim SelRange As Range
Dim oCell As Object
Dim strSheet As String
Dim i As Integer

strSheet = ActiveSheet.Name

’store all of the formulas on the given sheet into a range variable
Set SelRange = Selection.SpecialCells(xlCellTypeFormulas, 23)

ActiveWorkbook.Sheets.Add
ActiveSheet.Name = “Formulas”

i = 1   ’start at cell A1

‘loop through each cell in the range of formulas and print the address and formula
For Each oCell In SelRange.Cells
    Sheets(”Formulas”).Cells(i, 1) = oCell.address
    Sheets(”Formulas”).Cells(i, 2).NumberFormat = “@”   ‘change cell format first to text so formula shows
    Sheets(”Formulas”).Cells(i, 2) = oCell.Formula
    i = i + 1
Next oCell
End Sub

---