Identify formulas containing constant numeric amounts

I posted up the following code on another Excel forum in response to a question.  If you have a workbook and you want to identify formulas where a constant value has been added/subtracted/multiplied/divided in to the formula of the form:

=SUM(A1:A10)+18.5

=23000 - VLOOKUP(A1,C1:D10,2,0)

then you can use the following code.  It will create a new worksheet in the workbook of interest with a hyperlink to the formula(s) in column A and the respective formula string in column B.  I found it quite useful myself to audit a few of my spreadsheets.

You need to copy the code into a standard module and run it whilst the workbook of interest (ie the one with the formulas) is active.  The code does not have to be in a module within the workbook of interest eg you could place it in your personal macro workbook.

Code is:

Sub IdentifyConst()
Dim cell As Range, rng As Range
Dim regex
Dim i As Long
Dim wsNew As Worksheet
Dim xlCalc As XlCalculation

With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
Set wsNew = Worksheets.Add(before:=Sheets(1))
wsNew.Range("A1:B1").Value = Array("Cell", "Formula")
Set regex = CreateObject("vbscript.regexp")
For i = 2 To Worksheets.Count
On Error Resume Next
Set rng = Worksheets(i).UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rng Is Nothing Then
With regex
.Pattern = "([\+\-\*\/]\d+\b)|(\=\d+\b)"
For Each cell In rng
If .test(cell.Formula) Then
With wsNew
.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Formula = "=HYPERLINK(""#'"" & """ & Worksheets(i).Name & _
"'!" & cell.Address & """,""'" & Worksheets(i).Name & "'!" & cell.Address & """)"
.Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = "'" & cell.Formula
End With
End If
Next cell
End With
Set rng = Nothing
End If
Next i
With Application
.Calculation = xlCalc
.EnableEvents = True
End With
End Sub

Hope it helps someone else.

Richard