# 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
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

• 245 8
• 172 6
• 170 7
• 158 7
• 2,755 52
• 291 16
• 201 4
• 339 6
• 1,216 24
• 200 5
• 45 1
• 263 13
• 383 15
• 128 2
• 111 2
• 391 8
• 106 4
• 136 2
• 241 6
• 978 12
• 1,129
• 585
• 388
• 294
• 195
• 179
• 173
• 169
• 162
• 151