List of formula in a workbook

[Benali]

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.

 

[Nogslaw]

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

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!

 

[Nogslaw]

FYI - if you are not familiar with VBE, here is the code with comments that help explain what the macro is doing:

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]

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]

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]

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.

 

[Benali]

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

 

[Nogslaw]

Okay then, let's incorporate Weaver's suggestion into the code as well:

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.

 

[Benali]

That's done the job. Many thanks, excellent help.