Search Cell contains formula and Numeric value

[Myproblem]

I am looking for a code that loops through the selected cells and checks every cell it contains formula AND a numeric value, like to find cell A2
A B C
1 5=A1=B1+C1 4 1
2 7=A1=B1+C1+2 4 1

I found code that is close to match, because it finds formula, it do not recognize numerical value in formula

Sub AllNummericCells()
Dim rCcells As Range, rFcells As Range
Dim rAcells As Range
'Set variable to all used cells
Set rAcells = ActiveSheet.UsedRange
On Error Resume Next 'In case of no numeric formula or constants.
'Set variable to all numeric constants
Set rCcells = rAcells.SpecialCells(xlCellTypeConstants, xlNumbers)
'Set variable to all numeric formulas
Set rFcells = rAcells.SpecialCells(xlCellTypeFormulas, xlNumbers)
'Determine which type of numeric data (formulas, constants or none)
If rCcells Is Nothing And rFcells Is Nothing Then
MsgBox "You Worksheet contains no numbers"
End
ElseIf rCcells Is Nothing Then
Set rAcells = rFcells 'formulas
ElseIf rFcells Is Nothing Then
Set rAcells = rCcells 'constants
Else
Set rAcells = Application.Union(rFcells, rCcells) 'Both
End If
On Error GoTo 0
rAcells.Select
End Sub

any tips

 

[Myproblem]

ring ring
I found on ozgrid that it is impossible to depart formula from numerical value
so thx anyway

 

[Peter_SSs]

To me your sample and description is not clear. Care to try again?

 

[Myproblem]

To me your sample and description is not clear. Care to try again?

well the problem is in my first message in this thread
I was searching for problem all over net and books no solution, then I found this
"SpecialCells for Formulae & Constants

While we cannot specify more than one XlCellType (e.g. xlCellTypeConstants+xlCellTypeFormulas would fail) we can use the SpecialCells method to return only used cells housing numbers on a Worksheet (formulae & constants) and omit any cells containing text (formulae & constants)." (source ozgrid.com http://www.ozgrid.com/VBA/special-cells.htm)
so I figure out there is no solution for my problem

if you have some solution it will be breakthrought in vba for specialcell method
thx

 

[Rick Rothstein]

You missed the point of Peter's reply to you. He was asking you to described your problem in enough detail and with clear enough examples so that we can determine what it is that you are trying to do. Let me say that even without knowing exactly what your problem is, I know VB well enough to say, with some confidence, that there is a solution to your problem... you just have to tell us what you want and keep in mind that you are explaining your problem to people who have no idea of what your data is, what your worksheet layout is nor what you want to do with any of it.

 

[Myproblem]

You missed the point of Peter's reply to you. He was asking you to described your problem in enough detail and with clear enough examples so that we can determine what it is that you are trying to do. Let me say that even without knowing exactly what your problem is, I know VB well enough to say, with some confidence, that there is a solution to your problem... you just have to tell us what you want and keep in mind that you are explaining your problem to people who have no idea of what your data is, what your worksheet layout is nor what you want to do with any of it.

OK, here we go again.
My problem is this: I work with coworkers on the same workbook and it is hard to figure out what they changed, because the workbook is large, has to many sheets.
I did some specific controls with VBA to check and protect some cells and ranges, but to find differences between two version of the same workbook I need to solve, to find with VBA cells there is formula AND numerical values. Example:

A B C (columns)
(row)1 5=A1=B1+C1 4 1
(row)2 7=A2=B2+C2+2 4 1

In the cell B1 values is 4, C1 values is 1, the total amount in A1 in 5 (based on formula B1+C1, 4+1)
In the cell B2 values is 4, C2 values is 1, and numerical value is 2 so the total amount in A2 in 7 (based on formula B2+C2+2, 4+1+2)

the solution should be to find,higlight cell A2, because there is formula (B2+C2) and numerical value (2).

thx

 

[Weaver]

So what you really mean is that you need to find cells containing formulas that use constants as well as cell references. The '2' in cell A2 is part of a formula, rather than the cell containing a formula and a nuimeric value, which is where I think the confusion has set in.

 

[Peter_SSs]

I think I now understand what you are after and I think PGC has done the hard work already. Have a look at this thread.

 

[Myproblem]

I think I now understand what you are after and I think PGC has done the hard work already. Have a look at this thread.

man, it works
thx to everyone

seems to me that on ozgrid.com they are wrong about this problem

cheers