List of formula in a workbook

Benali

New Member
Joined
Oct 21, 2009
Messages
4
Hi, I have a workbook with 25 worksheets with multiple cross-referencing formulae. I'd like to produce a list of all formulae within the workbook accompanied by the cell references that contain that formula. Thanks.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
Welcome to the Board!

Try the following macro ... with 25 worksheets however, it could take a while.

1. Create a new worksheet where the formulas will be written
2. On the new worksheet, enter "Sheet" in A1, "Cell" in B1, and "Formula" in C1 (these are simply column headers)
3. Change every occurrence of Sheet3 in the following macro to the new sheet's name
4. Run the macro

Code:
Sub Print_Formulas()
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Sheet3" Then
        For Each cell In ws.UsedRange
            If ws.Range(cell.Address).HasFormula = True Then
                LR = Sheet3.Range("A65536").End(xlUp).Address
                Sheet3.Range(LR).Offset(1, 0).Value = ws.Name
                Sheet3.Range(LR).Offset(1, 1).Value = "'" & ws.Range(cell.Address).Address
                Sheet3.Range(LR).Offset(1, 2).Value = "'" & ws.Range(cell.Address).Formula
            End If
        Next cell
    End If
Next ws
End Sub


Note: To install the macro, do the following:

1. Press ALT+F11 to open the VBE Window
2. In the VBE window, click Insert>Module
3. Copy the above code and paste it in the Module Window that opens
4. To run the code, click anywhere in the body of the macro and click the play button on the VBE menu bar.

Let me know how it works for you, or if you have any questions!
 
Last edited:

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
FYI - if you are not familiar with VBE, here is the code with comments that help explain what the macro is doing:

Code:
Sub Print_Formulas()

'GO THROUGH EACH WORKSHEET
For Each ws In ThisWorkbook.Worksheets
    
    'EXCLUDE SHEET3 (USED TO LOG THE FORMULAS)
    If ws.Name <> "Sheet3" Then
        
        'GO THROUGH EACH CELL WITHIN THE USED RANGE OF THE SHEET
        For Each cell In ws.UsedRange
            
            'IF THE CELL CONTAINS A FORMULA
            If ws.Range(cell.Address).HasFormula = True Then
                
                'FIND LAST USED ROW ON SHEET3
                LR = Sheet3.Range("A65536").End(xlUp).Address
                
                'PRINT SHEET NAME WHERE FORMULA IS FOUND IN COLUMN A
                Sheet3.Range(LR).Offset(1, 0).Value = ws.Name
                
                'PRINT CELL WHERE FORMULA IS FOUND IN COLUMN B
                Sheet3.Range(LR).Offset(1, 1).Value = "'" & ws.Range(cell.Address).Address
                
                'PRINT FORMULA IN COLUMN C
                Sheet3.Range(LR).Offset(1, 2).Value = "'" & ws.Range(cell.Address).Formula
            End If
        
        Next cell
    
    End If

Next ws

End Sub
 

Benali

New Member
Joined
Oct 21, 2009
Messages
4
Thanks Nogslaw - for the help and the welcome.

Your solution doesn't run - I'm getting a "Run Time error 424 - Object Required" message.
Something about the "LR = Sheet3.Range("A65536").End(xlUp).Address" line perhaps? Any ideas?
Thanks again.
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196

ADVERTISEMENT

I think Nogslaw suggested you should change all references to sheet3 to the name of the new sheet you've inserted to record all your info. I might also suggest that where it's not in quotes "" you change it to sheets("mySheet"), where mySheet is the name of the sheet, just as a precaution, or in case your sheet name has spaces.

HTH
 

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
Benali - what is the sheet name of the new worksheet you created? I'll make the necessary changes to the code so you can see how it should be.

Weaver - good point about the quotes ... didn't think about spaces in the sheet name.
 
Last edited:

Benali

New Member
Joined
Oct 21, 2009
Messages
4

ADVERTISEMENT

I inserted a new sheet called "Sheet3" - to try to keep it straightforward (no spaces in there).
 

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
Okay then, let's incorporate Weaver's suggestion into the code as well:

Code:
Sub Print_Formulas()

'GO THROUGH EACH WORKSHEET
For Each ws In ThisWorkbook.Worksheets
    
    'EXCLUDE SHEET3 (USED TO LOG THE FORMULAS)
    If ws.Name <> "Sheet3" Then
        
        'GO THROUGH EACH CELL WITHIN THE USED RANGE OF THE SHEET
        For Each cell In ws.UsedRange
            
            'IF THE CELL CONTAINS A FORMULA
            If ws.Range(cell.Address).HasFormula = True Then
                
                'FIND LAST USED ROW ON SHEET3
                LR = Sheets("Sheet3").Range("A65536").End(xlUp).Address
                
                'PRINT SHEET NAME WHERE FORMULA IS FOUND IN COLUMN A
                Sheets("Sheet3").Range(LR).Offset(1, 0).Value = ws.Name
                
                'PRINT CELL WHERE FORMULA IS FOUND IN COLUMN B
                Sheets("Sheet3").Range(LR).Offset(1, 1).Value = "'" & ws.Range(cell.Address).Address
                
                'PRINT FORMULA IN COLUMN C
                Sheets("Sheet3").Range(LR).Offset(1, 2).Value = "'" & ws.Range(cell.Address).Formula
            End If
        
        Next cell
    
    End If

Next ws

End Sub

Run this code. If you get the error message again, click "DEBUG" on the error message window. This will open the VBE editor window and highlight the line of code that the compiler does not like, and we can figure out why it doesn't like it.

It should work, but Weaver made a good point about properly referencing the sheet name.
 

Forum statistics

Threads
1,144,583
Messages
5,725,132
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top