Viele Diagramme - Datenbereich ändern

Jorge_8bc528 · 23. November 2007 um 22:36

Liebe Excelianer,

ich bereite gerade eine Datei auf 2008 vor, 2006 und 2007 existieren bereits.
Die Quelldatei füttert die Zieldatei mit verknüpften Daten, die im Jahresvergleich in Diagrammen dargestellt werden. Es sind 30 Diagramme.
Der Datenbereich der Diagramme ist immer $a:blush:d mit unterschiedlichen Zeilennummern.
Für die Daten in 2008 wird sich der Datenbereich in allen Diagrammen in $a:blush:e ändern.
Jetzt möchte ich mir gern ersparen, jedes Diagramm einzeln anzuklicken, Datenquelle zu wählen und den Datenbereich zu ändern.
Leider kann ich nicht alle Diagramme markieren und „Suchen - Ersetzen“ benutzen.

Wisst ihr eine elegante Lösung, wie ich das mit weniger Aufwand hinkriege?

Liebe Grüße

Jorge

Reinhard_fb8cb7 · 24. November 2007 um 16:12

Der Datenbereich der Diagramme ist immer $a:blush:d mit
unterschiedlichen Zeilennummern.
Für die Daten in 2008 wird sich der Datenbereich in allen
Diagrammen in $a:blush:e ändern.
Jetzt möchte ich mir gern ersparen, jedes Diagramm einzeln
anzuklicken, Datenquelle zu wählen und den Datenbereich zu
ändern.

Hi Jorge,
zeichne dir ein Makro auf waehrend du die Quelle eines Diagramms aenderst.

Dann baust du den Code in eine For-Schleife, evtl. so:

For each C in activesheet.Charts

Hier den Makrocode, ggfs. angepasst

Next C

Ich hab hier grad kein Excel, nichmal eine dt. Tastatur *fluch*

Gruss
Reinhard

Jorge_8bc528 · 24. November 2007 um 17:16

Hi Jorge,
zeichne dir ein Makro auf waehrend du die Quelle eines
Diagramms aenderst.

Dann baust du den Code in eine For-Schleife, evtl. so:

For each C in activesheet.Charts

Hier den Makrocode, ggfs. angepasst

Next C

Ich hab hier grad kein Excel, nichmal eine dt. Tastatur
*fluch*

Salü Reinhard,

wo bist du denn auch schon wieder??

Hab das erste Diagramm geändert und Makro aufgezeichnet.

Heraus kam folgendes inkl. deiner beiden Ergänzungen und meiner Anpassung:

Sub DiagrammneuesJahr()
’
’ DiagrammneuesJahr Makro
’ Makro am 24.11.2007 von Internet aufgezeichnet
’
’ Tastenkombination: Strg+j
’
For Each A In ActiveSheet.Charts
ActiveSheet.ChartObjects(„Diagramm 29“).Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets(„Jahresvergleich“).Range(„A4:E5“), _
PlotBy:=xlRows
ActiveWindow.Visible = False
Next A
End Sub

Da es dann zu einem Laufzeitfehler 438 ( Objekt unterstützt diese Eigenschaft oder Methode nicht) kommt, habe ich wohl, wie von mir vermutet, etwas falsch gemacht.
Aber ich hab mich bemüht!

Kannst du das noch hinbiegen?

Gruß wo auch immer hin.

Jorge

Reinhard_fb8cb7 · 24. November 2007 um 18:01

Salü Jorge,

wo bist du denn auch schon wieder??

nicht da wo ich gerne wäre, naemlich da wo es wärmer ist
Hier im normalen Inetcafe sind die Rechner unterschiedlich ausgeruestet, jetzt habe ich halt Pech weil die „guten“ besetzt sind.

Bitte grundsätzlich immer die Zeile angeben wo der Fehler auftritt.

Aus dem Bauch heraus wuerde ich das so schreiben (Diagramme sind manchmal zickig und erfordern ein Select)

