Finding constants in formulas

Hello, I have a big spreadsheet which should be purely formula based however
I need to check that there are no constants (i.e. hard coded values) typed
into the formulas for example '=sum(a1:b10)+25000'.

Is there a way to check each cell reference to be totally sure that there
are no hardcodings? vb solution perhaps? I know you can switch to view
formulas on the spreadsheet but as there are so many and some are quite long
it isnt always easy to spot via the ***** eye. Any help much appreciated.

Thanks

Brian
Manchester, England

Hello Brian,

As far as I know the only to do it is using the slow and painful method of visual inspection. It is difficult for me to imagine a scenario where all constants would be bad and needed to be removed. The formula are strings that are brokendown by Excel's interpreter, which is a program similar to a compiler. I don't see using programming as a viable means to a solution in this case. Sorry.

Sincerely,
Leith Ross

Hi Brian,

Try:

'=================>>
Sub ConstantsInFormulas()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim arr As Variant
Dim sStr As String
Dim i As Long

Set WB = ActiveWorkbook '<<======== CHANGE
Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
Set rng = SH.UsedRange '<<======== CHANGE

On Error Resume Next 'In case no formulas!
Set rng = rng.SpecialCells(xlFormulas)
On Error GoTo 0

arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")

If Not rng Is Nothing Then
For Each rCell In rng.Cells
For i = LBound(arr) To UBound(arr)
sStr = "*" & arr(i) & "[0-9]*"
If rCell.Formula Like sStr Then
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, rCell)
Else
Set rng2 = rCell
End If
End If
Next i
Next rCell
Else
'No formulas found
End If

If Not rng2 Is Nothing Then
'do something e.g.:
Debug.Print rng2.Address
rng2.Select
Else
MsgBox "No Formula constants found in " & SH.Name
End If

End Sub
'<<=================

---
Regards,
Norman



"Co-op Bank" <[email protected]> wrote in message
news:[email protected]...
> Hello, I have a big spreadsheet which should be purely formula based
> however
> I need to check that there are no constants (i.e. hard coded values) typed
> into the formulas for example '=sum(a1:b10)+25000'.
>
> Is there a way to check each cell reference to be totally sure that there
> are no hardcodings? vb solution perhaps? I know you can switch to view
> formulas on the spreadsheet but as there are so many and some are quite
> long
> it isnt always easy to spot via the ***** eye. Any help much appreciated.
>
> Thanks
>
> Brian
> Manchester, England

Norman:
This is soooo cool,,
Thanks for sharing the code.

Note my temporary modification of the output lines:
If Not rng2 Is Nothing Then
'do something e.g.:
MsgBox "Cells containing constants are: " & rng2.Address
'Debug.Print rng2.Address
' rng2.Select ' use this line to highlight cells

Right now the above is producing the Messagebox with:

Cells containing constants are: $B$10,$G$15,$G$20,$J$45

How could I change above lines to have to show in messagebox as:

Cells containing constants are:
$B$10
$G$15
$G$20
$J$45

Is there a maximum a MsgBox can display (like this)?
Tks in advance,
Jim May


"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> Hi Brian,
>
> Try:
>
> '=================>>
> Sub ConstantsInFormulas()
> Dim WB As Workbook
> Dim SH As Worksheet
> Dim rng As Range
> Dim rng2 As Range
> Dim rCell As Range
> Dim arr As Variant
> Dim sStr As String
> Dim i As Long
>
> Set WB = ActiveWorkbook '<<======== CHANGE
> Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
> Set rng = SH.UsedRange '<<======== CHANGE
>
> On Error Resume Next 'In case no formulas!
> Set rng = rng.SpecialCells(xlFormulas)
> On Error GoTo 0
>
> arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")
>
> If Not rng Is Nothing Then
> For Each rCell In rng.Cells
> For i = LBound(arr) To UBound(arr)
> sStr = "*" & arr(i) & "[0-9]*"
> If rCell.Formula Like sStr Then
> If Not rng2 Is Nothing Then
> Set rng2 = Union(rng2, rCell)
> Else
> Set rng2 = rCell
> End If
> End If
> Next i
> Next rCell
> Else
> 'No formulas found
> End If
>
> If Not rng2 Is Nothing Then
> 'do something e.g.:
> Debug.Print rng2.Address
> rng2.Select
> Else
> MsgBox "No Formula constants found in " & SH.Name
> End If
>
> End Sub
> '<<=================
>
> ---
> Regards,
> Norman
>
>
>
> "Co-op Bank" <[email protected]> wrote in message
> news:[email protected]...
>> Hello, I have a big spreadsheet which should be purely formula based
>> however
>> I need to check that there are no constants (i.e. hard coded values)
>> typed
>> into the formulas for example '=sum(a1:b10)+25000'.
>>
>> Is there a way to check each cell reference to be totally sure that there
>> are no hardcodings? vb solution perhaps? I know you can switch to view
>> formulas on the spreadsheet but as there are so many and some are quite
>> long
>> it isnt always easy to spot via the ***** eye. Any help much appreciated.
>>
>> Thanks
>>
>> Brian
>> Manchester, England
>
>

Hi Jim,

Thank you for your comments: they are very much appreciated.

> How could I change above lines to have to show in messagebox as:
>
> Cells containing constants are:
> $B$10
> $G$15
> $G$20
> $J$45

