Create a list of formulas contained in a Workbook
 

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

Create a list of formulas contained in a Workbook
 

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

Create a list of formulas contained in a Workbook
 

Thank you for your help. This worked for me and I only ended up with 34 pages
of formulas.

These forums have been a life saver and provided many solutions for me the
past several years. Thanks for all the hard work.

"Gary''s Student" wrote:

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

Create a list of formulas contained in a Workbook
 

You are very welcome. Thanks for the feedback!
--
Gary''s Student - gsnu201001


"covingj" wrote:

Thank you for your help. This worked for me and I only ended up with 34 pages
of formulas.

These forums have been a life saver and provided many solutions for me the
past several years. Thanks for all the hard work.

"Gary''s Student" wrote:

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