Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1140to1144
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Bezüge zu Arbeitsblättern

Bezüge zu Arbeitsblättern
Tom
Hallo Exel-Spezialisten,
meine Datei besteht aus ca. 50 verschiedenen Arbeitsblättern, die teilweise durch Bezüge miteinander verknüpft sind.
Ist es möglich herauszufinden, welches Arbeitsblatt Bezüge zu anderen (und wenn ja: welchen) Arbeitsblättern hat? Natürlich kann ich dies mittels der Funktion „Bearbeiten...Suchen“ erfragen, aber dies erfordert bei 50 Arbeitsblättern einen hohen (und nervigen) Zeitaufwand. Optimal wäre es, wenn mir auch die betroffenen Zellen angezeigt würden.
Vielleicht hat jemand eine Makro-Lösung? Würde mich freuen!
Viele Grüße
Tom
AW: Bezüge zu Arbeitsblättern
06.03.2010 15:42:45
Tino
Hallo,
kannst mal diesen Code testen.
Ich gehe mal davon aus, dass in den Formeln nicht z. Bsp. =A1&"!" vorkommt.
Sub test()
Dim oWS As Worksheet, rngFormel As Range
Dim meAr()
Dim LCounter As Long

For Each oWS In ThisWorkbook.Worksheets
    FindFormelZellen oWS, rngFormel
    
    If Not rngFormel Is Nothing Then
        For Each rngFormel In rngFormel
            If InStr(rngFormel.FormulaLocal, "!") > 0 Then
                LCounter = LCounter + 1
                Redim Preserve meAr(1 To 3, 1 To LCounter)
                meAr(1, LCounter) = oWS.Name
                meAr(2, LCounter) = rngFormel.Address
                meAr(3, LCounter) = "'" & rngFormel.FormulaLocal
            End If
        Next rngFormel
    End If
Next oWS


If LCounter > 1 Then
    With Worksheets.Add(ThisWorkbook.Sheets(1))
        .Range("A1") = "Tabelle"
        .Range("B1") = "Zelle"
        .Range("C1") = "Formel"
        .Range("A2").Resize(Ubound(meAr, 2), Ubound(meAr)) = Application.Transpose(meAr)
         
         With .Range("A1:C1")
            .Font.Bold = True
            .EntireColumn.AutoFit
         End With
    End With
Else
    MsgBox "keine Zelle gefunden"
End If

End Sub

Sub FindFormelZellen(ByVal oWS As Worksheet, ByRef rngZelle)
Set rngZelle = Nothing
On Error Resume Next
Set rngZelle = oWS.UsedRange.SpecialCells(xlCellTypeFormulas)
End Sub
Gruß Tino
Anzeige
nochmal optimiert.
06.03.2010 15:58:54
Tino
Hallo,
jetzt sollte auch =A1&"!" drin vor kommen dürfen.
Sub test()
Dim oWS As Worksheet, rngFormel As Range
Dim meAr(), ArTabellen()
Dim LCounter As Long

For Each oWS In ThisWorkbook.Worksheets
    Redim Preserve ArTabellen(LCounter)
    ArTabellen(LCounter) = oWS.Name & "!"
    LCounter = LCounter + 1
Next oWS

LCounter = 0

For Each oWS In ThisWorkbook.Worksheets
    FindFormelZellen oWS, rngFormel
    
    If Not rngFormel Is Nothing Then
        For Each rngFormel In rngFormel
            If FindExternTab(ArTabellen, rngFormel.FormulaLocal, (oWS.Name & "!")) Then
                LCounter = LCounter + 1
                Redim Preserve meAr(1 To 3, 1 To LCounter)
                meAr(1, LCounter) = oWS.Name
                meAr(2, LCounter) = rngFormel.Address
                meAr(3, LCounter) = "'" & rngFormel.FormulaLocal
            End If
        Next rngFormel
    End If
Next oWS


If LCounter > 1 Then
    With Worksheets.Add(ThisWorkbook.Sheets(1))
        .Range("A1") = "Tabelle"
        .Range("B1") = "Zelle"
        .Range("C1") = "Formel"
        .Range("A2").Resize(Ubound(meAr, 2), Ubound(meAr)) = Application.Transpose(meAr)
         
         With .Range("A1:C1")
            .Font.Bold = True
            .EntireColumn.AutoFit
         End With
    End With
Else
    MsgBox "keine Zelle gefunden"
End If

End Sub

Sub FindFormelZellen(ByVal oWS As Worksheet, ByRef rngZelle)
Set rngZelle = Nothing
On Error Resume Next
Set rngZelle = oWS.UsedRange.SpecialCells(xlCellTypeFormulas)
End Sub

Function FindExternTab(ArTabellen, sFormel$, aktWS$) As Boolean
Dim A As Long
For A = Lbound(ArTabellen) To Ubound(ArTabellen)
    If aktWS$ <> ArTabellen(A) Then
        FindExternTab = InStr(sFormel, ArTabellen(A)) > 0
        If FindExternTab Then Exit Function
    End If
Next A
End Function
Gruß Tino
Anzeige
AW: nochmal optimiert.
06.03.2010 17:17:34
Tom
Hallo Tino,
entschuldige, dass ich mich erst jetzt bei Dir melde. Ich musste kurzfristig offline gehen.
Ich habe Deine beiden Makros getestet, allerdings konnte ich dies jetzt nur auf excel 2003 probieren (die eigentliche Datei, um die es sich dreht, ist auf einem anderen PC mit Excel 97 - da komme ich jetzt aber nicht dran).
Leider funktionieren die beiden Makros nicht. Excel 2003 wirft den Hinweis: "visual basic: Typen unverträglich"
aus. Liegt das an der Excel-Version oder müsste etwas am Makro verändert werden?
Wäre toll, wenn Du mir da weiter helfen könntest. Aber selbst wenn nicht: ich danke Dir auf alle Fälle für Deine Zeit, die Du investiert hast - ist nicht selbstverständlich!
Viele Grüße
Tom
Anzeige
Fehler kann ich nicht feststellen. oT.
06.03.2010 17:40:51
Tino
Tino@andere Datei getestet
06.03.2010 18:20:52
Tom
Hallo Tino,
ich habe eine Testdatei angelegt - die funktionierte ebenfalls reibungslos. Schade, dass es mit der "richtigen" Datei nicht klappt. Vielen Dank nochmals für Deine Hilfe
Gruss
Tom
AW: Tino@andere Datei getestet
06.03.2010 18:29:55
Tino
Hallo,
lade doch mal eine abgewandelte Form der richtigen Datei hoch,
wichtig ist das der Fehler erhalten bleibt.
Gruß Tino
AW: Tino@wie es nun doch klappt!:-)
06.03.2010 18:59:21
Tom
Hallo Tino,
ich hätte schon vorher drauf kommen können...: ich habe mal einige Blätter gelöscht und getestet, ob das Makro dann auf einmal funktioniert. Resultat: es steht und fällt mit einem einzigen Arbeitsblatt. Entferne ich von diesem die Formeln, wirft mir Dein Makro alle Infos aus!
Das heißt: keine weiteren Arbeiten notwendig, ich komme damit prima zurecht!
Nochmals Danke und einen schönen Abend!
Tom
Anzeige
wäre interessant zu wissen...
06.03.2010 19:29:53
Tino
Hallo,
woran es an dieser einen Tabelle liegt, was ist so besonders an dieser?
Gruß Tino
AW: wäre interessant zu wissen...
06.03.2010 19:39:44
Tom
Hei,
ja, geht mir auch so. Leider habe ich heute keine Zeit mehr, mich um die Datei zu kümmern (family...). Ich werde mir das aber noch mal genauer anschauen, ob da etwas vorkommt, was "anders" ist. Ich werde Dir dann "mein Ergebnis" an dieser Stelle "präsentieren" (falls das dann noch klappt) oder mit "Tino@" die Info weiter geben.
Gruß
Tom
Tino@Die Ursache, aber nicht der Grund...
07.03.2010 15:32:16
Tom
Hallo Tino,
es liegt an einer einzigen Formel, die aber völlig unspektakulär ist (Prinzip: A1 x B1/12*C1+TabelleE!A1). Dieselbe Formel habe ich testweise in ein anderes Blatt übertragen - da gibt es dann keine Probleme mit dem Makro. Probleme wirft im "richtigen" Arbeitsblatt auch nur der Bezug zur anderen Tabelle auf (nicht die Formel) - aber da gibt es nichts Geheimnisvolles...
Excel ist manchmal unergründlich, habe ich gehört und finde es hier bestätigt...
Das Wichtigste ist natürlich, dass Dein Makro (bis auf diese Merkwürdigkeit) astrein funktioniert.
Viele Grüße
Tom
Anzeige
ok. danke oT.
08.03.2010 09:00:16
Tino

303 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige