AW: Zählen von Formelbefehlen
24.11.2022 13:24:01
Formelbefehlen
Hallo Thomas,
ungefähr so:
Sub Formeln_zählen()
Dim W As Worksheet
Dim Z As Range
Dim R As Range
Dim Dic As Object
Dim Formel As String
Dim E
Application.Calculation = xlCalculationManual
With ActiveWorkbook.Worksheets.Add(after:=Sheets(ActiveWorkbook.Worksheets.Count))
.Range("A1:C1") = Array("Blatt", "Formel", "Anzahl")
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
For Each W In ActiveWorkbook.Worksheets
Set Dic = CreateObject("Scripting.Dictionary")
If W.Name .Name Then
Set R = Nothing
Set R = W.Cells.SpecialCells(xlCellTypeFormulas)
If Not R Is Nothing Then
For Each Z In R.Cells ' W.Cells.SpecialCells(xlCellTypeFormulas)
Formel = Left(Z.FormulaLocal, InStr(1, Z.FormulaLocal, "("))
E = 0
E = Dic(Formel)
Dic(Formel) = E + 1
Next
End If
For Each E In Dic.keys
With .Range("A999999").End(xlUp)
.Offset(1, 0) = W.Name
.Offset(1, 1) = "'" & CStr(E)
.Offset(1, 2) = Dic(E)
End With
Next
End If
Next
Set Dic = Nothing
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Danach eine Pivottabelle auf die gesammelte Ergebnisse. Die Analyse bezieht sich nur auf die erste Teil der Formel. Bei verschachtelten Formeln müsste man tiefer einsteigen.
VG
Yal