You could loop through the cells and build a string with a vbNewLine
separator.

> Is there a maximum a MsgBox can display (like this)?
Yes there is, and IIRC, there is no accompanying warining, just simple
truncation.

Best might be to add a report sheet showing the formuale anf the
corresponding cell/sheet addresses.

To illustrate each option, try something like:

'=================>>
Sub ConstantsInFormulas1A()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim arr As Variant
Dim sStr As String
Dim i As Long
Dim msg As String
Dim iCtr As Long

Set WB = ActiveWorkbook '<<======== CHANGE
Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
Set rng = SH.UsedRange '<<======== CHANGE

On Error Resume Next 'In case no formulas!
Set rng = rng.SpecialCells(xlFormulas)
On Error GoTo 0

arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")

If Not rng Is Nothing Then
For Each rCell In rng.Cells
For i = LBound(arr) To UBound(arr)
sStr = "*" & arr(i) & "[0-9]*"
If rCell.Formula Like sStr Then
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, rCell)
Else
Set rng2 = rCell
End If
End If
Next i
Next rCell
Else
'No formulas found
End If

If Not rng2 Is Nothing Then
'do something e.g.:
Debug.Print rng2.Address
SH.Activate
rng2.Select

Sheets.Add
ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd")

For iCtr = 1 To rng2.Cells.Count
msg = msg & vbNewLine & rng(i).Address(False, False)
With ActiveSheet
.Cells(iCtr, "A") = rng2(iCtr).Address(external:=True)
.Cells(iCtr, "B") = "'" & rng2(iCtr).Formula
End With
Next iCtr

MsgBox msg

Else
MsgBox "No Formula constants found in " & SH.Name
End If

End Sub
'<<=================

---
Regards,
Norman



"Jim May" <[email protected]> wrote in message
news:%N08f.4793$mV4.1171@dukeread02...
> Norman:
> This is soooo cool,,
> Thanks for sharing the code.
>
> Note my temporary modification of the output lines:
> If Not rng2 Is Nothing Then
> 'do something e.g.:
> MsgBox "Cells containing constants are: " & rng2.Address
> 'Debug.Print rng2.Address
> ' rng2.Select ' use this line to highlight cells
>
> Right now the above is producing the Messagebox with:
>
> Cells containing constants are: $B$10,$G$15,$G$20,$J$45
>
> How could I change above lines to have to show in messagebox as:
>
> Cells containing constants are:
> $B$10
> $G$15
> $G$20
> $J$45
>
> Is there a maximum a MsgBox can display (like this)?
> Tks in advance,
> Jim May
>
>
> "Norman Jones" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi Brian,
>>
>> Try:
>>
>> '=================>>
>> Sub ConstantsInFormulas()
>> Dim WB As Workbook
>> Dim SH As Worksheet
>> Dim rng As Range
>> Dim rng2 As Range
>> Dim rCell As Range
>> Dim arr As Variant
>> Dim sStr As String
>> Dim i As Long
>>
>> Set WB = ActiveWorkbook '<<======== CHANGE
>> Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
>> Set rng = SH.UsedRange '<<======== CHANGE
>>
>> On Error Resume Next 'In case no formulas!
>> Set rng = rng.SpecialCells(xlFormulas)
>> On Error GoTo 0
>>
>> arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")
>>
>> If Not rng Is Nothing Then
>> For Each rCell In rng.Cells
>> For i = LBound(arr) To UBound(arr)
>> sStr = "*" & arr(i) & "[0-9]*"
>> If rCell.Formula Like sStr Then
>> If Not rng2 Is Nothing Then
>> Set rng2 = Union(rng2, rCell)
>> Else
>> Set rng2 = rCell
>> End If
>> End If
>> Next i
>> Next rCell
>> Else
>> 'No formulas found
>> End If
>>
>> If Not rng2 Is Nothing Then
>> 'do something e.g.:
>> Debug.Print rng2.Address
>> rng2.Select
>> Else
>> MsgBox "No Formula constants found in " & SH.Name
>> End If
>>
>> End Sub
>> '<<=================
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Co-op Bank" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hello, I have a big spreadsheet which should be purely formula based
>>> however
>>> I need to check that there are no constants (i.e. hard coded values)
>>> typed
>>> into the formulas for example '=sum(a1:b10)+25000'.
>>>
>>> Is there a way to check each cell reference to be totally sure that
>>> there
>>> are no hardcodings? vb solution perhaps? I know you can switch to view
>>> formulas on the spreadsheet but as there are so many and some are quite
>>> long
>>> it isnt always easy to spot via the ***** eye. Any help much
>>> appreciated.
>>>
>>> Thanks
>>>
>>> Brian
>>> Manchester, England
>>
>>
>
>

Norman at this point (in your most recent code):

For iCtr = 1 To rng2.Cells.Count
' msg = msg & vbNewLine & rng(i).Address(False, False)
With ActiveSheet
.Cells(iCtr, "A") = rng2(iCtr).Address(external:=True)
.Cells(iCtr, "B") = "'" & rng2(iCtr).Formula
End With
Next iCtr

Prior to the above (from the immediate window)
? rng2.address
$B$10,$G$15

but also (from the immediate)
? rng2(2).address
$B$11 << Which is a blank cell !!

