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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
I inserted a new sheet called "Sheet3" - to try to keep it straightforward (no spaces in there).
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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
Back
Top