Sub DiagrammneuesJahr()
For Each A In ActiveSheet.Charts
A.Activate
A.ChartArea.Select
Selection.SetSourceData Source:=Sheets(„Jahresvergleich“).Range(„A4:E5“), PlotBy:=xlRows
Next A
End Sub

Was das visible=False sollte weiss ich grad nicht, habe es erst mal entfernt.

Kannst du das noch hinbiegen?

Ich denke schon, brauche nur mal kurz einen ExcelRechner, also morgen oder so.

Gruss
Reinhard

Jorge_8bc528 · 25. November 2007 um 01:29

Hallo Reinhard,

Hab deine Anweisungen befolgt, leider gibt es dieselbe Fehlermeldung.

Bitte grundsätzlich immer die Zeile angeben wo der Fehler
auftritt.

Wenn ich debuggen anklicke, wird diese Zeile gelb unterlegt:

For Each A In ActiveSheet.Charts

Aus dem Bauch heraus wuerde ich das so schreiben (Diagramme
sind manchmal zickig und erfordern ein Select)

Sub DiagrammneuesJahr()
For Each A In ActiveSheet.Charts
A.Activate
A.ChartArea.Select
Selection.SetSourceData
Source:=Sheets(„Jahresvergleich“).Range(„A4:E5“),
PlotBy:=xlRows
Next A
End Sub

Was das visible=False sollte weiss ich grad nicht, habe es
erst mal entfernt.

Das hab ich auch noch mal eingebaut, bringt aber auch keine Erfolg.

Kannst du das noch hinbiegen?

Ich denke schon, brauche nur mal kurz einen ExcelRechner, also
morgen oder so.

Da würde ich mich freuen. Und gute Nacht.

Grüße vom großen See.

Jorge

Reinhard_fb8cb7 · 25. November 2007 um 14:05

Hallo Jorge,

Wenn ich debuggen anklicke, wird diese Zeile gelb unterlegt:

For Each A In ActiveSheet.Charts

ohne Excel auf dem PC bin ich doch recht hilflos

Die Datei: http://www.hostarea.de/server-11/November-cfe3483283…

hat in Tabelle3 Daten in A1:F15, 3 PunktDiagramme, und 2 Schaltflächen mit denen man die nachstehenden Makros „mehr“ und „weniger“ startet.
Je nach Makro wird die Spaltenanzahl der Datenquelle der Diagramme erhöht bzw vermindert.

Insofern kannst du das Programm einige Jahre lang benutzen *gg*

Problem war, mit SetSourcedata weist man einen Datenbereich zu.
Ich fand aber keinen Befehl, der quasi GetSourceData entspricht, mir also den Datenbereich eines Diagramms zurückliefert.
Also strickte ich mir eine Funktion die das macht und gleichzeitig diesen verändert.

Getestet auf XL97, deshalb Split nicht benutzt.
Man beachte die Warnung im Funktionscode.

Gruß
Reinhard

in Modul1:

Option Explicit
'
Sub mehr()
Dim A As Object
For Each A In ActiveSheet.ChartObjects
 A.Activate
 ActiveChart.SetSourceData Source:=Sheets("Tabelle3").Range(strChangeSourceData(1)), \_
 PlotBy:=xlColumns
Next A
Range("A1").Select
End Sub
'
Sub weniger()
Dim A As Object
For Each A In ActiveSheet.ChartObjects
 A.Activate
 ActiveChart.SetSourceData Source:=Sheets("Tabelle3").Range(strChangeSourceData(-1)), \_
 PlotBy:=xlColumns
Next A
Range("A1").Select
End Sub
'
Function strChangeSourceData(Anz As Integer) As String
Dim pos1 As Byte, pos2 As Byte, Ende As Range, n
' Warnung: es kommt Fehler 1004, wenn Spalte leer!
' in der zweiten whileschleife kann dann .Formula nicht zugewiesen werden!
' Msgbox .Formula klappt dann auch nicht mit gleichem Fehler.
With ActiveChart.SeriesCollection(1)
 pos1 = InStr(1, .Formula, "!")
 pos2 = InStr(pos1 + 1, .Formula, ":")
 strChangeSourceData = Mid(.Formula, pos1 + 1, pos2 - pos1 - 1)
