Relativer zellbezug in range eines charts

Baierl · 10. November 2019 um 11:58

Hallo!

Ich habe eine Tabelle mit Daten, die ich einzeln plotten möchte, also z.b Chart1 mit Daten aus Spalte 1 (x-values) und Spalte 2 (values), Chart 2 aus Spalte 1 (x-values) und Spalte 12, Chart3 mit Daten aus Spalte 1 (x-values) und Spalte 22 usw.

Theoretisch könnte man ja eine Schleife laufen lassen, damit aus jeder zehnten Spalte ein Chart erstellt wird, der Haken ist jetzt nur, dass visual basic partout eine absolute Range haben will, die ich ihm aber wegen der schleife ja nicht geben kann.

Ich habe schon alles mögliche versucht, nichts hat funktioniert. Jemand einen Tip? Eigentlich müsste es doch ganz einfach sein, wahrscheinlich finde ich nur nicht die richtige Formulierung.

Range(„K501“).Select

For i = 1 To 2

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets(„tabelle1“).Range(„K501“)
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = „=tabelle1!R3C1:R496C1“
ActiveChart.SeriesCollection(1).Values = „=tabelle1!R3C11:R496C11“
ActiveChart.SeriesCollection(1).Name = „=“„Chart1"“"
ActiveChart.Location Where:=xlLocationAsNewSheet
Sheets(„tabelle1“).Select
ActiveCell.Offset(0, 10).Range(„A1“).Select

Next i

In der 5.zeile sollte quasi statt Range(„K501“) Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column))oder etwas derartiges stehen, aber das produziert immer wiecder Fehlermeldungen.

Bin für jede Hilfe sehr dankbar! (Sind am Ende natürlich mehr als nur 2 Durchgänge, nämlich 20)

Hoffnungsvolle Grüße an alle Spezialisten da draußen

Reinhard_fb8cb7 · 10. November 2019 um 11:59

Ich habe eine Tabelle mit Daten, die ich einzeln plotten
möchte, also z.b Chart1 mit Daten aus Spalte 1 (x-values) und
Spalte 2 (values), Chart 2 aus Spalte 1 (x-values) und Spalte
12, Chart3 mit Daten aus Spalte 1 (x-values) und Spalte 22
usw.
Range(„K501“).Select
For i = 1 To 2
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData
Source:=Sheets(„tabelle1“).Range(„K501“)
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues =
„=tabelle1!R3C1:R496C1“
ActiveChart.SeriesCollection(1).Values =
„=tabelle1!R3C11:R496C11“
ActiveChart.SeriesCollection(1).Name = „=“„Chart1"“"
ActiveChart.Location Where:=xlLocationAsNewSheet
Sheets(„tabelle1“).Select
ActiveCell.Offset(0, 10).Range(„A1“).Select
Next i

In der 5.zeile sollte quasi statt Range(„K501“)
Range(ActiveCell, Cells(ActiveCell.Row,
ActiveCell.Column))oder etwas derartiges stehen, aber das
produziert immer wiecder Fehlermeldungen.

Hi Baierle,

was solll das .Range(„A1“) bringen, wenn du es wegläßt hast du die gleiche Zelle markiert!?

Bei …Values= kommt bei mir Fehler 1004.
Bei dir nicht?

Beim Code posten benutze bitte den pre-Tag, wird unterhalb des Eingabefensters erläutert.

Gut wäre auch eine komplette lauffähige Sub incl. Dim usw.

Gruß
Reinhard

Baierl · 10. November 2019 um 11:59

Hallo Reinhard, danke für Deine Antwort!

ja, klar das Range stammte noch aus einer anderen Zeile, wo noch A1 bis C1 markiert waren. Hätts auch gleich löschen können. Bin noch ein blutiger Anfänger der viel rumkopiert und probiert

Also, bei mir funktioniert es so wie es unten steht, da ist jetzt der ganze Formatierungsschotter und mehrere Reihen dabei. Nur dass er eben für beide Durchgänge das gleiche Diagramm produziert.

Mit Dim arbeite ich bisher nicht, das hab ich mehr oder weniger nur aus dem Rekorder variiert.

