Determining if a tab has any formulas

[bradjedis]

Greetings,

I have a workbook with many sheets. II have used formulas on many of them and I have tried to copy/paste the results as text. However I may have missed some. Is there a method available to parse through each tab to see if there are any formulas remaining?

Thanks,
BRad

[HansV]

Run this macro:

Code: Select all

Sub FindFormulas()
    Dim wsh As Worksheet
    Dim rng As Range
    Dim msg As String
    On Error Resume Next
    For Each wsh In Worksheets
        Set rng = Nothing
        Set rng = wsh.Cells.SpecialCells(xlCellTypeFormulas)
        If Not rng Is Nothing Then
            msg = msg & vbCrLf & wsh.Name & ": " & rng.Address
        End If
    Next wsh
    If msg = "" Then
        MsgBox "No remaining formulas!", vbInformation
    Else
        MsgBox "Formulas on:" & msg, vbInformation
    End If
End Sub

[bradjedis]

Absolutely Beautiful. Worked great and found some hidden away that would have taken quite a while to locate.

Thanks!

[snb]

Unfamiliar with ?

Code: Select all

Sub M_snb()
  For Each it In ThisWorkbook.Windows
     it.DisplayFormulas = True
  Next
End Sub

[PJ_in_FL]

Unfamiliar with ?

Code: Select all

Sub M_snb()
  For Each it In ThisWorkbook.Windows
     it.DisplayFormulas = True
  Next
End Sub

I got no output running this macro against a workbook with three sheets, one of which contained a formula.

What should one expect?

[HansV]

snb's macro doesn't output anything, it makes Excel display formulas in cells instead of their results.
Unfortunately, it will affect only those sheets on which you currently have a window open, not all sheets.
And you still have to inspect those sheets visually to check if they contain formulas...

[snb]

Alternatively:

Code: Select all

Sub M_snb()
    For Each it In Sheets
       it.Activate
       ActiveWindow.DisplayFormulas = True
    Next
End Sub