This little macro looks at Sheet1. It produces a list of the formulas in
Sheet2. Column A will contain the address of the formula and column B will
contain the formula as a text string:
Sub ListFormulas()
Dim rr As Range
Dim s1 As Worksheet
Set s1 = Sheets("Sheet1")
Dim s2 As Worksheet
Set s2 = Sheets("Sheet2")
s1.Activate
Set rr = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
Dim sq As String
sq = Chr(39)
For Each r In rr
n = s2.Cells(Rows.Count, "A").End(xlUp).Row + 1
n = Application.WorksheetFunction.Max(2, n)
s2.Cells(n, "A").Value = r.Address
s2.Cells(n, "B").Value = sq & r.Formula
Next
End Sub
Macros are very easy to install and use:
1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window
If you save the workbook, the macro will be saved with it.
To use the macro from the normal Excel window:
1. ALT-F8
2. Select the macro
3. Touch Run
To remove the macro:
1. bring up the VBE window as above
2. clear the code out
3. close the VBE window
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
--
Gary''s Student - gsnu201001
"covingj" wrote:
I am trying to create a list of all of the formulas that are in a rather
large and complex(at least to me) workbook. I found where this topic was
answered back on 06/30/2006 (a Macro created by Chip), but I am unable to get
that solution to work. This could be for many reasons, primarily my inability
to understand and follow instructions. Any assistance would be appreciated. I
am using Excel 2003.
Thanks