Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 17:41:19
Link erstellen mit Formel oder VBA?
23.04.2024 17:00:22
Summe und Ausgabe
23.04.2024 16:27:34
Excel Starthinweis - Arbeitsmappenleistung überprüfen ...
23.04.2024 16:15:16
Mittels VBA vordefinierte Kommentare in Zellen einfügen?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1480to1484
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1480to1484
1480to1484
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

in Diagrammen suchen --> Ersetzen

in Diagrammen suchen --> Ersetzen
21.03.2016 23:28:10
trine
Hallo zusammen,
ich möchte gerne in einer Datei 30 Punkt-Diagramme erstellen, die jeweils 120 Datenreihen enthalten. Von einem Diagramm zum anderen unterscheidet sich nur die Spalte für die y-Werte. In einer Kopie des ersten Diagrammes möchte ich mit einem Makro die Spalten der y-Werte mit "suchen->ersetzen" jeder Datenreihe ändern.
Folgendes Makro habe ich durch aufzeichnen erhalten, als ich die Spalten eines Diagrammes manuell geändert habe. Leider wird das Makro für die Änderung der anderen Diagramme nicht ausgeführt.
Ändere ich jedes Diagramm manuell, dauert es Tage:o(. Daher bin ich für jeden Tipp dankbar.
Viele Grüße
Martina 
Sub Makro2()
ActiveChart.SeriesCollection(119).Name = "=Datatable2!$H$1112:$K$1112"
ActiveChart.SeriesCollection(119).XValues = "=Datatable2!$L$1112:$L$1122"
ActiveChart.SeriesCollection(119).Values = "=Datatable2!$AL$1112:$AL$1122"
ActiveChart.SeriesCollection(118).Name = "=Datatable2!$H$1101:$K$1101"
ActiveChart.SeriesCollection(118).XValues = "=Datatable2!$L$1101:$L$1111"
ActiveChart.SeriesCollection(118).Values = "=Datatable2!$AL$1101:$AL$1111"
ActiveChart.SeriesCollection(117).Name = "=Datatable2!$H$1090:$K$1090"
ActiveChart.SeriesCollection(117).XValues = "=Datatable2!$L$1090:$L$1100"
ActiveChart.SeriesCollection(117).Values = "=Datatable2!$AL$1090:$AL$1100"
ActiveChart.SeriesCollection(116).Name = "=Datatable2!$H$1086:$K$1086"
ActiveChart.SeriesCollection(116).XValues = "=Datatable2!$L$1086:$L$1089"
ActiveChart.SeriesCollection(116).Values = "=Datatable2!$AL$1086:$AL$1089"
ActiveChart.SeriesCollection(115).Name = "=Datatable2!$H$1075:$K$1075"
ActiveChart.SeriesCollection(115).XValues = "=Datatable2!$L$1075:$L$1085"
ActiveChart.SeriesCollection(115).Values = "=Datatable2!$AL$1075:$AL$1085"
ActiveChart.SeriesCollection(114).Name = "=Datatable2!$H$1064:$K$1064"
ActiveChart.SeriesCollection(114).XValues = "=Datatable2!$L$1064:$L$1074"
ActiveChart.SeriesCollection(114).Values = "=Datatable2!$AL$1064:$AL$1074"
ActiveChart.SeriesCollection(113).Name = "=Datatable2!$H$1053:$K$1053"
ActiveChart.SeriesCollection(113).XValues = "=Datatable2!$L$1053:$L$1063"
ActiveChart.SeriesCollection(113).Values = "=Datatable2!$AL$1053:$AL$1063"
ActiveChart.SeriesCollection(112).Name = "=Datatable2!$H$1049:$K$1049"
ActiveChart.SeriesCollection(112).XValues = "=Datatable2!$L$1049:$L$1052"
ActiveChart.SeriesCollection(112).Values = "=Datatable2!$AL$1049:$AL$1052"
ActiveChart.SeriesCollection(111).Name = "=Datatable2!$H$1038:$K$1038"
ActiveChart.SeriesCollection(111).XValues = "=Datatable2!$L$1038:$L$1048"
ActiveChart.SeriesCollection(111).Values = "=Datatable2!$AL$1038:$AL$1048"
ActiveChart.SeriesCollection(110).Name = "=Datatable2!$H$1027:$K$1027"
ActiveChart.SeriesCollection(110).XValues = "=Datatable2!$L$1027:$L$1037"
ActiveChart.SeriesCollection(110).Values = "=Datatable2!$AL$1027:$AL$1037"
ActiveChart.SeriesCollection(109).Name = "=Datatable2!$H$1016:$K$1016"
ActiveChart.SeriesCollection(109).XValues = "=Datatable2!$L$1016:$L$1026"
ActiveChart.SeriesCollection(109).Values = "=Datatable2!$AL$1016:$AL$1026"
ActiveChart.SeriesCollection(108).Name = "=Datatable2!$H$1012:$K$1012"
ActiveChart.SeriesCollection(108).XValues = "=Datatable2!$L$1012:$L$1015"
ActiveChart.SeriesCollection(108).Values = "=Datatable2!$AL$1012:$AL$1015"
ActiveChart.SeriesCollection(107).Name = "=Datatable2!$H$1001:$K$1001"
ActiveChart.SeriesCollection(107).XValues = "=Datatable2!$L$1001:$L$1011"
ActiveChart.SeriesCollection(107).Values = "=Datatable2!$AL$1001:$AL$1011"
ActiveChart.SeriesCollection(106).Name = "=Datatable2!$H$990:$K$990"
ActiveChart.SeriesCollection(106).XValues = "=Datatable2!$L$990:$L$1000"
ActiveChart.SeriesCollection(106).Values = "=Datatable2!$AL$990:$AL$1000"
ActiveChart.SeriesCollection(105).Name = "=Datatable2!$H$979:$K$979"
ActiveChart.SeriesCollection(105).XValues = "=Datatable2!$L$979:$L$989"
ActiveChart.SeriesCollection(105).Values = "=Datatable2!$AL$979:$AL$989"
ActiveChart.SeriesCollection(104).Name = "=Datatable2!$H$975:$K$975"
ActiveChart.SeriesCollection(104).XValues = "=Datatable2!$L$975:$L$978"
ActiveChart.SeriesCollection(104).Values = "=Datatable2!$AL$975:$AL$978"
ActiveChart.SeriesCollection(103).Name = "=Datatable2!$H$964:$K$964"
ActiveChart.SeriesCollection(103).XValues = "=Datatable2!$L$964:$L$974"
ActiveChart.SeriesCollection(103).Values = "=Datatable2!$AL$964:$AL$974"
ActiveChart.SeriesCollection(102).Name = "=Datatable2!$H$953:$K$953"
ActiveChart.SeriesCollection(102).XValues = "=Datatable2!$L$953:$L$963"
ActiveChart.SeriesCollection(102).Values = "=Datatable2!$AL$953:$AL$963"
ActiveChart.SeriesCollection(101).Name = "=Datatable2!$H$942:$K$942"
ActiveChart.SeriesCollection(101).XValues = "=Datatable2!$L$942:$L$952"
ActiveChart.SeriesCollection(101).Values = "=Datatable2!$AL$942:$AL$952"
ActiveChart.SeriesCollection(100).Name = "=Datatable2!$H$938:$K$938"
ActiveChart.SeriesCollection(100).XValues = "=Datatable2!$L$938:$L$941"
ActiveChart.SeriesCollection(100).Values = "=Datatable2!$AL$938:$AL$941"
ActiveChart.SeriesCollection(99).Name = "=Datatable2!$H$927:$K$927"
ActiveChart.SeriesCollection(99).XValues = "=Datatable2!$L$927:$L$937"
ActiveChart.SeriesCollection(99).Values = "=Datatable2!$AL$927:$AL$937"
ActiveChart.SeriesCollection(98).Name = "=Datatable2!$H$916:$K$916"
ActiveChart.SeriesCollection(98).XValues = "=Datatable2!$L$916:$L$926"
ActiveChart.SeriesCollection(98).Values = "=Datatable2!$AL$916:$AL$926"
ActiveChart.SeriesCollection(97).Name = "=Datatable2!$H$905:$K$905"
ActiveChart.SeriesCollection(97).XValues = "=Datatable2!$L$905:$L$915"
ActiveChart.SeriesCollection(97).Values = "=Datatable2!$AL$905:$AL$915"
ActiveChart.SeriesCollection(96).Name = "=Datatable2!$H$901:$K$901"
ActiveChart.SeriesCollection(96).XValues = "=Datatable2!$AB$901:$AB$904"
ActiveChart.SeriesCollection(96).Values = "=Datatable2!$AL$901:$AL$904"
ActiveChart.SeriesCollection(95).Name = "=Datatable2!$H$890:$K$890"
ActiveChart.SeriesCollection(95).XValues = "=Datatable2!$L$890:$L$900"
ActiveChart.SeriesCollection(95).Values = "=Datatable2!$AL$890:$AL$900"
ActiveChart.SeriesCollection(94).Name = "=Datatable2!$H$879:$K$879"
ActiveChart.SeriesCollection(94).XValues = "=Datatable2!$L$879:$L$889"
ActiveChart.SeriesCollection(94).Values = "=Datatable2!$AL$879:$AL$889"
ActiveChart.SeriesCollection(93).Name = "=Datatable2!$H$868:$K$868"
ActiveChart.SeriesCollection(93).XValues = "=Datatable2!$L$868:$L$878"
ActiveChart.SeriesCollection(93).Values = "=Datatable2!$AL$868:$AL$878"
ActiveChart.SeriesCollection(92).Name = "=Datatable2!$H$864:$K$864"
ActiveChart.SeriesCollection(92).XValues = "=Datatable2!$L$864:$L$867"
ActiveChart.SeriesCollection(92).Values = "=Datatable2!$AL$864:$AL$867"
ActiveChart.SeriesCollection(91).Name = "=Datatable2!$H$853:$K$853"
ActiveChart.SeriesCollection(91).XValues = "=Datatable2!$L$853:$L$863"
ActiveChart.SeriesCollection(91).Values = "=Datatable2!$AL$853:$AL$863"
ActiveChart.SeriesCollection(90).Name = "=Datatable2!$H$842:$K$842"
ActiveChart.SeriesCollection(90).XValues = "=Datatable2!$L$842:$L$852"
ActiveChart.SeriesCollection(90).Values = "=Datatable2!$AL$842:$AL$852"
ActiveChart.SeriesCollection(89).Name = "=Datatable2!$H$831:$K$831"
ActiveChart.SeriesCollection(89).XValues = "=Datatable2!$L$831:$L$841"
ActiveChart.SeriesCollection(89).Values = "=Datatable2!$AL$831:$AL$841"
ActiveChart.SeriesCollection(88).Name = "=Datatable2!$H$827:$K$827"
ActiveChart.SeriesCollection(88).XValues = "=Datatable2!$L$827:$L$830"
ActiveChart.SeriesCollection(88).Values = "=Datatable2!$AL$827:$AL$830"
ActiveChart.SeriesCollection(87).Name = "=Datatable2!$H$816:$K$816"
ActiveChart.SeriesCollection(87).XValues = "=Datatable2!$L$816:$L$826"
ActiveChart.SeriesCollection(87).Values = "=Datatable2!$AL$816:$AL$826"
ActiveChart.SeriesCollection(86).Name = "=Datatable2!$H$805:$K$805"
ActiveChart.SeriesCollection(86).XValues = "=Datatable2!$L$805:$L$815"
ActiveChart.SeriesCollection(86).Values = "=Datatable2!$AL$805:$AL$815"
ActiveChart.SeriesCollection(85).Name = "=Datatable2!$H$794:$K$794"
ActiveChart.SeriesCollection(85).XValues = "=Datatable2!$L$794:$L$804"
ActiveChart.SeriesCollection(85).Values = "=Datatable2!$AL$794:$AL$804"
ActiveChart.SeriesCollection(84).Name = "=Datatable2!$H$790:$K$790"
ActiveChart.SeriesCollection(84).XValues = "=Datatable2!$L$790:$L$793"
ActiveChart.SeriesCollection(84).Values = "=Datatable2!$AL$790:$AL$793"
ActiveChart.SeriesCollection(83).Name = "=Datatable2!$H$779:$K$779"
ActiveChart.SeriesCollection(83).XValues = "=Datatable2!$L$779:$L$789"
ActiveChart.SeriesCollection(83).Values = "=Datatable2!$AL$779:$AL$789"
ActiveChart.SeriesCollection(82).Name = "=Datatable2!$H$768:$K$768"
ActiveChart.SeriesCollection(82).XValues = "=Datatable2!$L$768:$L$778"
ActiveChart.SeriesCollection(82).Values = "=Datatable2!$AL$768:$AL$778"
ActiveChart.SeriesCollection(81).Name = "=Datatable2!$H$757:$K$757"
ActiveChart.SeriesCollection(81).XValues = "=Datatable2!$L$757:$L$767"
ActiveChart.SeriesCollection(81).Values = "=Datatable2!$AL$757:$AL$767"
ActiveChart.SeriesCollection(80).Name = "=Datatable2!$H$753:$K$753"
ActiveChart.SeriesCollection(80).XValues = "=Datatable2!$L$753:$L$756"
ActiveChart.SeriesCollection(80).Values = "=Datatable2!$AL$753:$AL$756"
ActiveChart.SeriesCollection(79).Name = "=Datatable2!$H$742:$K$742"
ActiveChart.SeriesCollection(79).XValues = "=Datatable2!$L$742:$L$752"
ActiveChart.SeriesCollection(79).Values = "=Datatable2!$AL$742:$AL$752"
ActiveChart.SeriesCollection(78).Name = "=Datatable2!$H$731:$K$731"
ActiveChart.SeriesCollection(78).XValues = "=Datatable2!$L$731:$L$741"
ActiveChart.SeriesCollection(78).Values = "=Datatable2!$AL$731:$AL$741"
ActiveChart.SeriesCollection(77).Name = "=Datatable2!$H$720:$K$720"
ActiveChart.SeriesCollection(77).XValues = "=Datatable2!$L$720:$L$730"
ActiveChart.SeriesCollection(77).Values = "=Datatable2!$AL$720:$AL$730"
ActiveChart.SeriesCollection(76).Name = "=Datatable2!$H$716:$K$716"
ActiveChart.SeriesCollection(76).XValues = "=Datatable2!$L$716:$L$719"
ActiveChart.SeriesCollection(76).Values = "=Datatable2!$AL$716:$AL$719"
ActiveChart.SeriesCollection(75).Name = "=Datatable2!$H$705:$K$705"
ActiveChart.SeriesCollection(75).XValues = "=Datatable2!$L$705:$L$715"
ActiveChart.SeriesCollection(75).Values = "=Datatable2!$AL$705:$AL$715"
ActiveChart.SeriesCollection(74).Name = "=Datatable2!$H$694:$K$694"
ActiveChart.SeriesCollection(74).XValues = "=Datatable2!$L$694:$L$704"
ActiveChart.SeriesCollection(74).Values = "=Datatable2!$AL$694:$AL$704"
ActiveChart.SeriesCollection(73).XValues = "=Datatable2!$L$683:$L$693"
ActiveChart.SeriesCollection(73).Name = "=Datatable2!$H$683:$K$683"
ActiveChart.SeriesCollection(73).Values = "=Datatable2!$AL$683:$AL$693"
ActiveChart.SeriesCollection(72).Name = "=Datatable2!$H$679:$K$679"
ActiveChart.SeriesCollection(72).XValues = "=Datatable2!$L$679:$L$682"
ActiveChart.SeriesCollection(72).Values = "=Datatable2!$AL$679:$AL$682"
ActiveChart.SeriesCollection(71).Name = "=Datatable2!$H$668:$K$668"
ActiveChart.SeriesCollection(71).XValues = "=Datatable2!$L$668:$L$678"
ActiveChart.SeriesCollection(71).Values = "=Datatable2!$AL$668:$AL$678"
ActiveChart.SeriesCollection(70).Name = "=Datatable2!$H$657:$K$657"
ActiveChart.SeriesCollection(70).XValues = "=Datatable2!$L$657:$L$667"
ActiveChart.SeriesCollection(70).Values = "=Datatable2!$AL$657:$AL$667"
ActiveChart.SeriesCollection(69).Name = "=Datatable2!$H$646:$K$646"
ActiveChart.SeriesCollection(69).XValues = "=Datatable2!$L$646:$L$656"
ActiveChart.SeriesCollection(69).Values = "=Datatable2!$AL$646:$AL$656"
ActiveChart.SeriesCollection(68).Name = "=Datatable2!$H$642:$K$642"
ActiveChart.SeriesCollection(68).XValues = "=Datatable2!$L$642:$L$645"
ActiveChart.SeriesCollection(68).Values = "=Datatable2!$AL$642:$AL$645"
ActiveChart.SeriesCollection(67).Name = "=Datatable2!$H$631:$K$631"
ActiveChart.SeriesCollection(67).XValues = "=Datatable2!$L$631:$L$641"
ActiveChart.SeriesCollection(67).Values = "=Datatable2!$AL$631:$AL$641"
ActiveChart.SeriesCollection(66).Name = "=Datatable2!$H$620:$K$620"
ActiveChart.SeriesCollection(66).XValues = "=Datatable2!$L$620:$L$630"
ActiveChart.SeriesCollection(66).Values = "=Datatable2!$AL$620:$AL$630"
ActiveChart.SeriesCollection(65).Name = "=Datatable2!$H$609:$K$609"
ActiveChart.SeriesCollection(65).XValues = "=Datatable2!$L$609:$L$619"
ActiveChart.SeriesCollection(65).Values = "=Datatable2!$AL$609:$AL$619"
ActiveChart.SeriesCollection(64).Name = "=Datatable2!$H$605:$K$605"
ActiveChart.SeriesCollection(64).XValues = "=Datatable2!$L$605:$L$608"
ActiveChart.SeriesCollection(64).Values = "=Datatable2!$AL$605:$AL$608"
ActiveChart.SeriesCollection(63).Name = "=Datatable2!$H$594:$K$594"
ActiveChart.SeriesCollection(63).XValues = "=Datatable2!$L$594:$L$604"
ActiveChart.SeriesCollection(63).Values = "=Datatable2!$AL$594:$AL$604"
ActiveChart.SeriesCollection(62).Name = "=Datatable2!$H$583:$K$583"
ActiveChart.SeriesCollection(62).XValues = "=Datatable2!$L$583:$L$593"
ActiveChart.SeriesCollection(62).Values = "=Datatable2!$AL$583:$AL$593"
ActiveChart.SeriesCollection(61).Name = "=Datatable2!$H$572:$K$572"
ActiveChart.SeriesCollection(61).XValues = "=Datatable2!$L$572:$L$582"
ActiveChart.SeriesCollection(61).Values = "=Datatable2!$AL$572:$AL$582"
ActiveChart.SeriesCollection(60).Name = "=Datatable2!$H$568:$K$568"
ActiveChart.SeriesCollection(60).XValues = "=Datatable2!$L$568:$L$571"
ActiveChart.SeriesCollection(60).Values = "=Datatable2!$AL$568:$AL$571"
ActiveChart.SeriesCollection(59).Name = "=Datatable2!$H$557:$K$557"
ActiveChart.SeriesCollection(59).XValues = "=Datatable2!$L$557:$L$567"
ActiveChart.SeriesCollection(59).Values = "=Datatable2!$AL$557:$AL$567"
ActiveChart.SeriesCollection(58).Name = "=Datatable2!$H$546:$K$546"
ActiveChart.SeriesCollection(58).XValues = "=Datatable2!$L$546:$L$556"
ActiveChart.SeriesCollection(58).Values = "=Datatable2!$AL$546:$AL$556"
ActiveChart.SeriesCollection(57).Name = "=Datatable2!$H$535:$K$535"
ActiveChart.SeriesCollection(57).XValues = "=Datatable2!$L$535:$L$545"
ActiveChart.SeriesCollection(57).Values = "=Datatable2!$AL$535:$AL$545"
ActiveChart.SeriesCollection(56).Name = "=Datatable2!$H$531:$K$531"
ActiveChart.SeriesCollection(56).XValues = "=Datatable2!$L$531:$L$534"
ActiveChart.SeriesCollection(56).Values = "=Datatable2!$AL$531:$AL$534"
ActiveChart.SeriesCollection(55).Name = "=Datatable2!$H$520:$K$520"
ActiveChart.SeriesCollection(55).XValues = "=Datatable2!$L$520:$L$530"
ActiveChart.SeriesCollection(55).Values = "=Datatable2!$AL$520:$AL$530"
ActiveChart.SeriesCollection(54).Name = "=Datatable2!$H$509:$K$509"
ActiveChart.SeriesCollection(54).XValues = "=Datatable2!$L$509:$L$519"
ActiveChart.SeriesCollection(54).Values = "=Datatable2!$AL$509:$AL$519"
ActiveChart.SeriesCollection(53).Name = "=Datatable2!$H$498:$K$498"
ActiveChart.SeriesCollection(53).XValues = "=Datatable2!$L$498:$L$508"
ActiveChart.SeriesCollection(53).Values = "=Datatable2!$AL$498:$AL$508"
ActiveChart.SeriesCollection(52).Name = "=Datatable2!$H$494:$K$494"
ActiveChart.SeriesCollection(52).XValues = "=Datatable2!$L$494:$L$497"
ActiveChart.SeriesCollection(52).Values = "=Datatable2!$AL$494:$AL$497"
ActiveChart.SeriesCollection(51).Name = "=Datatable2!$H$483:$K$483"
ActiveChart.SeriesCollection(51).XValues = "=Datatable2!$L$483:$L$493"
ActiveChart.SeriesCollection(51).Values = "=Datatable2!$AL$483:$AL$493"
ActiveChart.SeriesCollection(50).Name = "=Datatable2!$H$472:$K$472"
ActiveChart.SeriesCollection(50).XValues = "=Datatable2!$L$472:$L$482"
ActiveChart.SeriesCollection(50).Values = "=Datatable2!$AL$472:$AL$482"
ActiveChart.SeriesCollection(49).Name = "=Datatable2!$H$461:$K$461"
ActiveChart.SeriesCollection(49).XValues = "=Datatable2!$L$461:$L$471"
ActiveChart.SeriesCollection(49).Values = "=Datatable2!$AL$461:$AL$471"
ActiveChart.SeriesCollection(48).Name = "=Datatable2!$H$457:$K$457"
ActiveChart.SeriesCollection(48).XValues = "=Datatable2!$L$457:$L$460"
ActiveChart.SeriesCollection(48).Values = "=Datatable2!$AL$457:$AL$460"
ActiveChart.SeriesCollection(47).Name = "=Datatable2!$H$446:$K$446"
ActiveChart.SeriesCollection(47).XValues = "=Datatable2!$L$446:$L$456"
ActiveChart.SeriesCollection(47).Values = "=Datatable2!$AL$446:$AL$456"
ActiveChart.SeriesCollection(46).Name = "=Datatable2!$H$435:$K$435"
ActiveChart.SeriesCollection(46).XValues = "=Datatable2!$L$435:$L$445"
ActiveChart.SeriesCollection(46).Values = "=Datatable2!$AL$435:$AL$445"
ActiveChart.SeriesCollection(45).Name = "=Datatable2!$H$424:$K$424"
ActiveChart.SeriesCollection(45).XValues = "=Datatable2!$L$424:$L$434"
ActiveChart.SeriesCollection(45).Values = "=Datatable2!$AL$424:$AL$434"
ActiveChart.SeriesCollection(44).Name = "=Datatable2!$H$420:$K$420"
ActiveChart.SeriesCollection(44).XValues = "=Datatable2!$L$420:$L$423"
ActiveChart.SeriesCollection(44).Values = "=Datatable2!$AL$420:$AL$423"
ActiveChart.SeriesCollection(43).Name = "=Datatable2!$H$409:$K$409"
ActiveChart.SeriesCollection(43).XValues = "=Datatable2!$L$409:$L$419"
ActiveChart.SeriesCollection(43).Values = "=Datatable2!$AL$409:$AL$419"
ActiveChart.SeriesCollection(42).Name = "=Datatable2!$H$398:$K$398"
ActiveChart.SeriesCollection(42).XValues = "=Datatable2!$L$398:$L$408"
ActiveChart.SeriesCollection(42).Values = "=Datatable2!$AL$398:$AL$408"
ActiveChart.SeriesCollection(41).Name = "=Datatable2!$H$387:$K$387"
ActiveChart.SeriesCollection(41).XValues = "=Datatable2!$L$387:$L$397"
ActiveChart.SeriesCollection(41).Values = "=Datatable2!$AL$387:$AL$397"
ActiveChart.SeriesCollection(40).Name = "=Datatable2!$H$383:$K$383"
ActiveChart.SeriesCollection(40).XValues = "=Datatable2!$L$383:$L$386"
ActiveChart.SeriesCollection(40).Values = "=Datatable2!$AL$383:$AL$386"
ActiveChart.SeriesCollection(39).Name = "=Datatable2!$H$372:$K$372"
ActiveChart.SeriesCollection(39).XValues = "=Datatable2!$L$372:$L$382"
ActiveChart.SeriesCollection(39).Values = "=Datatable2!$AL$372:$AL$382"
ActiveChart.SeriesCollection(38).Name = "=Datatable2!$H$361:$K$361"
ActiveChart.SeriesCollection(38).XValues = "=Datatable2!$L$361:$L$371"
ActiveChart.SeriesCollection(38).Values = "=Datatable2!$AL$361:$AL$371"
ActiveChart.SeriesCollection(37).Name = "=Datatable2!$H$350:$K$350"
ActiveChart.SeriesCollection(37).XValues = "=Datatable2!$L$350:$L$360"
ActiveChart.SeriesCollection(37).Values = "=Datatable2!$AL$350:$AL$360"
ActiveChart.SeriesCollection(36).Name = "=Datatable2!$H$346:$K$346"
ActiveChart.SeriesCollection(36).XValues = "=Datatable2!$L$346:$L$349"
ActiveChart.SeriesCollection(36).Values = "=Datatable2!$AL$346:$AL$349"
ActiveChart.SeriesCollection(35).Name = "=Datatable2!$H$335:$K$335"
ActiveChart.SeriesCollection(35).XValues = "=Datatable2!$L$335:$L$345"
ActiveChart.SeriesCollection(35).Values = "=Datatable2!$AL$335:$AL$345"
ActiveChart.SeriesCollection(34).Name = "=Datatable2!$H$324:$K$324"
ActiveChart.SeriesCollection(34).XValues = "=Datatable2!$L$324:$L$334"
ActiveChart.SeriesCollection(34).Values = "=Datatable2!$AL$324:$AL$334"
ActiveChart.SeriesCollection(33).Name = "=Datatable2!$H$313:$K$313"
ActiveChart.SeriesCollection(33).XValues = "=Datatable2!$L$313:$L$323"
ActiveChart.SeriesCollection(33).Values = "=Datatable2!$AL$313:$AL$323"
ActiveChart.SeriesCollection(32).Name = "=Datatable2!$H$309:$K$309"
ActiveChart.SeriesCollection(32).XValues = "=Datatable2!$L$309:$L$312"
ActiveChart.SeriesCollection(32).Values = "=Datatable2!$AL$309:$AL$312"
ActiveChart.SeriesCollection(31).Name = "=Datatable2!$H$298:$K$298"
ActiveChart.SeriesCollection(31).XValues = "=Datatable2!$L$298:$L$308"
ActiveChart.SeriesCollection(31).Values = "=Datatable2!$AL$298:$AL$308"
ActiveChart.SeriesCollection(30).Name = "=Datatable2!$H$287:$K$287"
ActiveChart.SeriesCollection(30).XValues = "=Datatable2!$L$287:$L$297"
ActiveChart.SeriesCollection(30).Values = "=Datatable2!$AL$287:$AL$297"
ActiveChart.SeriesCollection(29).Name = "=Datatable2!$H$276:$K$276"
ActiveChart.SeriesCollection(29).XValues = "=Datatable2!$L$276:$L$286"
ActiveChart.SeriesCollection(29).Values = "=Datatable2!$AL$276:$AL$286"
ActiveChart.SeriesCollection(28).Name = "=Datatable2!$H$272:$K$272"
ActiveChart.SeriesCollection(28).XValues = "=Datatable2!$L$272:$L$275"
ActiveChart.SeriesCollection(28).Values = "=Datatable2!$AL$272:$AL$275"
ActiveChart.SeriesCollection(27).Name = "=Datatable2!$H$261:$K$261"
ActiveChart.SeriesCollection(27).XValues = "=Datatable2!$L$261:$L$271"
ActiveChart.SeriesCollection(27).Values = "=Datatable2!$AL$261:$AL$271"
ActiveChart.SeriesCollection(26).Name = "=Datatable2!$H$250:$K$250"
ActiveChart.SeriesCollection(26).XValues = "=Datatable2!$L$250:$L$260"
ActiveChart.SeriesCollection(26).Values = "=Datatable2!$AL$250:$AL$260"
ActiveChart.SeriesCollection(25).Name = "=Datatable2!$H$239:$K$239"
ActiveChart.SeriesCollection(25).XValues = "=Datatable2!$L$239:$L$249"
ActiveChart.SeriesCollection(25).Values = "=Datatable2!$AL$239:$AL$249"
ActiveChart.SeriesCollection(24).Name = "=Datatable2!$H$235:$K$235"
ActiveChart.SeriesCollection(24).XValues = "=Datatable2!$L$235:$L$238"
ActiveChart.SeriesCollection(24).Values = "=Datatable2!$AL$235:$AL$238"
ActiveChart.SeriesCollection(23).Name = "=Datatable2!$H$224:$K$224"
ActiveChart.SeriesCollection(23).XValues = "=Datatable2!$L$224:$L$234"
ActiveChart.SeriesCollection(23).Values = "=Datatable2!$AL$224:$AL$234"
ActiveChart.SeriesCollection(22).Name = "=Datatable2!$H$213:$K$213"
ActiveChart.SeriesCollection(22).XValues = "=Datatable2!$L$213:$L$223"
ActiveChart.SeriesCollection(22).Values = "=Datatable2!$AL$213:$AL$223"
ActiveChart.SeriesCollection(21).Name = "=Datatable2!$H$202:$K$202"
ActiveChart.SeriesCollection(21).XValues = "=Datatable2!$L$202:$L$212"
ActiveChart.SeriesCollection(21).Values = "=Datatable2!$AL$202:$AL$212"
ActiveChart.SeriesCollection(20).Name = "=Datatable2!$H$198:$K$198"
ActiveChart.SeriesCollection(20).XValues = "=Datatable2!$L$198:$L$201"
ActiveChart.SeriesCollection(20).Values = "=Datatable2!$AL$198:$AL$201"
ActiveChart.SeriesCollection(19).Name = "=Datatable2!$H$187:$K$187"
ActiveChart.SeriesCollection(19).XValues = "=Datatable2!$L$187:$L$197"
ActiveChart.SeriesCollection(19).Values = "=Datatable2!$AL$187:$AL$197"
ActiveChart.SeriesCollection(18).Name = "=Datatable2!$H$176:$K$176"
ActiveChart.SeriesCollection(18).XValues = "=Datatable2!$L$176:$L$186"
ActiveChart.SeriesCollection(18).Values = "=Datatable2!$AL$176:$AL$186"
ActiveChart.SeriesCollection(17).Name = "=Datatable2!$H$165:$K$165"
ActiveChart.SeriesCollection(17).XValues = "=Datatable2!$L$165:$L$175"
ActiveChart.SeriesCollection(17).Values = "=Datatable2!$AL$165:$AL$175"
ActiveChart.SeriesCollection(16).Name = "=Datatable2!$H$161:$K$161"
ActiveChart.SeriesCollection(16).XValues = "=Datatable2!$L$161:$L$164"
ActiveChart.SeriesCollection(16).Values = "=Datatable2!$AL$161:$AL$164"
ActiveChart.SeriesCollection(15).Name = "=Datatable2!$H$150:$K$150"
ActiveChart.SeriesCollection(15).XValues = "=Datatable2!$L$150:$L$160"
ActiveChart.SeriesCollection(15).Values = "=Datatable2!$AL$150:$AL$160"
ActiveChart.SeriesCollection(14).Name = "=Datatable2!$H$139:$K$139"
ActiveChart.SeriesCollection(14).XValues = "=Datatable2!$L$139:$L$149"
ActiveChart.SeriesCollection(14).Values = "=Datatable2!$AL$139:$AL$149"
ActiveChart.SeriesCollection(13).Name = "=Datatable2!$H$128:$K$128"
ActiveChart.SeriesCollection(13).XValues = "=Datatable2!$L$128:$L$138"
ActiveChart.SeriesCollection(13).Values = "=Datatable2!$AL$128:$AL$138"
ActiveChart.SeriesCollection(12).Name = "=Datatable2!$H$124:$K$124"
ActiveChart.SeriesCollection(12).XValues = "=Datatable2!$L$124:$L$127"
ActiveChart.SeriesCollection(12).Values = "=Datatable2!$AL$124:$AL$127"
ActiveChart.SeriesCollection(11).Name = "=Datatable2!$H$113:$K$113"
ActiveChart.SeriesCollection(11).XValues = "=Datatable2!$L$113:$L$123"
ActiveChart.SeriesCollection(11).Values = "=Datatable2!$AL$113:$AL$123"
ActiveChart.SeriesCollection(10).Name = "=Datatable2!$H$102:$K$102"
ActiveChart.SeriesCollection(10).XValues = "=Datatable2!$L$102:$L$112"
ActiveChart.SeriesCollection(10).Values = "=Datatable2!$AL$102:$AL$112"
ActiveChart.SeriesCollection(9).Name = "=Datatable2!$H$91:$K$91"
ActiveChart.SeriesCollection(9).XValues = "=Datatable2!$L$91:$L$101"
ActiveChart.SeriesCollection(9).Values = "=Datatable2!$AL$91:$AL$101"
ActiveChart.SeriesCollection(8).Name = "=Datatable2!$H$87:$K$87"
ActiveChart.SeriesCollection(8).XValues = "=Datatable2!$L$87:$L$90"
ActiveChart.SeriesCollection(8).Values = "=Datatable2!$AL$87:$AL$90"
ActiveChart.SeriesCollection(7).Name = "=Datatable2!$H$76:$K$76"
ActiveChart.SeriesCollection(7).XValues = "=Datatable2!$L$76:$L$86"
ActiveChart.SeriesCollection(7).Values = "=Datatable2!$AL$76:$AL$86"
ActiveChart.SeriesCollection(6).Name = "=Datatable2!$H$65:$K$65"
ActiveChart.SeriesCollection(6).XValues = "=Datatable2!$L$65:$L$75"
ActiveChart.SeriesCollection(6).Values = "=Datatable2!$AL$65:$AL$75"
ActiveChart.SeriesCollection(5).Name = "=Datatable2!$H$54:$K$54"
ActiveChart.SeriesCollection(5).XValues = "=Datatable2!$L$54:$L$64"
ActiveChart.SeriesCollection(5).Values = "=Datatable2!$AL$54:$AL$64"
ActiveChart.SeriesCollection(4).Name = "=Datatable2!$H$50:$K$50"
ActiveChart.SeriesCollection(4).XValues = "=Datatable2!$L$50:$L$53"
ActiveChart.SeriesCollection(4).Values = "=Datatable2!$AL$50:$AL$53"
ActiveChart.SeriesCollection(3).Name = "=Datatable2!$H$39:$K$39"
ActiveChart.SeriesCollection(3).XValues = "=Datatable2!$L$39:$L$49"
ActiveChart.SeriesCollection(3).Values = "=Datatable2!$AL$39:$AL$49"
ActiveChart.SeriesCollection(2).Name = "=Datatable2!$H$28:$K$28"
ActiveChart.SeriesCollection(2).XValues = "=Datatable2!$L$28:$L$38"
ActiveChart.SeriesCollection(2).Values = "=Datatable2!$AL$28:$AL$38"
ActiveChart.SeriesCollection(1).Name = "=Datatable2!$H$17:$K$17"
ActiveChart.SeriesCollection(1).XValues = "=Datatable2!$L$17:$L$27"
ActiveChart.SeriesCollection(1).Values = "=Datatable2!$AL$17:$AL$27"
ActiveChart.ChartTitle.Select
Selection.Caption = "=Datatable2!AK6"
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Caption = "=Datatable2!AK2"
ActiveWorkbook.Save
End Sub

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: in Diagrammen suchen --> Ersetzen
22.03.2016 07:57:16
Beverly
Hi,
und um welche Spalten handelt es sich - also welche Systematik gibt es dabei?

AW: in Diagrammen suchen --> Ersetzen
22.03.2016 09:17:12
Steve
Hallo Martina,
unter der Annahme dass alle Diagramme in einem Tabellenblatt liegen könnte das etwa so ausschauen: 
Sub DiagrammeAendern()
Dim wks As Worksheet
Dim oChart As ChartObject, oSC As Object
Dim i As Long, Row As Long, Col As Long
Set wks = ThisWorkbook.Sheets("Datatable2")
If wks.ChartObjects.Count > 0 Then
For Each oChart In wks.ChartObjects
i = 0
For Each oSC In oChart.Chart.SeriesCollection
Row = 17 + i * 11
Col = 8 + i * 0 '
Die Formeln zu Verschiebung der Spalten werden auf alle Fälle nicht stimmen, solang du uns nicht deine Systematik näher beschreibst. Die Zeilen jedoch sollten hinhauen.
lg Steve

Anzeige
AW: in Diagrammen suchen --> Ersetzen
22.03.2016 19:34:37
trine
Hallo ihr zwei,
vielen Dank für Eure Mühe und Antworten.
Hier die weitere Spezifikation:
Die Daten sollen aus dem Tabellenblatt "Datatable2" entnommen werden, die Diagramme befinden sich im Blatt "Diagramme".
Die erste Datenreihe des ersten Diagramms beginnt in Zeile 17 und umfasst 11 Zeilen.
Die x-Werte befinden sich in Spalte L. Zeilen 17-27
Die y-Werte in Spalte V. Zeile 17 -27
Der Reihenname immer in der ersten Zeile des Diagrammes. Für Datenreihe 1 :in Zeile 17 in den Spalten H bis K
Die zweite Datenreihe des ersten Diagramms beginnt in Zeile 28 und umfasst 11 Zeilen
Die dritte Datenreihen des ersten Diagramms beginnt in Zeile 39 und umfasst 11 Zeilen.
Die vierte Datenreihe des ersten Diagramms beginnt in Zeile 50 und umfasst 4 Zeilen
Danach wiederholt sich dieses Muster (drei Datenreihen mit 11 Zeile und dann eine Datenreihe mit 4 Zeilen) bis Datenreihe 120 des ersten Diagramms.
Im Diagramm 2 bleiben die Spalten für Reihenname und x-Werten jeder Datenreihe gleich.
Die Spalte für die y-Werte wird von spalte V auf spalte AD geändert (also um 8 Spalten verschoben, gilf für alle weiteren Diagramme)
Viele Grüße
Martina

Anzeige
AW: in Diagrammen suchen --> Ersetzen
22.03.2016 20:44:17
Beverly
Hi Martina,
wenn ich dich richtig verstanden habe, müssen in deinen Diagrammkopien doch nur die Spalten für die Y-Werte angepasst werden, da die X-Werte und Namen der Reihen sich nicht ändern. Dann versuche es mal mit folgendem Makro:
Sub DiasKopieren()
Dim chrDia As ChartObject
Dim serReihe As Series
Dim strYWerte As String
Dim intZaehler As Integer
Dim intSpalte As Integer
intSpalte = 8
With Worksheets("Diagramme")
Set chrDia = .ChartObjects(1)
For intZaehler = 1 To 29
chrDia.Copy
Worksheets("Diagramme").Paste
.ChartObjects(.ChartObjects.Count).Top = _
.ChartObjects(.ChartObjects.Count - 1).Top + _
.ChartObjects(.ChartObjects.Count - 1).Height
With .ChartObjects(.ChartObjects.Count).Chart
For Each serReihe In .SeriesCollection
strYWerte = Split(serReihe.Formula, ",")(2)
serReihe.Values = Range(strYWerte).Offset(0, intSpalte)
intSpalte = intSpalte + 8
Next serReihe
End With
Next intZaehler
End With
End Sub

Bedingung ist, dass bereits 1 Diagramm vorhanden ist. Dieses wird als Basis genommen und 29mal kopiert (also letztendlich sind 30 Diagramme vorhanden). Die Zellbezüge für die Y-Werte werden dabei jeweils um 8 Spalten versetzt.

Anzeige
AW: in Diagrammen suchen --> Ersetzen
22.03.2016 22:08:42
trine
Hallo Karin,
das hast Du richtig verstanden. Das erste Diagramm existiert bereits, so dass ich das Makro verwenden könnte. Ich habe es direkt ausprobiert. Leider läuft es nicht durch, sondern nur bis zur Zeile
serReihe.Values = Range(strYWerte).Offset(0, intSpalte)
Viele Grüße
Martina

AW: in Diagrammen suchen --> Ersetzen
22.03.2016 22:48:14
Beverly
Hi Martina,
und was sagt der Debugger genau? Außerdem: lade doch mal deine Mappe mit dem 1. Diagramm hoch, damit ich es am Original testen kann.

AW: in Diagrammen suchen --> Ersetzen
23.03.2016 17:13:53
trine
Hallo Karin, anbei die Testdatei als Beipiel.
https://www.herber.de/bbs/user/104572.xlsm
Viele Grüße
Martina

Anzeige
AW: in Diagrammen suchen --> Ersetzen
23.03.2016 17:52:37
Beverly
Hi Martina,
in deinem Diagramm gibt es die Datenreihen UL, MW, OL - alle 3 haben einen Bezugsfehler in der Bereichszuweisung und lösen folglich einen Fehler aus, da sie keine Datenreihenformel haben, auf die der Code Bezug nimmt um den Y-Wertebereich festzulegen. Ich weiß nun nicht, ob für diese auch gilt, dass der Y-Wertebereich versetzt wird - ich bin mal davon ausgegangen, dass dies nicht der Fall ist. Im folgenden Code werden sie nicht berücksichtigt:
        For intZaehler = 1 To 29
chrDia.Copy
Worksheets("Diagramme").Paste
.ChartObjects(.ChartObjects.Count).Top = _
.ChartObjects(.ChartObjects.Count - 1).Top + _
.ChartObjects(.ChartObjects.Count - 1).Height
With .ChartObjects(.ChartObjects.Count).Chart
For Each serReihe In .SeriesCollection
' UL, MW und OL nicht berücksichtigen
If serReihe.Name  "UL" And serReihe.Name  "MW" And _
serReihe.Name  "OL" Then
strYWerte = Split(serReihe.Formula, ",")(2)
serReihe.Values = Range(strYWerte).Offset(0, intSpalte)
End If
Next serReihe
End With
intSpalte = intSpalte + 8
Next intZaehler

Falls sie berückichtigt werden sollen, musst du die Bezüge im Diagramm korrigieren (was ich dir sowieso empfehlen würde, denn andernfalls kannst du sie auch löschen da sie nichts anzeigen) - dann wird der Fehler nicht mehr ausgelöst. Die If-Verzweigung im Code kannst du dann wieder löschen.
An den Anfang des Codes (vor der äußeren Schleife) kannst du noch die Bildschirmaktualisierung aus- und am Ende wieder einschalten, dann ist das Flackern nicht zu sehen. Beahcte aber, dass der Code einige Zeit benötigt, bis er zum Ende durchgelaufen ist.
Außerdem war der Code nicht ganz korrekt (hat aber nicht mit dem aufgetretenen Fehler zu tun) - das Hochzählen des Spaltenzählers darf selstverständlich nicht in der inneren Scheife erfolgen sondern muss erst beim nächsten Diagramm (also in der äußeren Schleife) geändert werden - habe ich jetzt berücksichtigt.

Anzeige
AW: in Diagrammen suchen --> Ersetzen
23.03.2016 22:05:58
trine
Hallo Karin,
es funktioniert!
Super vielen Lieben Dank.
Martina

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige