I found this great code that was working and am now getting an error on
vFormulas = Application.Index(Array(dic.keys, dic.items, dic1.items), 0, 0)
and can't figure out why.
I realize there are other ones out there but this one is lighting and the only one that could handle a huge spreadsheet without Excel crashing. Any input would be appreciated.
-What got me is it was working and I haven't made any changes to the sheet itself.
Sub ListFormulas()
Dim ws As Worksheet
Dim FormulaCells As Range
Dim Cell As Range
Dim FormulaSheet As Worksheet
Dim lRow As Long
Dim dic As Object
Dim vFormulas As Variant
Dim vFormulas1 As Variant
Application.ScreenUpdating = False
lRow = 2
Set dic = CreateObject("Scripting.Dictionary")
Set dic1 = CreateObject("Scripting.Dictionary")
For Each ws In ActiveWorkbook.Worksheets
' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = ws.UsedRange.SpecialCells(xlFormulas)
On Error GoTo 0
' Exit if no formulas are found
If Not FormulaCells Is Nothing Then
' Process each formula
For Each Cell In FormulaCells
dic.Add ws.Name & "!" & Cell.Address(0, 0), "'" & Cell.Formula
dic1.Add ws.Name & "!" & Cell.Address(0, 0), Cell.Value
Next Cell
End If
Next ws
If dic.Count <> 0 Then
' Add a new worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Formula list").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formula list"
' Set up the column headings
FormulaSheet.Range("A1:B1") = Array("Address", Formula)
vFormulas = Application.Index(Array(dic.keys, dic.items, dic1.items), 0, 0)
FormulaSheet.Range("A2").Resize(UBound(vFormulas, 2), UBound(vFormulas, 1)).Value = Application.Transpose(vFormulas)
' Adjust column widths
'FormulaSheet.Columns("A:C").AutoFit
End If
End Sub
the original post:
https://www.experts-exchange.com/que...answer40553334
Bookmarks