Suchen und Ersetzen in Diagrammen
Schritt-für-Schritt-Anleitung
- Öffne deine Excel-Datei mit den Diagrammen, in denen Du Daten suchen und ersetzen möchtest.
- Kopiere das Tabellenblatt, das die Diagramme enthält, indem Du mit der rechten Maustaste darauf klickst und "Verschieben oder Kopieren" auswählst.
- Benenne das neue Blatt um, um die Referenzen zu aktualisieren (z.B. von "HP 9 Diag" nach "HP 8 Diag").
- Öffne die VBA-Entwicklungsumgebung mit [Alt] + [F11].
- Füge ein neues Modul hinzu: Rechtsklick auf "VBAProject (DeinDateiname)" > "Einfügen" > "Modul".
- Kopiere den folgenden VBA-Code in das Modul:
Option Explicit
Sub ChgDataSeriesRef()
Dim ws As Worksheet, wsName As String
Dim chtObj As ChartObject, cht As Chart
Dim strF As String
For Each ws In Worksheets
ws.Activate
wsName = ws.Name
For Each chtObj In ActiveSheet.ChartObjects
If InStr(wsName, "Doku") Then wsName = Replace(wsName, "Doku", "Tab")
chtObj.Activate
With ActiveChart
strF = .SeriesCollection(1).Formula
If InStr(strF, "#REF") Then
MsgBox "Fehlerhafter Bezug im Chart (" & chtObj.Chart.Name & "): " & strF
chtObj.Delete
Else
.SeriesCollection(1).Formula = CheckWorksheetName(wsName, strF)
End If
End With
Next
Next
End Sub
Private Function CheckWorksheetName(wsName As String, strF As String) As String
Dim p1 As Integer, p2 As Integer
p1 = InStr(1, strF, wsName) 'Suche wsName für X-Werte
p2 = InStr(p1 + 1, strF, wsName) ' suche wsName für Y-Achse
' Hier kann der Code zur Anpassung des wsName hinzugefügt werden
End Function
- Schließe die Entwicklungsumgebung und kehre zurück zu Excel.
- Führe das Makro aus, indem Du [Strg] + [F8] drückst und
ChgDataSeriesRef auswählst.
Häufige Fehler und Lösungen
-
Fehler: "#REF" in Diagrammen: Dieser Fehler tritt auf, wenn die Datenquelle ungültig ist. Du kannst den Fehler beheben, indem Du sicherstellst, dass die Referenzen korrekt sind, bevor Du das Makro ausführst.
-
Diagramme werden nicht aktualisiert: Vergewissere Dich, dass die Diagramme in den entsprechenden Arbeitsblättern aktiv sind. Das Makro funktioniert nur, wenn die richtigen Blätter ausgewählt sind.
Alternative Methoden
Eine manuelle Methode, um die Referenzen in Diagrammen zu ändern, besteht darin, jeden Diagramm einzeln zu öffnen und die Datenquelle manuell zu aktualisieren. Dies kann jedoch zeitaufwendig sein, insbesondere bei vielen Diagrammen.
Ein anderer Ansatz wäre die Verwendung von Excel-Daten suchen und ersetzen in Kombination mit den Diagrammschutzoptionen, um sicherzustellen, dass alle Bezüge aktualisiert werden.
Praktische Beispiele
Angenommen, Du hast ein Diagramm, das auf ='HP 9 Werte'!$C$2:$C$12 verweist. Nach dem Kopieren und Umbenennen des Blatts in "HP 8 Werte", sollte der Bezug im Diagramm automatisch auf ='HP 8 Werte'!$C$2:$C$12 aktualisiert werden, wenn Du das Makro ausführst.
Ein Beispiel für den VBA-Code, um die Änderungen in einem Diagramm zu überprüfen, könnte so aussehen:
If InStr(strF, "HP 9 Werte") Then
strF = Replace(strF, "HP 9 Werte", "HP 8 Werte")
End If
Tipps für Profis
- Nutze Kommentare im VBA-Code, um Deine Schritte zu dokumentieren. So kannst Du später leichter nachvollziehen, was jeder Teil des Codes bewirkt.
- Teste den Code in einer Kopie Deiner Arbeitsmappe, um Datenverluste zu vermeiden.
- Verwende Excel Tabstopp suchen und ersetzen, um effizienter durch die Arbeitsblätter zu navigieren.
FAQ: Häufige Fragen
1. Wie kann ich sicherstellen, dass alle Diagramme aktualisiert werden?
Führe das Makro aus, nachdem Du alle notwendigen Tabellenblätter kopiert hast. Es wird alle Diagramme auf den Blättern bearbeiten, die den entsprechenden Tabellennamen enthalten.
2. Was passiert, wenn das Makro einen Fehler findet?
Das Makro zeigt eine Fehlermeldung an, wenn es auf einen "#REF" Fehler stößt, und löscht das fehlerhafte Diagramm, es sei denn, Du kommentierst die Zeile aus.
3. Kann ich das Makro für andere Diagrammtypen verwenden?
Ja, das Makro funktioniert mit allen Diagrammtypen in Excel, solange die Diagramme die richtige Struktur für die Datenreferenz haben.