End With
With ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count)
 pos1 = InStr(pos2 + 1, .Formula, ":")
 pos2 = InStr(pos1, .Formula, ",")
 Set Ende = Range(Mid(.Formula, pos1 + 1, pos2 - pos1 - 1))
 strChangeSourceData = strChangeSourceData & ":" & Ende.Offset(0, Anz).Address
End With
End Function

Jorge_8bc528 · 26. November 2007 um 21:23

Hallo Reinhard,

dir ist hoffentlich wärmer!

Vielen Dank für das Makro.
Hab die Namen des Tabellenblatts geändert und sogar die Komboboxen einfügen können. Jetzt macht das Makro was, leider nicht das, was ich will.
Ich habe alle Diagramme mit „Datenbereich“ ( nicht Reihe) in Spalten definiert.
Klicke ich auf „Mehr“ ändern sich alle Diagramme in „Reihe“. Es wird zwar tatsächlich die neue Spalte hinzugefügt, aber die Diagramme stimmen ja jetzt nicht mehr.
Kannst du da am Makro was ändern oder muss ich alle Diagramme anders anlegen?

Zum anderen: Meine Quelldaten stehen nicht ununterbrochen neben- und untereinander. Die auzuwertenden Daten sind natürlich „schön“ formatiert und haben ein bis mehrere Zeilen, auch leere, zwischen den einzelnen Quelldatenbereichen.
Wenn ich ein zweites mal auf „Mehr“ klicke, kommt die Meldung „Laufzeitfehler 6: Überlauf“. Nach Debuggen wird diese Zeile gelb unterlegt:

strChangeSourceData = Mid(.Formula, pos1 + 1, pos2 - pos1 - 1)

Könnte das damit zusammenhängen?

Ich würd mich freuen, wenn du, mit für dich vertretbarem Zeitaufwand, noch mal drüberschauen würdest, weil ich dieses Makro so schön einfach in der Bedienung finde. Es hat übrigens keine Eile.

Liebe Grüße

Jorge

PS: Darf jeder das Hostarea benutzen oder nur die Experten?

Reinhard_fb8cb7 · 27. November 2007 um 18:44

Hallo Jorge,

dir ist hoffentlich wärmer!

Ich hoffe bald, sobald ich die 30 Millionen im Lotto geholt habe frier ich nie mehr

Hab die Namen des Tabellenblatts geändert und sogar die
Komboboxen einfügen können. Jetzt macht das Makro was, leider
nicht das, was ich will.
Ich habe alle Diagramme mit „Datenbereich“ ( nicht Reihe) in
Spalten definiert.

Ja, dachte ich mir als ich deinen Vba-Code sah daß es da Komplikationen gibt.

Klicke ich auf „Mehr“ ändern sich alle Diagramme in „Reihe“.
Es wird zwar tatsächlich die neue Spalte hinzugefügt, aber die
Diagramme stimmen ja jetzt nicht mehr.
Kannst du da am Makro was ändern oder muss ich alle Diagramme
anders anlegen?

Beides ist möglich.

Zum anderen: Meine Quelldaten stehen nicht ununterbrochen
neben- und untereinander. Die auzuwertenden Daten sind
natürlich „schön“ formatiert und haben ein bis mehrere Zeilen,
auch leere, zwischen den einzelnen Quelldatenbereichen.
Wenn ich ein zweites mal auf „Mehr“ klicke, kommt die Meldung
„Laufzeitfehler 6: Überlauf“. Nach Debuggen wird diese Zeile
gelb unterlegt:

strChangeSourceData = Mid(.Formula, pos1 + 1, pos2 - pos1 - 1)

Normalerweise müßte „String“ in der Form bis 2 Milionen Zeichen gehen, kann mir den Überlauf grad nicht erklären.

Ich würd mich freuen, wenn du, mit für dich vertretbarem
Zeitaufwand, noch mal drüberschauen würdest, weil ich dieses
Makro so schön einfach in der Bedienung finde. Es hat übrigens
keine Eile.

Es wäre für mich bedeutend einfacher ich hätte deine Komplettdatei, genauer gesagt wegen Datenschutz eine Beispieldatei die exakt so aufgebaut ist.

PS: Darf jeder das Hostarea benutzen oder nur die Experten?

Jeder, ist freie Hochladadresse. Allerdings fangen die an mich zu nerven, weil ich da zunehmend Werbungsfenster wegklicken muss.

Wenn jmd. eine bessere Hochladadresse kennt, gerne, als her mit der Adresse.
Sie muß einfach zu bedienen sein. Ich wähle eine Datei auf meinem Rechner aus, lade sie hoch, erhalte die Adresse der hochgeladenen Datei, fertig.
Möglichst kein Schnickschnack mit Anmeldung und passwort, Werbung usw.
Mit Werbung meine ich Popup Fenster die aufgehen und die ich wegklicken muß.
Wenn auf der Seite selbst was auch immer für Werbung ist so ist mir das völlig egal, schaue ich eh nicht hin, ich schaue nur auf den Button „Durchsuchen“, dann „Hochladen“.

-) Analog zu w-w-w, mir doch wurscht was auf der Startseite steht, ich sehe nur die Felder wo ich Mailadresse und PW eingebe, der Rest mag blinken wie er will…

Dies aber bitte niccht an Pro7 petzen.
Gruß
Reinhard

Jorge_8bc528 · 27. November 2007 um 22:23

Hallo Reinhard,

Es wäre für mich bedeutend einfacher ich hätte deine
Komplettdatei, genauer gesagt wegen Datenschutz eine
Beispieldatei die exakt so aufgebaut ist.

http://www.hostarea.de/server-11/November-50b8965d2f…

Ich hoffe mal, das ich das jetzt richtig gemacht habe.

Diese große Datei ist inzwischen unterteilt in die Dateien 92 06, 92 07; 92 08 wird dann folgen; zudem gibt es jetzt die Datei „Jahresvergleich“ mit den Tabellenblättern „Jahresvergleich“ und „dito Diagramme“. Alle sind gleich aufgebaut, wie die hostarea-Tabellenblätter. In Letztere werden die Auswertungsdaten verknüft.

Dies ist die Datei eines Laien, also bitte nicht zu laut lachen, wenn ich mir bei manchen Formeln einen abgekünstelt habe.

Ich hatte ja schon geschrieben, dass das Ganze keine Eile hat.

Vielen Dank und liebe Grüße vom verschwiegenen

Jorge, der dir die 30 Mios gerne gönnt, damit es dir warm wird…

Reinhard_fb8cb7 · 29. November 2007 um 17:23

Hallo jorge,

Diese große Datei ist inzwischen unterteilt in die Dateien 92
06, 92 07; 92 08 wird dann folgen; zudem gibt es jetzt die
Datei „Jahresvergleich“ mit den Tabellenblättern
„Jahresvergleich“ und „dito Diagramme“. Alle sind gleich
aufgebaut, wie die hostarea-Tabellenblätter. In Letztere
werden die Auswertungsdaten verknüft.

Die Diagramme sind nicht alle identisch aufgebaut, waas das Ganze kompliziert.
in "Daten 07 habe ich paar Formeln geändert bzw. durch vergebene Namen ersetzt. Ist farblich markiert wo das geschah.

http://www.hostarea.de/server-11/November-136b17c2d0…

Nachfolgend der Code, der mehr schlecht als recht aber irggnedwie doch stellenweise funktioniert

Ups, kann den Code gar nicht posten, hab ja hier kein Tabellenfunktionsrogramm.

Gruß
Reinhard

Jorge, der dir die 30 Mios gerne gönnt, damit es dir warm
wird…

Die waren mir zuwenig, ich warte noch ab

Jorge_8bc528 · 29. November 2007 um 21:41

Hallo Reinhard,

in "Daten 07 habe ich paar Formeln geändert bzw. durch
vergebene Namen ersetzt. Ist farblich markiert wo das geschah.

Na, du bist ja der Beste! Ich bin ein Dummie! Ich gehe mal davon aus, dass du zur Zeit hunderte von Lottoscheinen ausfüllst; wenn du fertig bist, schreib mir doch bitte, wie das geht mit z.B. „=AlSp“ etc. Das ist ja supereinfach.

Was bedeutet das Blatt „abc“ und zB diese Formel:
"’=SERIES(Jahresvergleich!$D$8,Jahresvergleich!$A$9:blush:A$15,Jahresvergleich!$D$9:blush:D$15,3)

Und wie machst du es, dass die Balken im Diagr. pro Jahr die selbe Farbe haben? Einzeln ändern kann ich auch, aber geht das auch mit einer Voreinstellung oder was ähnlichem ?

Ich werde mich am Samsatgnachmittag erst wieder mit der Datei befassen können während ein paar Überstunden vor Bremen:HSV. Als Hamburger tippe ich auf 1:4

http://www.hostarea.de/server-11/November-136b17c2d0…

Nachfolgend der Code, der mehr schlecht als recht aber
irggnedwie doch stellenweise funktioniert

Sonen kurzen Code hatte ich noch nie…

Ups, kann den Code gar nicht posten, hab ja hier kein
Tabellenfunktionsrogramm.

Deshalb!

Gruß

Jorge

Die waren mir zuwenig, ich warte noch ab

Ich drück dir die Daumen!!

Reinhard_fb8cb7 · 30. November 2007 um 18:56

Hallo Jorge,

in "Daten 07 habe ich paar Formeln geändert bzw. durch
vergebene Namen ersetzt. Ist farblich markiert wo das geschah.

ausfüllst; wenn du fertig bist, schreib mir doch bitte, wie
das geht mit z.B. „=AlSp“ etc. Das ist ja supereinfach.

Unter Einfügen–Namen–sieht man was sich dahinter verbirgt.
Es macht die Formeln einfacher pflegbar, die Datei kleiner und wenn man die Namen in anderen Formeln benutzt,deren Funktion leichter verständlich.
MwSt
versteht jeder,
bei AL56 muß man erst schauen was da eigentlich steht.
Also ist A1+A1*Mwst leichter lesbar als A1+A1*AL56.

Was bedeutet das Blatt „abc“ und zB diese Formel:
"’=SERIES(Jahresvergleich!$D$8,Jahresvergleich!$A$9:blush:A$15,Jahresvergleich!$D$9:blush:D$15,3)

abc war zum Testen. Noch weiß ich nicht wie man direkt die „Sourcedata“ eines Diagrammes ausliest, ich kann aber die „Sourcedaten“ einzelner datenreihen eines Diagrammes auslesen indem ich „.Formula“ auslese, und in .Formula der letzten Datenreihe der 28 Diagramme steht halt das was man in abc sieht.

Und Das Blatt Feiertage ist wohl klar, da listest du die Feiertage des Jahres auf, damit dann in Blatt Daten die Wochentage mit 1-7 und ein Feiertag als 8 eingetragen wird, habe die Formeln diesbezueglich abgeändert.

Und wie machst du es, dass die Balken im Diagr. pro Jahr die
selbe Farbe haben? Einzeln ändern kann ich auch, aber geht das
auch mit einer Voreinstellung oder was ähnlichem ?

Ändere mal in einem Diagramm die Farben nach Wunsch und zeichne dabei ein makro auf, darin siehst du dann die Farbwerte der einzelenen Farben. Diese setzt du dann im Makro „Farben()“ anstelle von 2,3,4 ein. und startest Makro Farben.

