Search For Array Formulas by Cell Reference

Dear All,

Am in need to find every array formula cell references in a large workbook.

Seems the "find tab does not recognize the { in an array formula.

Does any one have any suggestion on how to find all array formulas by cell reference?

Thanks and regards John

Hi Zbor,

Thanks, but not so practical as it would be no different from looking manually at each cell.

Any other suggestions?

Regards John

Hi Sixthsense,

Thanks for your response.

Let me set up your VBA in a simple spreadsheet and test it.

Regards John

Hi,

Definitely works, thanks I could have never written a macro to do that.

Will try and adapt it to look at every worksheet in the workbook.

Kindest regards John

Hi SixthSense,

Much more elegant than I came up with.

Will go with your code

Thanks you make me humble, John

Hi,

Seems the multiple worksheet code above is not working beyond the first worksheet, not sure why.

The code I ended up with noted below in case anyone is interested.

Once again thanks for all the help, regards John

Sub Macro1()
'
' Macro1 Macro
'

 Dim WS_Count As Integer
 Dim I As Integer
 Dim rFormulas As Range
 Dim r As Range

    WS_Count = ActiveWorkbook.Worksheets.Count
    For I = 1 To WS_Count
    ActiveWorkbook.Worksheets(I).Select
      
       On Error Resume Next
       Set rFormulas = Cells.SpecialCells(xlCellTypeFormulas)
       On Error GoTo 0
       If Not rFormulas Is Nothing Then
       For Each r In rFormulas.Cells
       If r.HasArray Then r.Interior.Color = 11119017 ' DARK GREY
       Next r
       End If
       Set rFormulas = Nothing
      
    MsgBox ActiveWorkbook.Worksheets(I).Name
    Next I
    End Sub

To fix code in post #8 change

For Each Ws In ThisWorkbook.Worksheets
    On Error Resume Next

to

For Each Ws In ThisWorkbook.Worksheets
   Ws.Activate
    On Error Resume Next

Alf