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, therefo
'=================
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" 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