My report produces $B$10 and $B$11
versus the $B$10 and $G$15
any observations?

TIA,
Jim


"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Jim,
>
> Thank you for your comments: they are very much appreciated.
>
>> How could I change above lines to have to show in messagebox as:
>>
>> Cells containing constants are:
>> $B$10
>> $G$15
>> $G$20
>> $J$45
>
> You could loop through the cells and build a string with a vbNewLine
> separator.
>
>> Is there a maximum a MsgBox can display (like this)?
> Yes there is, and IIRC, there is no accompanying warining, just simple
> truncation.
>
> Best might be to add a report sheet showing the formuale anf the
> corresponding cell/sheet addresses.
>
> To illustrate each option, try something like:
>
> '=================>>
> Sub ConstantsInFormulas1A()
> Dim WB As Workbook
> Dim SH As Worksheet
> Dim rng As Range
> Dim rng2 As Range
> Dim rCell As Range
> Dim arr As Variant
> Dim sStr As String
> Dim i As Long
> Dim msg As String
> Dim iCtr As Long
>
> Set WB = ActiveWorkbook '<<======== CHANGE
> Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
> Set rng = SH.UsedRange '<<======== CHANGE
>
> On Error Resume Next 'In case no formulas!
> Set rng = rng.SpecialCells(xlFormulas)
> On Error GoTo 0
>
> arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")
>
> If Not rng Is Nothing Then
> For Each rCell In rng.Cells
> For i = LBound(arr) To UBound(arr)
> sStr = "*" & arr(i) & "[0-9]*"
> If rCell.Formula Like sStr Then
> If Not rng2 Is Nothing Then
> Set rng2 = Union(rng2, rCell)
> Else
> Set rng2 = rCell
> End If
> End If
> Next i
> Next rCell
> Else
> 'No formulas found
> End If
>
> If Not rng2 Is Nothing Then
> 'do something e.g.:
> Debug.Print rng2.Address
> SH.Activate
> rng2.Select
>
> Sheets.Add
> ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd")
>
> For iCtr = 1 To rng2.Cells.Count
> msg = msg & vbNewLine & rng(i).Address(False, False)
> With ActiveSheet
> .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True)
> .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula
> End With
> Next iCtr
>
> MsgBox msg
>
> Else
> MsgBox "No Formula constants found in " & SH.Name
> End If
>
> End Sub
> '<<=================
>
> ---
> Regards,
> Norman
>
>
>
> "Jim May" <[email protected]> wrote in message
> news:%N08f.4793$mV4.1171@dukeread02...
>> Norman:
>> This is soooo cool,,
>> Thanks for sharing the code.
>>
>> Note my temporary modification of the output lines:
>> If Not rng2 Is Nothing Then
>> 'do something e.g.:
>> MsgBox "Cells containing constants are: " & rng2.Address
>> 'Debug.Print rng2.Address
>> ' rng2.Select ' use this line to highlight cells
>>
>> Right now the above is producing the Messagebox with:
>>
>> Cells containing constants are: $B$10,$G$15,$G$20,$J$45
>>
>> How could I change above lines to have to show in messagebox as:
>>
>> Cells containing constants are:
>> $B$10
>> $G$15
>> $G$20
>> $J$45
>>
>> Is there a maximum a MsgBox can display (like this)?
>> Tks in advance,
>> Jim May
>>
>>
>> "Norman Jones" <[email protected]> wrote in message
>> news:%[email protected]...
>>> Hi Brian,
>>>
>>> Try:
>>>
>>> '=================>>
>>> Sub ConstantsInFormulas()
>>> Dim WB As Workbook
>>> Dim SH As Worksheet
>>> Dim rng As Range
>>> Dim rng2 As Range
>>> Dim rCell As Range
>>> Dim arr As Variant
>>> Dim sStr As String
>>> Dim i As Long
>>>
>>> Set WB = ActiveWorkbook '<<======== CHANGE
>>> Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
>>> Set rng = SH.UsedRange '<<======== CHANGE
>>>
>>> On Error Resume Next 'In case no formulas!
>>> Set rng = rng.SpecialCells(xlFormulas)
>>> On Error GoTo 0
>>>
>>> arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")
>>>
>>> If Not rng Is Nothing Then
>>> For Each rCell In rng.Cells
>>> For i = LBound(arr) To UBound(arr)
>>> sStr = "*" & arr(i) & "[0-9]*"
>>> If rCell.Formula Like sStr Then
>>> If Not rng2 Is Nothing Then
>>> Set rng2 = Union(rng2, rCell)
>>> Else
>>> Set rng2 = rCell
>>> End If
>>> End If
>>> Next i
>>> Next rCell
>>> Else
>>> 'No formulas found
>>> End If
>>>
>>> If Not rng2 Is Nothing Then
>>> 'do something e.g.:
>>> Debug.Print rng2.Address
>>> rng2.Select
>>> Else
>>> MsgBox "No Formula constants found in " & SH.Name
>>> End If
>>>
>>> End Sub
>>> '<<=================
>>>
>>> ---
>>> Regards,
>>> Norman
>>>
>>>
>>>
>>> "Co-op Bank" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Hello, I have a big spreadsheet which should be purely formula based
>>>> however
>>>> I need to check that there are no constants (i.e. hard coded values)
>>>> typed
>>>> into the formulas for example '=sum(a1:b10)+25000'.
>>>>
>>>> Is there a way to check each cell reference to be totally sure that
>>>> there
>>>> are no hardcodings? vb solution perhaps? I know you can switch to view
>>>> formulas on the spreadsheet but as there are so many and some are quite
>>>> long
>>>> it isnt always easy to spot via the ***** eye. Any help much
>>>> appreciated.
>>>>
>>>> Thanks
>>>>
>>>> Brian
>>>> Manchester, England
>>>
>>>
>>
>>
>
>

