Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 08:41:23
Zwei Makros in ein Arbeitsblatt - zweite wird nicht ausführt
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1184to1188
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
1184to1188
1184to1188
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Schleife zur Diagrammerstellung

Schleife zur Diagrammerstellung
Benni
Hallo zusammen,
ich würde zur Vereinfachung gerne die vorliegenden Sheets in einer Arbeitsmappe jeweils in einem neuen Diagramm darstellen. Den Code von einem Diagramm findet Ihr weiter unten. Kann mir jemand bei einer Art Schleife weiterhelfen, mit der ich die vielen Sheets in jeweils einem Diagramm erstellen kann?
Vielen Dank!

Sub Diagramm()
Sheets("V3_0_1").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets("V3_0_1").Range("H12")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=V3_0_1!R6C2:R3005C2"
ActiveChart.SeriesCollection(1).Values = "=V3_0_1!R6C3:R3005C3"
ActiveChart.SeriesCollection(1).Name = "=V3_0_1!R1C1"
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="DiagrammV3_0_1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "V3_0_1"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Weg [mm]"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Kraft [KN]"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 9
.MinorUnitIsAuto = True
.MajorUnit = 1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 2
.MinorUnitIsAuto = True
.MajorUnit = 0.2
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Axes(xlCategory).MajorGridlines.Select
With Selection.Border
.ColorIndex = 15
.Weight = xlHairline
.LineStyle = xlContinuous
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 15
.Weight = xlHairline
.LineStyle = xlContinuous
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 5
.MinorUnitIsAuto = True
.MajorUnit = 1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 2
.MinorUnitIsAuto = True
.MajorUnit = 0.1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Schleife zur Diagrammerstellung
02.11.2010 15:07:36
Beverly
Hi Benni,
ungetestet: 
Sub Diagramm()
Dim wshTabelle As Worksheet
For Each wshTabelle In Worksheets
With Charts.Add
.ChartType = xlXYScatterLinesNoMarkers
.SetSourceData Source:=wshTabelle.Range("B6:C3005")
.SeriesCollection(1).Name = wshTabelle.Range("A1")
.Name = "Diagramm" & wshTabelle.Name
.HasTitle = True
.ChartTitle.Characters.Text = wshTabelle.Name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Weg [mm]"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Kraft [KN]"
.Axes(xlCategory).HasMajorGridlines = True
.HasLegend = False
With .PlotArea.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With .PlotArea.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With .Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 9
.MajorUnit = 1
.DisplayUnit = xlNone
End With
With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 2
.MajorUnit = 0.2
.DisplayUnit = xlNone
End With
With .Axes(xlCategory).MajorGridlines.Border
.ColorIndex = 15
.Weight = xlHairline
.LineStyle = xlContinuous
End With
With .Axes(xlValue).MajorGridlines.Border
.ColorIndex = 15
.Weight = xlHairline
.LineStyle = xlContinuous
End With
With .Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 5
.MajorUnit = 1
.DisplayUnit = xlNone
End With
With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 2
.MajorUnit = 0.1
.DisplayUnit = xlNone
End With
End With
Next wshTabelle
End Sub

Einige Angaben bei der Formatierung können weggelassen werden, weil es Standardwerte sind.

Anzeige
AW: Schleife zur Diagrammerstellung
02.11.2010 15:24:43
Benni
Hallo Beverly,
vielen Dank, hat funktioniert.
Wie würde das ganze Aussehen wenn ich alle Sheets in ein Diagramm darstellen möchte.
Vielen Dank
Benni!
AW: Schleife zur Diagrammerstellung
02.11.2010 16:22:23
Beverly
Hi Benni, 
Sub Diagramm2()
Dim wshTabelle As Worksheet
With Charts.Add
.ChartType = xlXYScatterLinesNoMarkers
.SetSourceData Source:=Worksheets("V3_0_1").Range("B6:C3005")
.SeriesCollection(1).Name = Worksheets("V3_0_1").Range("A1")
.Name = "DiagrammV3_0_1"
.HasTitle = True
.ChartTitle.Characters.Text = "V3_0_1"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Weg [mm]"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Kraft [KN]"
.Axes(xlCategory).HasMajorGridlines = True
.HasLegend = False
With .PlotArea.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With .PlotArea.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With .Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 9
.MajorUnit = 1
.DisplayUnit = xlNone
End With
With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 2
.MajorUnit = 0.2
.DisplayUnit = xlNone
End With
With .Axes(xlCategory).MajorGridlines.Border
.ColorIndex = 15
.Weight = xlHairline
.LineStyle = xlContinuous
End With
With .Axes(xlValue).MajorGridlines.Border
.ColorIndex = 15
.Weight = xlHairline
.LineStyle = xlContinuous
End With
With .Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 5
.MajorUnit = 1
.DisplayUnit = xlNone
End With
With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 2
.MajorUnit = 0.1
.DisplayUnit = xlNone
End With
For Each wshTabelle In Worksheets
If wshTabelle.Name  "V3_0_1" Then
.SeriesCollection.NewSeries
With .SeriesCollection(.SeriesCollection.Count)
.Name = wshTabelle.Range("A1")
.XValues = wshTabelle.Range("B6:B3005")
.Values = wshTabelle.Range("C6:C3005")
End With
End If
Next wshTabelle
End With
End Sub


Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige