AW: geschütze, verknüpfte Zellen finden
Uduuh
Hallo,
in ein Modul:
Sub Formeln_suchen_extern()
Dim strName As String, sh As Worksheet
Dim FIndex As Boolean, a As Range
Application.ScreenUpdating = False
strName = "Formeln_"
FIndex = False
z = 2
For Each sh In ActiveWorkbook.Worksheets
Set R1 = sh.Range(sh.Cells(1, 1), sh.Cells(1, 1).SpecialCells(xlLastCell))
For Each a In R1.Cells
If a.HasFormula And InStr(a.FormulaLocal, "[") > 0 And a.Locked = True Then
If FIndex = False Then
Worksheets.Add After:=Sheets(Worksheets.Count)
ActiveSheet.Name = strName
Kopf = Array("Blatt", "Zelle", "Zeile", "Spalte", "Formel")
For T = 1 To 5
Cells(1, T) = Kopf(T - 1)
Cells(1, T).Font.Bold = True
Next T
FIndex = True
End If
Sheets(strName).Cells(z, 1) = sh.Name
Sheets(strName).Cells(z, 2) = a.Address(rowabsolute:=False, columnabsolute:=False)
Sheets(strName).Cells(z, 3) = a.Row
Sheets(strName).Cells(z, 4) = a.Column
Sheets(strName).Cells(z, 5) = "'" & a.FormulaLocal
z = z + 1
End If
Next a
Next sh
Columns("A:E").EntireColumn.AutoFit
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Gruß aus'm Pott
Udo