Hi Jim,

> any observations?

Sloppily, I was iterating though a potentially non-contiguous range as if it
were contiguous. In the case of a non-contiguous range, use
of the index will produce results such as those you have observed.

Try, instead, therefore:
'=================>>
Sub ConstantsInFormulas1B()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim arr As Variant
Dim sStr As String
Dim i As Long
Dim msg As String
Dim iCtr As Long
Dim aCell As Range

Set WB = ActiveWorkbook '<<======== CHANGE
Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
Set rng = SH.UsedRange '<<======== CHANGE

On Error Resume Next 'In case no formulas!
Set rng = rng.SpecialCells(xlFormulas)
On Error GoTo 0

arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")

If Not rng Is Nothing Then
For Each rCell In rng.Cells
For i = LBound(arr) To UBound(arr)
sStr = "*" & arr(i) & "[0-9]*"
If rCell.Formula Like sStr Then
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, rCell)
Else
Set rng2 = rCell
End If
End If
Next i
Next rCell
Else
'No formulas found
End If

If Not rng2 Is Nothing Then
'do something e.g.:
Debug.Print rng2.Address
SH.Activate
rng2.Select


Sheets.Add
ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd")

For Each aCell In rng2.Cells
iCtr = iCtr + 1
With ActiveSheet
.Cells(iCtr, "A") = aCell.Address(external:=True)
.Cells(iCtr, "B") = "'" & aCell.Formula
msg = msg & vbNewLine & aCell.Address(external:=True)
End With
Next aCell

MsgBox msg

Else
MsgBox "No Formula constants found in " & SH.Name
End If

End Sub
'<<=================

---
Regards,
Norman



"Jim May" <[email protected]> wrote in message
news:t338f.4794$mV4.2353@dukeread02...
> Norman at this point (in your most recent code):
>
> For iCtr = 1 To rng2.Cells.Count
> ' msg = msg & vbNewLine & rng(i).Address(False, False)
> With ActiveSheet
> .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True)
> .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula
> End With
> Next iCtr
>
> Prior to the above (from the immediate window)
> ? rng2.address
> $B$10,$G$15
>
> but also (from the immediate)
> ? rng2(2).address
> $B$11 << Which is a blank cell !!
>
> My report produces $B$10 and $B$11
> versus the $B$10 and $G$15
> any observations?
>
> TIA,
> Jim
>

how about just
msg = "Cell Containing Constants: " & vbNewLine
msgbox msg & replace(rng2.Address(0,0),",",chr10)

demo'd from the immediate window:

set rng2 = Selection
? rng2.Address
$B$5,$D$7,$C$9,$F$11,$D$3,$F$4:$G$5
? replace(rng2.address(0,0),",",chr(10))
B5
D7
C9
F11
D3
F4:G5

I believe a message box is limited to 255 characters.

--
Regards,
Tom Ogilvy

"Jim May" <[email protected]> wrote in message
news:%N08f.4793$mV4.1171@dukeread02...
> Norman:
> This is soooo cool,,
> Thanks for sharing the code.
>
> Note my temporary modification of the output lines:
> If Not rng2 Is Nothing Then
> 'do something e.g.:
> MsgBox "Cells containing constants are: " & rng2.Address
> 'Debug.Print rng2.Address
> ' rng2.Select ' use this line to highlight cells
>
> Right now the above is producing the Messagebox with:
>
> Cells containing constants are: $B$10,$G$15,$G$20,$J$45
>
> How could I change above lines to have to show in messagebox as:
>
> Cells containing constants are:
> $B$10
> $G$15
> $G$20
> $J$45
>
> Is there a maximum a MsgBox can display (like this)?
> Tks in advance,
> Jim May
>
>
> "Norman Jones" <[email protected]> wrote in message
> news:%[email protected]...
> > Hi Brian,
> >
> > Try:
> >
> > '=================>>
> > Sub ConstantsInFormulas()
> > Dim WB As Workbook
> > Dim SH As Worksheet
> > Dim rng As Range
> > Dim rng2 As Range
> > Dim rCell As Range
> > Dim arr As Variant
> > Dim sStr As String
> > Dim i As Long
> >
> > Set WB = ActiveWorkbook '<<======== CHANGE
> > Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
> > Set rng = SH.UsedRange '<<======== CHANGE
> >
> > On Error Resume Next 'In case no formulas!
> > Set rng = rng.SpecialCells(xlFormulas)
> > On Error GoTo 0
> >
> > arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")
> >
> > If Not rng Is Nothing Then
> > For Each rCell In rng.Cells
> > For i = LBound(arr) To UBound(arr)
> > sStr = "*" & arr(i) & "[0-9]*"
> > If rCell.Formula Like sStr Then
> > If Not rng2 Is Nothing Then
> > Set rng2 = Union(rng2, rCell)
> > Else
> > Set rng2 = rCell
> > End If
> > End If
> > Next i
> > Next rCell
> > Else
> > 'No formulas found
> > End If
> >
> > If Not rng2 Is Nothing Then
> > 'do something e.g.:
> > Debug.Print rng2.Address
> > rng2.Select
> > Else
> > MsgBox "No Formula constants found in " & SH.Name
> > End If
> >
> > End Sub
> > '<<=================
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Co-op Bank" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hello, I have a big spreadsheet which should be purely formula based
> >> however
> >> I need to check that there are no constants (i.e. hard coded values)
> >> typed
> >> into the formulas for example '=sum(a1:b10)+25000'.
> >>
> >> Is there a way to check each cell reference to be totally sure that
there
> >> are no hardcodings? vb solution perhaps? I know you can switch to view
> >> formulas on the spreadsheet but as there are so many and some are quite
> >> long
> >> it isnt always easy to spot via the ***** eye. Any help much
appreciated.
> >>
> >> Thanks
> >>
> >> Brian
> >> Manchester, England
> >
> >
>
>

