AW: externe Zellverweise finden
22.08.2007 15:46:02
Rudi Maintaire
Hallo,
nicht schön, klappt aber:
Sub Formeln_suchen_extern()
'Auflistung aller Formeln mit Bezug zu einer anderen Mappe
Dim strName As String, wks As Worksheet
Dim FIndex As Boolean, a As Range, R1 As Range
Dim lngZeile As Long
Application.ScreenUpdating = False
strName = "Formeln_"
FIndex = False
lngZeile = 2
For Each wks In ActiveWorkbook.Worksheets
Set R1 = wks.Range(wks.Cells(1, 1), wks.Cells(1, 1).SpecialCells(xlLastCell))
For Each a In R1.SpecialCells(xlCellTypeFormulas)
If InStr(a.FormulaLocal, "\[") > 0 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
With Sheets(strName)
.Cells(lngZeile, 1) = wks.Name
.Cells(lngZeile, 2) = a.Address(rowabsolute:=False, columnabsolute:=False)
.Cells(lngZeile, 3) = a.Row
.Cells(lngZeile, 4) = a.Column
.Cells(lngZeile, 5) = "'" & a.FormulaLocal
End With
lngZeile = lngZeile + 1
End If
Next a
Next wks
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Gruß
Rudi
Eine Kuh macht Muh, viele Kühe machen Mühe