Nur ich seh schon, ohne wirds wohl nicht gehen?

Grüße
Baierle

Sub plot()

 Range("K501").Select
a = 90
For i = 1 To 2

 Charts.Add
 ActiveChart.ChartType = xlXYScatterLinesNoMarkers
 ActiveChart.SetSourceData Source:=Sheets("tabelle1").Range("K501")
 ActiveChart.SeriesCollection.NewSeries
 ActiveChart.SeriesCollection.NewSeries
 ActiveChart.SeriesCollection.NewSeries
 ActiveChart.SeriesCollection(1).XValues = "=tabelle1!R3C1:R496C1"
 ActiveChart.SeriesCollection(1).Values = "=tabelle1!R3C11:R496C11"
 ActiveChart.SeriesCollection(1).Name = "=""mittel15"""
 ActiveChart.SeriesCollection(2).XValues = "=tabelle1!R3C1:R496C1"
 ActiveChart.SeriesCollection(2).Values = "=tabelle1!R3C12:R496C12"
 ActiveChart.SeriesCollection(2).Name = "=""mittel25"""
 ActiveChart.SeriesCollection(3).XValues = "=tabelle1!R3C1:R496C1"
 ActiveChart.SeriesCollection(3).Values = "=tabelle1!R3C13:R496C13"
 ActiveChart.SeriesCollection(3).Name = "=""mittel35"""
 ActiveChart.Location Where:=xlLocationAsNewSheet
 With ActiveChart
 .HasTitle = True
 .ChartTitle.Characters.Text = "minus " & a & "° (außen)"
 .Axes(xlCategory, xlPrimary).HasTitle = True
 .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "f [Hz]"
 .Axes(xlValue, xlPrimary).HasTitle = True
 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "dB"
 End With
 With ActiveChart
 .HasAxis(xlCategory, xlPrimary) = True
 .HasAxis(xlValue, xlPrimary) = True
 End With
 ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
 ActiveChart.Axes(xlCategory).Select
 With ActiveChart.Axes(xlCategory)
 .MinimumScaleIsAuto = True
 .MaximumScale = 95000
 .MinorUnitIsAuto = True
 .MajorUnitIsAuto = True
 .Crosses = xlAutomatic
 .ReversePlotOrder = False
 .ScaleType = xlLinear
 .DisplayUnit = xlNone
 End With
 With ActiveChart.Axes(xlCategory)
 .MinimumScale = 0
 .MaximumScale = 95000
 .MinorUnitIsAuto = True
 .MajorUnitIsAuto = True
 .Crosses = xlAutomatic
 .ReversePlotOrder = False
 .ScaleType = xlLinear
 .DisplayUnit = xlNone
 End With
 With ActiveChart.Axes(xlValue)
 .MinimumScale = -25
 .MaximumScale = 15
 .MinorUnitIsAuto = True
 .MajorUnitIsAuto = True
 .Crosses = xlAutomatic
 .ReversePlotOrder = False
 .ScaleType = xlLinear
 .DisplayUnit = xlNone
 End With
 Sheets("tabelle1").Select
 ActiveCell.Offset(0, 12).Select
 a = a - 10
Next i
a = 0
End Sub

.

Hi Baierle,

was solll das .Range(„A1“) bringen, wenn du es wegläßt hast du
die gleiche Zelle markiert!?

Bei …Values= kommt bei mir Fehler 1004.
Bei dir nicht?

Beim Code posten benutze bitte den pre-Tag, wird unterhalb des
Eingabefensters erläutert.

Gut wäre auch eine komplette lauffähige Sub incl. Dim usw.

Gruß
Reinhard

Reinhard_fb8cb7 · 10. November 2019 um 12:01

Hallo Baierl,

funktioniert das auch bei dir wenn du eine neue leere Mappe öffnest und den Code ausführst? Ich habe XL2000, und da hängt sich der Debugger immer noch mit 1004 an der „values“-Zeile auf.

Genauer gesagt, immer am zweiten Befehl, also bei der Reihenfolge