Hi Jim,

> Sloppily, I was iterating though a potentially non-contiguous range as if
> it were contiguous. In the case of a non-contiguous range, use
> of the index will produce results such as those you have observed.

To demonstrate:

'=================>>
Sub ShowIt()
Dim rng As Range
Dim i As Long
Dim rCell As Range

Set rng = Union(Range("A1"), Range("IV65536"))

'Sloppily iterating through a non contiguous range!
For i = 1 To rng.Cells.Count
MsgBox rng(i).Address(0, 0)
Next i

'Iterating the range cells!
For Each rCell In rng.Cells
MsgBox rCell.Address(0, 0)
Next rCell

End Sub
'<<=================

---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Jim,
>
>> any observations?
>
> Sloppily, I was iterating though a potentially non-contiguous range as if
> it were contiguous. In the case of a non-contiguous range, use
> of the index will produce results such as those you have observed.
>
> Try, instead, therefore:
> '=================>>
> Sub ConstantsInFormulas1B()
> Dim WB As Workbook
> Dim SH As Worksheet
> Dim rng As Range
> Dim rng2 As Range
> Dim rCell As Range
> Dim arr As Variant
> Dim sStr As String
> Dim i As Long
> Dim msg As String
> Dim iCtr As Long
> Dim aCell As Range
>
> Set WB = ActiveWorkbook '<<======== CHANGE
> Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
> Set rng = SH.UsedRange '<<======== CHANGE
>
> On Error Resume Next 'In case no formulas!
> Set rng = rng.SpecialCells(xlFormulas)
> On Error GoTo 0
>
> arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")
>
> If Not rng Is Nothing Then
> For Each rCell In rng.Cells
> For i = LBound(arr) To UBound(arr)
> sStr = "*" & arr(i) & "[0-9]*"
> If rCell.Formula Like sStr Then
> If Not rng2 Is Nothing Then
> Set rng2 = Union(rng2, rCell)
> Else
> Set rng2 = rCell
> End If
> End If
> Next i
> Next rCell
> Else
> 'No formulas found
> End If
>
> If Not rng2 Is Nothing Then
> 'do something e.g.:
> Debug.Print rng2.Address
> SH.Activate
> rng2.Select
>
>
> Sheets.Add
> ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd")
>
> For Each aCell In rng2.Cells
> iCtr = iCtr + 1
> With ActiveSheet
> .Cells(iCtr, "A") = aCell.Address(external:=True)
> .Cells(iCtr, "B") = "'" & aCell.Formula
> msg = msg & vbNewLine & aCell.Address(external:=True)
> End With
> Next aCell
>
> MsgBox msg
>
> Else
> MsgBox "No Formula constants found in " & SH.Name
> End If
>
> End Sub
> '<<=================
>
> ---
> Regards,
> Norman
>
>
>
> "Jim May" <[email protected]> wrote in message
> news:t338f.4794$mV4.2353@dukeread02...
>> Norman at this point (in your most recent code):
>>
>> For iCtr = 1 To rng2.Cells.Count
>> ' msg = msg & vbNewLine & rng(i).Address(False, False)
>> With ActiveSheet
>> .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True)
>> .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula
>> End With
>> Next iCtr
>>
>> Prior to the above (from the immediate window)
>> ? rng2.address
>> $B$10,$G$15
>>
>> but also (from the immediate)
>> ? rng2(2).address
>> $B$11 << Which is a blank cell !!
>>
>> My report produces $B$10 and $B$11
>> versus the $B$10 and $G$15
>> any observations?
>>
>> TIA,
>> Jim
>>
>
>

Hi Norman/Jim,

Thanks excellent thankyou very much!

Best regards
Brian

"Norman Jones" wrote:

> Hi Jim,
>
> > Sloppily, I was iterating though a potentially non-contiguous range as if
> > it were contiguous. In the case of a non-contiguous range, use
> > of the index will produce results such as those you have observed.
>
> To demonstrate:
>
> '=================>>
> Sub ShowIt()
> Dim rng As Range
> Dim i As Long
> Dim rCell As Range
>
> Set rng = Union(Range("A1"), Range("IV65536"))
>
> 'Sloppily iterating through a non contiguous range!
> For i = 1 To rng.Cells.Count
> MsgBox rng(i).Address(0, 0)
> Next i
>
> 'Iterating the range cells!
> For Each rCell In rng.Cells
> MsgBox rCell.Address(0, 0)
> Next rCell
>
> End Sub
> '<<=================
>
> ---
> Regards,
> Norman
>
>
>
> "Norman Jones" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Jim,
> >
> >> any observations?
> >
> > Sloppily, I was iterating though a potentially non-contiguous range as if
> > it were contiguous. In the case of a non-contiguous range, use
> > of the index will produce results such as those you have observed.
> >
> > Try, instead, therefore:
> > '=================>>
> > Sub ConstantsInFormulas1B()
> > Dim WB As Workbook
> > Dim SH As Worksheet
> > Dim rng As Range
> > Dim rng2 As Range
> > Dim rCell As Range
> > Dim arr As Variant
> > Dim sStr As String
> > Dim i As Long
> > Dim msg As String
> > Dim iCtr As Long
> > Dim aCell As Range
> >
> > Set WB = ActiveWorkbook '<<======== CHANGE
> > Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
> > Set rng = SH.UsedRange '<<======== CHANGE
> >
> > On Error Resume Next 'In case no formulas!
> > Set rng = rng.SpecialCells(xlFormulas)
> > On Error GoTo 0
> >
> > arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")
> >
> > If Not rng Is Nothing Then
> > For Each rCell In rng.Cells
> > For i = LBound(arr) To UBound(arr)
> > sStr = "*" & arr(i) & "[0-9]*"
> > If rCell.Formula Like sStr Then
> > If Not rng2 Is Nothing Then
> > Set rng2 = Union(rng2, rCell)
> > Else
> > Set rng2 = rCell
> > End If
> > End If
> > Next i
> > Next rCell
> > Else
> > 'No formulas found
> > End If
> >
> > If Not rng2 Is Nothing Then
> > 'do something e.g.:
> > Debug.Print rng2.Address
> > SH.Activate
> > rng2.Select
> >
> >
> > Sheets.Add
> > ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd")
> >
> > For Each aCell In rng2.Cells
> > iCtr = iCtr + 1
> > With ActiveSheet
> > .Cells(iCtr, "A") = aCell.Address(external:=True)
> > .Cells(iCtr, "B") = "'" & aCell.Formula
> > msg = msg & vbNewLine & aCell.Address(external:=True)
> > End With
> > Next aCell
> >
> > MsgBox msg
> >
> > Else
> > MsgBox "No Formula constants found in " & SH.Name
> > End If
> >
> > End Sub
> > '<<=================
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Jim May" <[email protected]> wrote in message
> > news:t338f.4794$mV4.2353@dukeread02...
> >> Norman at this point (in your most recent code):
> >>
> >> For iCtr = 1 To rng2.Cells.Count
> >> ' msg = msg & vbNewLine & rng(i).Address(False, False)
> >> With ActiveSheet
> >> .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True)
> >> .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula
> >> End With
> >> Next iCtr
> >>
> >> Prior to the above (from the immediate window)
> >> ? rng2.address
> >> $B$10,$G$15
> >>
> >> but also (from the immediate)
> >> ? rng2(2).address
> >> $B$11 << Which is a blank cell !!
> >>
> >> My report produces $B$10 and $B$11
> >> versus the $B$10 and $G$15
> >> any observations?
> >>
> >> TIA,
> >> Jim
> >>
> >
> >
>
>
>

Hi Tom,

A good suggestion!

Thank you

