Determining if a tab has any formulas

bradjedis
4StarLounger
Posts: 538
Joined: 30 Mar 2010, 18:49
Location: United States

Determining if a tab has any formulas

Post by 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

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Determining if a tab has any formulas

Post by 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
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 538
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Determining if a tab has any formulas

Post by bradjedis »

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

Thanks!

snb
4StarLounger
Posts: 573
Joined: 14 Nov 2012, 16:06

Re: Determining if a tab has any formulas

Post by snb »

Unfamiliar with ?

Code: Select all

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

PJ_in_FL
5StarLounger
Posts: 1098
Joined: 21 Jan 2011, 16:51
Location: Florida

Re: Determining if a tab has any formulas

Post by PJ_in_FL »

snb wrote:
02 Apr 2024, 19:57
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?
PJ in (usually sunny) FL

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Determining if a tab has any formulas

Post by 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...
Best wishes,
Hans

snb
4StarLounger
Posts: 573
Joined: 14 Nov 2012, 16:06

Re: Determining if a tab has any formulas

Post by snb »

Alternatively:

Code: Select all

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