VBA: How to check if cell contains Formula

[sunilbsrv2k]

Hi,

I am trying to search through a range of cells, if any cell contains formula. If the cell contains formula, it has to pop-up "Yes".

But I am getting Error as Object Required. Please help.

here is my code:

Sub chkform()
Rng = Range("R8:R16")
For Each cell In Rng
    If cell.HasFormula = True Then
        MsgBox "Yes"
    End If
Next cell
End Sub

 

[alansidman]

Option Explicit

Sub chkform()
Dim cell As Range
Dim rng As Range
Set rng = Range("R8:R16")
For Each cell In rng
    If cell.HasFormula = True Then
        MsgBox "Yes"
    End If
Next cell
End Sub

 

[sunilbsrv2k]

Option Explicit

Sub chkform()
Dim cell As Range
Dim rng As Range
Set rng = Range("R8:R16")
For Each cell In rng
    If cell.HasFormula = True Then
        MsgBox "Yes"
    End If
Next cell
End Sub

Awesome.... Its working fine... Thank you so much

 

[sunilbsrv2k]

Hi, I have one more question with respect to "For each cell".

How to copy an entire row from this loop and paste it in another sheet.

Thanks

 

[sunilbsrv2k]

Just wanted to update this question, maybe I am not clear.

I am using the loop :

For Each cell In rng
    If cell.HasFormula = True Then
        MsgBox "Yes"
    End If
Next cell

In this loop, if I want to capture the entire row where "cell.HasFormula = True", how to get that?

Please help.

Thanks,

 

[MARK858]

Something like?.....

Sub chkform()
Dim cell As Range, rng As Range
Set rng = Range("R8:R16")

For Each cell In rng
    If cell.HasFormula = True Then cell.EntireRow.Interior.ColorIndex = 6
Next cell
End Sub

 

[mohadin]

Hi

Sub chkform()
    Dim cell As Range
    Dim rng As Range
    Dim r As Range
    Set rng = Range("R8:R16")
    For Each cell In rng
        If cell.HasFormula = True Then
            If Not r Is Nothing Then
                Set r = Union(r, cell.EntireRow)
            Else: Set r = cell.EntireRow
            End If
        End If
    Next cell
    r.Select
End Sub