ActiveChart.SeriesCollection(1).XValues=
ActiveChart.SeriesCollection(1).Values=

wird „values“ moniert

Bei

ActiveChart.SeriesCollection(1).Values=
ActiveChart.SeriesCollection(1).XValues=

wird „Xvalues“ moniert.

Dies geschieht auch bei

ActiveChart.SeriesCollection(1).Values=
ActiveChart.SeriesCollection(1).Values=
ActiveChart.SeriesCollection(1).Values=

dann wird auch das zweite „values“ moniert.

So wie sich mir das also darstellt ist beim ersten Aufruf eine andere Ausgangskonstellation als beim zweiten Aufruf, aber was weiß ich nicht.
Was anderes selektiert oder so, aber alles mit Chartarea.selct, Plotarea.select brachte mich nicht weiter.

Und wenn ich das auskommentiere, .Names=„xyz“ oder .Name="="„xyz“"" wird nie genommen, immeer Fehler.

Ich weiß da echt nicht weiter. Ich habe gegoogelt, dein Code scheint sehr okay zu sein und überall zu laufen denn ich fand sehr viele nahezu identische Codes wo es scheinbar klappt, naja, außer bei mir

Deshalb ist mein problem gar nicht so deine eigentliche Anfrage, sondern wo seh ich den Wald vor lauter Bäumen nicht sodaß ich es nicht hinkriege einem Diagramm einen Wertbereich zuzuweisen per Vba.

Weil ich weiß sehr genau ich habe das schonmehrmals gemacht, also geht es *100pro*

Der makrorekorder war in diesem fall wenig hilfreich, ich habe das Diagramm manuell erstellt und die Erstellung aufzeichnen lassen.

Starte ich dann das grad eben aufgezeichnete Makro, schwer zu raten was dann passiert *sufz* es kommt wieder meinen geliebten 1004-Fehler an der gleichen Stelle *schwerseufz*

Welche XL-Version hast du? Obwohl, blödsinnige Frage, daran liegts bestimmt nicht, aber man weiß ja nie…

Gruß
Reinhard

Baierl · 10. November 2019 um 12:02

Hallo Reinhard!

Oh, interessant, wenn ich eine neue Mappe öffne und das Makro laufen lasse, kommt der gleiche Fehler an gleicher Stelle. Aber wie gesagt, dieser Teil ist 1:1 vom Rekorder erstellt. Und vor allem keine Ahnung, warum das in der neuen mappe nicht klappt, im Original aber wohl.

Meine Version ist übrigens XL2003.

Habe mittlerweile in den sauren Apfel gebissen und das sub einfach 20mal hintereinander kopiert, die Spalte in der Range dann jeweils per Hand geändert. Läuft einwandfrei. Jedenfalls im Original. In einer neuen Mappe mit irgenwelchen Spaßdaten kommt natürlich 1004. Das ist wohl ein bug, der sich nicht debuggen lässt?

Trotzdem noch interessehalber: kennst Du denn einen Weg, um hier

ActiveChart.SetSourceData Source:=Sheets("tabelle2").Range("K501")

 ActiveChart.SeriesCollection.NewSeries
 ActiveChart.SeriesCollection(1).XValues = "=tabelle2!R3C1:R496C1"
 ActiveChart.SeriesCollection(1).Values = "=tabelle2!R3C11:R496C11"

statt der K501 und R3C1:R496C1 etc. relative Angaben einzubauen?

Viele Grüße
Baierl

Reinhard_fb8cb7 · 10. November 2019 um 12:03

Habe mittlerweile in den sauren Apfel gebissen und das sub
einfach 20mal hintereinander kopiert, die Spalte in der Range
dann jeweils per Hand geändert. Läuft einwandfrei. Jedenfalls
im Original. In einer neuen Mappe mit irgenwelchen Spaßdaten
kommt natürlich 1004. Das ist wohl ein bug, der sich nicht
debuggen lässt?

Hi Baierl,

zum Testen nehme ich immer gut erreichbare kleine Bereiche, also A1:W5 in dem Beispiel im nachfolgenden Code, schreibe in jede Spalte zumindest eine Zahl rein, dann ist der Fehler 1004 weg.

