JUMPING TO THE NEAREST FORMULA CONTAINING CELL

[FARAZ QURESHI]

Could an expert devise a macro for me which upon running would:

1. Check row by row every cell to find if it contains a formula;
2. Indicate in a dialogue box "Formula Found!" if a formula found;
3. Jump to such a cell.

Thanx in advance!

 

[Jarek Kujawa]

Sub sth()
Dim cell As Range

For Each cell In Selection
If cell.HasFormula Then
cell.Select
MsgBox "Formula found!"
End If
Next cell

End Sub

 

[FARAZ QURESHI]

XCLent Jarek!
Thanx!

However, could you modify the code a little more so as to find cells with
references to some other cell(s), only?

For instance, if the cell contains a furmula like:

=2*10 OR
="The Date Today Is "&text(today(),"dd-mmm-yyyy")

then it is not selected. But if the formula is like:

=B1*2 OR
="The Date Today Is "&text(Sheet2!A1,"dd-mmm-yyyy")

Then it is selected, because B1 in the first and Sheet2!A1 in the other
example reflect an address/reference to another cell.

Thanx again!
--

Best Regards,
FARAZ A. QURESHI

Sub sth()
Dim cell As Range

For Each cell In Selection
If cell.HasFormula Then
cell.Select
MsgBox "Formula found!"
End If
Next cell

End Sub

 

[Jarek Kujawa]

Sub sth()
Dim cell As Range

For Each cell In Selection
If cell.HasFormula And cell.Formula = "=B1*2" Or cell.Formula =
"=""The Date Today Is ""&TEXT(Sheet2!A1,""dd-mmm-yyyy"")" Then
cell.Select
MsgBox "Formula found!"
End If
Next cell


End Sub

 

[FARAZ QURESHI]

Thanx again Jarek!

But I think I wasn't clear. What I meant was that formula referring to any
other cell being caught. B1 or Sheet2!A1 were just examples. They could have
been any.

In other words any idea how to refer to a formula which has precedent cells
in VBA?