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" wrote in message
...
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 a
$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" 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 a " & 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 a $B$10,$G$15,$G$20,$J$45

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

Cells containing constants a
$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" wrote in message
...
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" wrote in message
...
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 naked eye. Any help much
appreciated.

Thanks

Brian
Manchester, England