Warum ist die With .Axes(xlCategory) - Schleife eigentlich doppelt drin? Habe das eben erst gemerkt, weil mich der mistige 1004 so lange aufhielt.

Im Code gabe ich ja dynamische Bereiche benutzt.

Gruß
Reinhard

Option Explicit
'
Sub plot()
Dim wks As Worksheet, Ch As Chart, a As Integer, i As Integer
Const Anz As Integer = 20
Set wks = Worksheets("Tabelle1")
wks.Activate
a = 90
Call Loesch
For i = 1 To Anz
 Set Ch = Charts.Add
 With Ch
 .ChartType = xlXYScatterLinesNoMarkers
 '.SetSourceData Source:=wks.Range("A1:W5")
 .SetSourceData Source:=wks.Range(wks.Cells(1, 1), wks.Cells(5, Anz + 3))
 .SeriesCollection.NewSeries
 .SeriesCollection(1).XValues = wks.Range("A1:A5")
 .SeriesCollection(1).Values = wks.Range(wks.Cells(1, i + 3), wks.Cells(5, i + 3))
 .SeriesCollection(1).Name = "mittel15"
 .Location Where:=xlLocationAsNewSheet
 .HasTitle = True
 .ChartTitle.Characters.Text = "minus " & a & "° (außen)"
 .Axes(xlCategory, xlPrimary).HasTitle = True
 .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "f [Hz]"
 .Axes(xlValue, xlPrimary).HasTitle = True
 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "dB"
 .HasAxis(xlCategory, xlPrimary) = True
 .HasAxis(xlValue, xlPrimary) = True
 .Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
 .Axes(xlCategory).Select
 With .Axes(xlCategory)
 .MinimumScaleIsAuto = True
 .MaximumScale = 95000
 .MinorUnitIsAuto = True
 .MajorUnitIsAuto = True
 .Crosses = xlAutomatic
 .ReversePlotOrder = False
 .ScaleType = xlLinear
 .DisplayUnit = xlNone
 End With
 With .Axes(xlCategory)
 .MinimumScale = 0
 .MaximumScale = 95000
 .MinorUnitIsAuto = True
 .MajorUnitIsAuto = True
 .Crosses = xlAutomatic
 .ReversePlotOrder = False
 .ScaleType = xlLinear
 .DisplayUnit = xlNone
 End With
 With .Axes(xlValue)
 .MinimumScale = -25
 .MaximumScale = 15
 .MinorUnitIsAuto = True
 .MajorUnitIsAuto = True
 .Crosses = xlAutomatic
 .ReversePlotOrder = False
 .ScaleType = xlLinear
 .DisplayUnit = xlNone
 End With
 a = a - 4
 End With
Next i
End Sub
'
Sub Loesch()
Application.DisplayAlerts = False
Dim i
For i = Sheets.Count To 1 Step -1
 If Sheets(i).Name Like "Diagr\*" Then Sheets(i).Delete
Next i
Application.DisplayAlerts = True
End Sub

Baierl · 10. November 2019 um 12:03

Hi!

Ja, mittlerweile funktioniert es bei mir auch nicht mehr *grummel* Wollte Deine trelativen Zellbezüge ausprobieren und sofort kam der Fehler 1004.
Aber hatte ja vorher gottseidank alle Diagramme schonmalgemacht, also ist mein Problem zwar nicht gelöst aber kann gut bis nächste Woche warten

Danke für Deine Hilfe!
Grüße Baierl

Reinhard_fb8cb7 · 10. November 2019 um 12:03

Ja, mittlerweile funktioniert es bei mir auch nicht mehr
*grummel* Wollte Deine trelativen Zellbezüge ausprobieren und
sofort kam der Fehler 1004.
Aber hatte ja vorher gottseidank alle Diagramme
schonmalgemacht, also ist mein Problem zwar nicht gelöst aber
kann gut bis nächste Woche warten

Hi Baierl,

sorry, wenn ich das unklar formuliert habe, so wie mein Code da steht hat er problemlos funtioniert.

Gruß
Reinhard