---
Regards,
Norman


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> how about just
> msg = "Cell Containing Constants: " & vbNewLine
> msgbox msg & replace(rng2.Address(0,0),",",chr10)
>
> demo'd from the immediate window:
>
> set rng2 = Selection
> ? rng2.Address
> $B$5,$D$7,$C$9,$F$11,$D$3,$F$4:$G$5
> ? replace(rng2.address(0,0),",",chr(10))
> B5
> D7
> C9
> F11
> D3
> F4:G5
>
> I believe a message box is limited to 255 characters.
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim May" <[email protected]> wrote in message
> news:%N08f.4793$mV4.1171@dukeread02...
>> Norman:
>> This is soooo cool,,
>> Thanks for sharing the code.
>>
>> Note my temporary modification of the output lines:
>> If Not rng2 Is Nothing Then
>> 'do something e.g.:
>> MsgBox "Cells containing constants are: " & rng2.Address
>> 'Debug.Print rng2.Address
>> ' rng2.Select ' use this line to highlight cells
>>
>> Right now the above is producing the Messagebox with:
>>
>> Cells containing constants are: $B$10,$G$15,$G$20,$J$45
>>
>> How could I change above lines to have to show in messagebox as:
>>
>> Cells containing constants are:
>> $B$10
>> $G$15
>> $G$20
>> $J$45
>>
>> Is there a maximum a MsgBox can display (like this)?
>> Tks in advance,
>> Jim May
>>
>>
>> "Norman Jones" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Hi Brian,
>> >
>> > Try:
>> >
>> > '=================>>
>> > Sub ConstantsInFormulas()
>> > Dim WB As Workbook
>> > Dim SH As Worksheet
>> > Dim rng As Range
>> > Dim rng2 As Range
>> > Dim rCell As Range
>> > Dim arr As Variant
>> > Dim sStr As String
>> > Dim i As Long
>> >
>> > Set WB = ActiveWorkbook '<<======== CHANGE
>> > Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
>> > Set rng = SH.UsedRange '<<======== CHANGE
>> >
>> > On Error Resume Next 'In case no formulas!
>> > Set rng = rng.SpecialCells(xlFormulas)
>> > On Error GoTo 0
>> >
>> > arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")
>> >
>> > If Not rng Is Nothing Then
>> > For Each rCell In rng.Cells
>> > For i = LBound(arr) To UBound(arr)
>> > sStr = "*" & arr(i) & "[0-9]*"
>> > If rCell.Formula Like sStr Then
>> > If Not rng2 Is Nothing Then
>> > Set rng2 = Union(rng2, rCell)
>> > Else
>> > Set rng2 = rCell
>> > End If
>> > End If
>> > Next i
>> > Next rCell
>> > Else
>> > 'No formulas found
>> > End If
>> >
>> > If Not rng2 Is Nothing Then
>> > 'do something e.g.:
>> > Debug.Print rng2.Address
>> > rng2.Select
>> > Else
>> > MsgBox "No Formula constants found in " & SH.Name
>> > End If
>> >
>> > End Sub
>> > '<<=================
>> >
>> > ---
>> > Regards,
>> > Norman
>> >
>> >
>> >
>> > "Co-op Bank" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hello, I have a big spreadsheet which should be purely formula based
>> >> however
>> >> I need to check that there are no constants (i.e. hard coded values)
>> >> typed
>> >> into the formulas for example '=sum(a1:b10)+25000'.
>> >>
>> >> Is there a way to check each cell reference to be totally sure that
> there
>> >> are no hardcodings? vb solution perhaps? I know you can switch to view
>> >> formulas on the spreadsheet but as there are so many and some are
>> >> quite
>> >> long
>> >> it isnt always easy to spot via the ***** eye. Any help much
> appreciated.
>> >>
>> >> Thanks
>> >>
>> >> Brian
>> >> Manchester, England
>> >
>> >
>>
>>
>
>

Thanks VERY much Norman;
This is a real "Keeper"!!
Jim

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Jim,
>
>> any observations?
>
> Sloppily, I was iterating though a potentially non-contiguous range as if
> it were contiguous. In the case of a non-contiguous range, use
> of the index will produce results such as those you have observed.
>
> Try, instead, therefore:
> '=================>>
> Sub ConstantsInFormulas1B()
> Dim WB As Workbook
> Dim SH As Worksheet
> Dim rng As Range
> Dim rng2 As Range
> Dim rCell As Range
> Dim arr As Variant
> Dim sStr As String
> Dim i As Long
> Dim msg As String
> Dim iCtr As Long
> Dim aCell As Range
>
> Set WB = ActiveWorkbook '<<======== CHANGE
> Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
> Set rng = SH.UsedRange '<<======== CHANGE
>
> On Error Resume Next 'In case no formulas!
> Set rng = rng.SpecialCells(xlFormulas)
> On Error GoTo 0
>
> arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")
>
> If Not rng Is Nothing Then
> For Each rCell In rng.Cells
> For i = LBound(arr) To UBound(arr)
> sStr = "*" & arr(i) & "[0-9]*"
> If rCell.Formula Like sStr Then
> If Not rng2 Is Nothing Then
> Set rng2 = Union(rng2, rCell)
> Else
> Set rng2 = rCell
> End If
> End If
> Next i
> Next rCell
> Else
> 'No formulas found
> End If
>
> If Not rng2 Is Nothing Then
> 'do something e.g.:
> Debug.Print rng2.Address
> SH.Activate
> rng2.Select
>
>
> Sheets.Add
> ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd")
>
> For Each aCell In rng2.Cells
> iCtr = iCtr + 1
> With ActiveSheet
> .Cells(iCtr, "A") = aCell.Address(external:=True)
> .Cells(iCtr, "B") = "'" & aCell.Formula
> msg = msg & vbNewLine & aCell.Address(external:=True)
> End With
> Next aCell
>
> MsgBox msg
>
> Else
> MsgBox "No Formula constants found in " & SH.Name
> End If
>
> End Sub
> '<<=================
>
> ---
> Regards,
> Norman
>
>
>
> "Jim May" <[email protected]> wrote in message
> news:t338f.4794$mV4.2353@dukeread02...
>> Norman at this point (in your most recent code):
>>
>> For iCtr = 1 To rng2.Cells.Count
>> ' msg = msg & vbNewLine & rng(i).Address(False, False)
>> With ActiveSheet
>> .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True)
>> .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula
>> End With
>> Next iCtr
>>
>> Prior to the above (from the immediate window)
>> ? rng2.address
>> $B$10,$G$15
>>
>> but also (from the immediate)
>> ? rng2(2).address
>> $B$11 << Which is a blank cell !!
>>
>> My report produces $B$10 and $B$11
>> versus the $B$10 and $G$15
>> any observations?
>>
>> TIA,
>> Jim
>>
>
>

Hi Jim,

Try this slightly revised and commented version:

'=================>>
Sub ConstantsInFormulas2()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim Rng2 As Range
Dim rCell As Range
Dim aCell As Range
Dim arr As Variant
Dim sStr As String
Dim strName As String
Dim msg As String
Dim i As Long
Dim iCtr As Long

