Flagging constants
 

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

Flagging constants
 

NMD,
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
http://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 "+#" Or strPart Like "-#" Then
rngCell.Interior.ColorIndex = 40
Exit For
End If
Next 'lngN
Next 'rngCell
Application.ScreenUpdating = True
Set rngAll = Nothing
Set rngCell = Nothing
End Sub
'------------


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

Flagging constants
 

Jim,

Thank you for your response. With a little bit of work I got the code
to work great. Thanks.
Jim Cone wrote:
NMD,
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
http://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 "+#" Or strPart Like "-#" Then
rngCell.Interior.ColorIndex = 40
Exit For
End If
Next 'lngN
Next 'rngCell
Application.ScreenUpdating = True
Set rngAll = Nothing
Set rngCell = Nothing
End Sub
'------------


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