Hi EM,
just saw that while i was working Bernie posted a similar solution.

IMPORTANT!
I suppose you run your function against all cells
in a worksheet.

I'd run it against the result of
Range(x).SpecialCells(xlCellTypeFormulas)
(note SpeciallCells returns a multiarea range!)

To find the "pasted numbers" use
SpecialCells(xlCellTypeConstants,xlNumbers)

Further:

I'm not sure that making it more robust improves speed :
and I've not thoroughly tested (and still slow on large quantities)

Function FormulaHasConstant(rngCell As Range) As Boolean
Dim sFml$, aFml$(), i&, bQuoted As Boolean

'If cell is not a formula then check to see that
'it is not a paste specialed number
With rngCell
If Len(.Formula) = 0 Then
'empty cell
ElseIf Not .HasFormula Then
'no formula
If IsError(.Value) Then
'it's an error constant
Stop
ElseIf Not IsNumeric(.Value) Then
'it's a string string constant
Else
'------------------------
'it's a numeric constant!
FormulaHasConstant = True
End If
Else
'Pass the cell formula to a string variable
'ignore the starting "="
sFml = Mid$(.Formula, 2)

'Replace operators with separator, but skip quoted strings
For i = 1 To Len(sFml)
Select Case Mid$(sFml, i, 1)
Case """"
bQuoted = Not bQuoted
Case "=", "+", "-", "/", "*", "^", "&", "{", "}"
If Not bQuoted Then
sFml = Left$(sFml, i - 1) & "," & Mid$(sFml, i + 1)
End If
End Select
Next
'Split the string into components
aFml = Split(sFml, ",")
'Check each component to see if it's numeric
For i = 0 To UBound(aFml)
If IsNumeric(aFml(i)) Then
FormulaHasConstant = True
Exit Function
End If
Next

End If
End With

End Function


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ExcelMonkey wrote :

I am using a function which searches formulas for hard coded contants
(+1,-6^8,*9,/7). There are two issues with this:

1) It does not find constants at the beginning of a fomula (i.e.
can't find =1+SUM(A1:A10) but will find =SUM(A1:A10) +1

2) Can be very slow on spreadsheets with large number of cells with
large complex formulas.

Can anyone recommend a more robust version which is faster.

Thanks

EM






Private Function FormulaHasConstant(rn As Range) As Boolean
Dim FormulaString As String
Dim Operators As String
Dim FormulaCharacter As String
Dim i As Integer, j As Integer
Dim Constraints As String
Dim FoundOperator As String
Dim FoundNumber As Integer
Dim FoundCombination As String

Operators = "=" & "/" & "+" & "-" & "*" & "^"

FormulaHasConstant = False

'If cell is not a formula then check to see that
'it is not a paste specialed number
If rn.HasFormula = False Then
'If not formula but empty, exit
If Not IsEmpty(rn) Then
If IsNumeric(rn) Then
FormulaHasConstant = True
Else
FormulaHasConstant = False
End If
Else
Exit Function
End If
Exit Function
End If

'Pass the cell formula to a string variable

FormulaString = rn.Formula

'Searh each character in string
For i = 1 To Len(FormulaString)
FormulaCharacter = Mid(FormulaString, i, 1)
'Search for operators in string
If InStr(1, Operators, FormulaCharacter) 0 Then
'Add "Or" Condition
If IsNumeric(Mid(FormulaString, i + 1, 1)) Then
FormulaHasConstant = True
FoundOperator = FormulaCharacter
FoundNumber = Mid(FormulaString, i + 1, 1)
'Pass operator and number to variable so that you know
'what has been found
FoundCombination = FoundOperator & FoundNumber
Exit Function
End If
End If
Next i
End Function