software

Hello All,

Part of my job is maintenance on a VERY large excel financial workbook.
Well, over time it has been necessary to add constants to some formulas
to get the historical projections to match the historical data (for
example a cell might read =C4-H7 10.2). After a while we have lost
track of all of the all constants we have added to equations. Is there
a way to flag these functions (in my example the 10.2). Thank you in
advance.

NMDNMD,
Experiment with this VBA code on a test sheet.
It will color cells (on the active sheet) that have formulas that
contain the addition or subtraction of numbers.
Jim Cone
San Francisco, USA
www.realezsites.com/bus/primitivesoftware

'-------------------
Sub FindTheNumbersInFormulas()
Dim lngN As Long
Dim rngAll As Excel.Range
Dim rngCell As Excel.Range
Dim strForm As String
Dim strPart As String

Set rngAll = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
Application.ScreenUpdating = False
For Each rngCell In rngAll
strForm = rngCell.Formula
For lngN = 1 To Len(strForm)
strPart = Mid$(strForm, lngN, 2)
If strPart Like quot; #quot; Or strPart Like quot;-#quot; Then
rngCell.Interior.ColorIndex = 40
Exit For
End If
Next 'lngN
Next 'rngCell
Application.ScreenUpdating = True
Set rngAll = Nothing
Set rngCell = Nothing
End Sub
'------------gt; wrote in message...
Hello All,
Part of my job is maintenance on a VERY large excel financial workbook.
Well, over time it has been necessary to add constants to some formulas
to get the historical projections to match the historical data (for
example a cell might read =C4-H7 10.2). After a while we have lost
track of all of the all constants we have added to equations. Is there
a way to flag these functions (in my example the 10.2). Thank you in
advance.
NMD

Jim,

Thank you for your response. With a little bit of work I got the code
to work great. Thanks.
Jim Cone wrote:
gt; NMD,
gt; Experiment with this VBA code on a test sheet.
gt; It will color cells (on the active sheet) that have formulas that
gt; contain the addition or subtraction of numbers.
gt; Jim Cone
gt; San Francisco, USA
gt; www.realezsites.com/bus/primitivesoftware
gt;
gt; '-------------------
gt; Sub FindTheNumbersInFormulas()
gt; Dim lngN As Long
gt; Dim rngAll As Excel.Range
gt; Dim rngCell As Excel.Range
gt; Dim strForm As String
gt; Dim strPart As String
gt;
gt; Set rngAll = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
gt; Application.ScreenUpdating = False
gt; For Each rngCell In rngAll
gt; strForm = rngCell.Formula
gt; For lngN = 1 To Len(strForm)
gt; strPart = Mid$(strForm, lngN, 2)
gt; If strPart Like quot; #quot; Or strPart Like quot;-#quot; Then
gt; rngCell.Interior.ColorIndex = 40
gt; Exit For
gt; End If
gt; Next 'lngN
gt; Next 'rngCell
gt; Application.ScreenUpdating = True
gt; Set rngAll = Nothing
gt; Set rngCell = Nothing
gt; End Sub
gt; '------------
gt;
gt;
gt; gt; wrote in message...
gt; Hello All,
gt; Part of my job is maintenance on a VERY large excel financial workbook.
gt; Well, over time it has been necessary to add constants to some formulas
gt; to get the historical projections to match the historical data (for
gt; example a cell might read =C4-H7 10.2). After a while we have lost
gt; track of all of the all constants we have added to equations. Is there
gt; a way to flag these functions (in my example the 10.2). Thank you in
gt; advance.
gt; NMD