Ich werde mich am Samsatgnachmittag erst wieder mit der Datei
befassen können während ein paar Überstunden vor Bremen:HSV.
Als Hamburger tippe ich auf 1:4

Drücke dir die Daumen:smile:

Gruß
Reinhard

Jorge_8bc528 · 3. Dezember 2007 um 21:53

Lieber Reinhard,

Unter Einfügen–Namen–sieht man was sich dahinter verbirgt.
Es macht die Formeln einfacher pflegbar, die Datei kleiner und
wenn man die Namen in anderen Formeln benutzt,deren Funktion
leichter verständlich.

Da habe ich mich am Samstagabend mit beschäftigt und um 3/4 1 völlig frustriert das Zauberbuch bei Amazon bestellt.
Heute abend habe ich es auch ohne Buch kapiert. Total gute Idee.

Was bedeutet das Blatt „abc“ und zB diese Formel:
"’=SERIES(Jahresvergleich!$D$8,Jahresvergleich!$A$9:blush:A$15,Jahresvergleich!$D$9:blush:D$15,3)

abc war zum Testen. Noch weiß ich nicht wie man direkt die
„Sourcedata“ eines Diagrammes ausliest, ich kann aber die
„Sourcedaten“ einzelner datenreihen eines Diagrammes auslesen
indem ich „.Formula“ auslese, und in .Formula der letzten
Datenreihe der 28 Diagramme steht halt das was man in abc
sieht.

Das Makro bezüglich Diagrammerweiterung tut nicht so, wie es soll. Es findet ‚mehr‘ nicht mehr ( ich übrigens auch nicht…)

Und Das Blatt Feiertage ist wohl klar, da listest du die
Feiertage des Jahres auf, damit dann in Blatt Daten die
Wochentage mit 1-7 und ein Feiertag als 8 eingetragen wird,
habe die Formeln diesbezueglich abgeändert.

Das war nun überhaupt nicht klar

Diese Formel habe ich heute abend mit Excel-Hilfe folgendermaßen entziffert:

WENN(‚Daten 07‘!F2="";"";WENN(ISTFEHLER(SVERWEIS(‚Daten 07‘!F2;Feiertage!$A$1:blush:A$100;1;0));WOCHENTAG(‚Daten 07‘!F2;2);8))

Wenn F2 leer, soll es leer bleiben, sonst soll es prüfen, ob F2 mit genau einem Eintrag aus „Feiertage“ ( wegen der 0 ) übereinstimmt; wenn nicht, soll kein „#NV“ erscheinen ( deshalb Istfehler); wenn das WAHr ist, soll „Wochentag“ benutzt werden; wenn Falsch, also ein Eintrag gefunden wurde, soll „8“ eingetragen werden.

Gut so??

Und wie machst du es, dass die Balken im Diagr. pro Jahr die
selbe Farbe haben? Einzeln ändern kann ich auch, aber geht das
auch mit einer Voreinstellung oder was ähnlichem ?

Ändere mal in einem Diagramm die Farben nach Wunsch und
zeichne dabei ein makro auf, darin siehst du dann die
Farbwerte der einzelenen Farben. Diese setzt du dann im Makro
„Farben()“ anstelle von 2,3,4 ein. und startest Makro Farben.

Das muss ich, wie auch die Diagrammerweiterung, nächstes Jahr noch mal angehen.

Auf mich warten etliche „Quelldateien“, die ich, wie etliche „Ziel- = Auswertungsdateien“ auf 2008 anpassen muss.

Bremen:HSV.

Als Hamburger tippe ich auf 1:4

Nach dem 1:2 war der Abend erst mal gelaufen.
Dir gings ja wohl nach den Lottozahlen ähnlich…
Und für die nächste Ziehung: ich

Drücke dir die Daumen:smile:

Gruß

Jorge