Set WB = ActiveWorkbook '<<======== CHANGE
Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
Set rng = SH.UsedRange '<<======== CHANGE

On Error Resume Next '\\ In case no formulas!
Set rng = rng.SpecialCells(xlFormulas)
On Error GoTo 0

arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")

If Not rng Is Nothing Then
For Each rCell In rng.Cells
For i = LBound(arr) To UBound(arr)
sStr = "*" & arr(i) & "[0-9]*"
If rCell.Formula Like sStr Then
If Not Rng2 Is Nothing Then
Set Rng2 = Union(Rng2, rCell)
Else
Set Rng2 = rCell
End If
End If
Next i
Next rCell
Else
'\\No formulas found
End If

If Not Rng2 Is Nothing Then
'\\ do something e.g.:
Debug.Print Rng2.Address
'\\ Highlight Formulas with constants
Rng2.Interior.ColorIndex = 6

'\\ Add a report sheet
Sheets.Add
'\\ Name the report sheet -include Report date & time
strName = "FormulasReport" _
& Format(Now, "yyyymmdd hh-mm")
ActiveSheet.Name = strName

For Each aCell In Rng2.Cells
iCtr = iCtr + 1
'\\ Write information to the Report sheet
With ActiveSheet
.Cells(iCtr, "A") = aCell.Address(external:=True)
.Cells(iCtr, "B") = "'" & aCell.Formula
End With
Next aCell

ActiveSheet.Columns("A:B").AutoFit

'\\ Parse address string to produce columnar MsgBox report
'\\ N.B. A Msgbox is limited to 255 characters.
msg = "Cells holding formulas which include constants" _
& vbNewLine
msg = msg & Replace(Rng2.Address(0, 0), ",", Chr(10))

Else
msg = "No Formula constants found in " & SH.Name
End If

MsgBox prompt:=msg, _
Buttons:=vbInformation, _
Title:="Formulas Report"

End Sub
'<<=================


---
Regards,
Norman



"Jim May" <[email protected]> wrote in message
news:QHc8f.4854$mV4.3704@dukeread02...
> Thanks VERY much Norman;
> This is a real "Keeper"!!
> Jim

Hi Norman,

Told you it was a keeper :o)
Just the hyperlinks still missing in the report sheet ;o)
Cheers Sige


Norman Jones wrote:
> Hi Jim,
>
> Try this slightly revised and commented version:
>
> '=================>>
> Sub ConstantsInFormulas2()
> Dim WB As Workbook
> Dim SH As Worksheet
> Dim rng As Range
> Dim Rng2 As Range
> Dim rCell As Range
> Dim aCell As Range
> Dim arr As Variant
> Dim sStr As String
> Dim strName As String
> Dim msg As String
> Dim i As Long
> Dim iCtr As Long
>
> Set WB = ActiveWorkbook '<<======== CHANGE
> Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
> Set rng = SH.UsedRange '<<======== CHANGE
>
> On Error Resume Next '\\ In case no formulas!
> Set rng = rng.SpecialCells(xlFormulas)
> On Error GoTo 0
>
> arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]", "(")
>
> If Not rng Is Nothing Then
> For Each rCell In rng.Cells
> For i = LBound(arr) To UBound(arr)
> sStr = "*" & arr(i) & "[0-9]*"
> If rCell.Formula Like sStr Then
> If Not Rng2 Is Nothing Then
> Set Rng2 = Union(Rng2, rCell)
> Else
> Set Rng2 = rCell
> End If
> End If
> Next i
> Next rCell
> Else
> '\\No formulas found
> End If
>
> If Not Rng2 Is Nothing Then
> '\\ do something e.g.:
> Debug.Print Rng2.Address
> '\\ Highlight Formulas with constants
> Rng2.Interior.ColorIndex = 6
>
> '\\ Add a report sheet
> Sheets.Add
> '\\ Name the report sheet -include Report date & time
> strName = "FormulasReport" _
> & Format(Now, "yyyymmdd hh-mm")
> ActiveSheet.Name = strName
>
> For Each aCell In Rng2.Cells
> iCtr = iCtr + 1
> '\\ Write information to the Report sheet
> With ActiveSheet
> .Cells(iCtr, "A") = aCell.Address(external:=True)
> .Cells(iCtr, "B") = "'" & aCell.Formula
> End With
> Next aCell
>
> ActiveSheet.Columns("A:B").AutoFit
>
> '\\ Parse address string to produce columnar MsgBox report
> '\\ N.B. A Msgbox is limited to 255 characters.
> msg = "Cells holding formulas which include constants" _
> & vbNewLine
> msg = msg & Replace(Rng2.Address(0, 0), ",", Chr(10))
>
> Else
> msg = "No Formula constants found in " & SH.Name
> End If
>
> MsgBox prompt:=msg, _
> Buttons:=vbInformation, _
> Title:="Formulas Report"
>
> End Sub
> '<<=================
>
>
> ---
> Regards,
> Norman
>
>
>
> "Jim May" <[email protected]> wrote in message
> news:QHc8f.4854$mV4.3704@dukeread02...
> > Thanks VERY much Norman;
> > This is a real "Keeper"!!
> > Jim