Microsoft Excel

Herbers Excel/VBA-Archiv

For-Next-Schleife + Sheetname

Betrifft: For-Next-Schleife + Sheetname von: Piotr
Geschrieben am: 14.09.2004 10:43:16

Hallo Zusammen,

ich habe ein Problem. Ich möchte gerne in einer Schleife den Sheetnamen übernehmen. Der Sheetname soll in der Schleife erkannt werden.

Bei maueller Eingabe des Sheetnamen (N10) gibt es keine Probleme. Die Zeile sieht dann so aus:
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='N10'!R3C3:R4C3"

In einer Schleife, die so aussieht,

For i = 10 To sheets.count
ActiveChart.SeriesCollection(i).XValues = "='"N"&i&'"!R3C3:R4C3
Next i

bekomme ich andauern die Fehlermeldung: Objektvariable oder With-Blockvariable nicht festgelegt (Fehler 91)!

In meinem Makro sind vorher sind keine Variablen definiert. Liegt es daran und wenn ja was müsste ich vorher eingeben oder habe ich einen Syntaxfehler?

Kurz zur Vorgeschichte: Mit dem Diagramm-Assistenten habe ich ein XY-Diagramm erzeugt. Durch "Hinzuführen kann man diesem Diagramm weitere Geraden einfügen. Da ich sehr viele solche Diagramme erstellen muß, wollte ich es mit einem Makro vereinfachen. Leider wird in der Schleife der Sheetname nicht übernommen.

Das zuletzt formulierte Makro sieht so aus:


Sub Diagramme_erstellen()
'
' Makro2 Makro
' Makro am 10.09.2004 von Piotr aufgezeichnet
'
'
'
    Sheets("N10").Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmooth
    ActiveChart.SetSourceData Source:=Sheets("N80").Range("D21"), PlotBy:= _
        xlRows

    ActiveChart.SeriesCollection(1).XValues = "='N10'!R3C3:R4C3"
    ActiveChart.SeriesCollection(1).Values = "='N10'!R3C4:R4C4"
    ActiveChart.SeriesCollection(1).Name = "='N10'!R1C2:R1C4"
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).XValues = "='N11'!R3C3:R4C3"
    ActiveChart.SeriesCollection(2).Values = "='N11'!R3C4:R4C4"
    ActiveChart.SeriesCollection(2).Name = "='N11'!R1C2:R1C4"
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).XValues = "='N12'!R3C3:R4C3"
    ActiveChart.SeriesCollection(3).Values = "='N12'!R3C4:R4C4"
    ActiveChart.SeriesCollection(3).Name = "='N12'!R1C2:R1C4" 'Bis hierhin gibt es keine Probleme. Es wird ein Diagramm erstellt
    

    For i = 10 To 20
 
        ActiveChart.SeriesCollection.NewSeries 'Ab hier wird eine Diagramm hinzugefügt (Reihe 4)
        Sheets("tabelle1").Select     'Dies habe ich nur eingeführt, um zu schauen, ob der Sheetname erkannt wird.
        Cells(i -9, 1) = Sheets("N" & i).Name
        Cells(i -9, 2) = "N-Test " & i      'Das funktioniert soweit auch ganz gut
       
        
        'Ab hier gibt es bereits Probleme, Objektvariable oder With-
        Blockvariable nicht festgelegt (Fehler 91)!
        ActiveChart.SeriesCollection(i - 79).XValues = "='N83'!R3C3:R4C3"
        ActiveChart.SeriesCollection(i - 79).Values = "='N83'!R3C4:R4C4"
        ActiveChart.SeriesCollection(i - 79).Name = "='N83'!R1C2:R1C4"
        ActiveChart.Location Where:=xlLocationAsNewSheet
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "N 80"
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "x-achse"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "y-achse"
        End With
   Next i
    
End Sub

  


Betrifft: AW: For-Next-Schleife + Sheetname von: Axel
Geschrieben am: 14.09.2004 12:36:10

Hallo Piotr,

die Daten für die einzelnen neu hinzuzufügenden Reihen stehen ja immer in den gleichen Zellbereichen der Tabellenblätter.

Du kannst somit folgende Schleife verwenden, wenn du statt der relativen Bezüge absolute angibst. (Hier beispielhaft nur für die Werte, der Zellbezug muß natürlich angepasst werden.)



Sub x()
   
   Dim ws As Worksheet
   
   For Each ws In Worksheets
      With ActiveChart.SeriesCollection.NewSeries
         .Values = ws.Range("A1:B1")
      End With
   Next
      
      
End Sub


     Code eingefügt mit Syntaxhighlighter 2.5


Auf den Blattnamen kannst du übrigens mit der Eigenschaft "Name" zugreifen.

Gruß
Axel


  


Betrifft: AW: For-Next-Schleife + Sheetname von: Piotr
Geschrieben am: 14.09.2004 13:50:44

Hallo Axel,

vorab vielen dank. Habe zu wenig Ahnung den Tip umzusetzen.
Wie müsste meine Schleife konkret verändert werden?

For i = 10 To 20
Ab hier gibt es bereits Probleme, Objektvariable oder With-
Blockvariable nicht festgelegt (Fehler 91)!
ActiveChart.SeriesCollection(i - 79).XValues = "='N83'!R3C3:R4C3"
ActiveChart.SeriesCollection(i - 79).Values = "='N83'!R3C4:R4C4"
ActiveChart.SeriesCollection(i - 79).Name = "='N83'!R1C2:R1C4"
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "N 80"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "x-achse"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "y-achse"
End With
Next i



  


Betrifft: AW: For-Next-Schleife + Sheetname von: Axel
Geschrieben am: 14.09.2004 16:46:46

Hallo,

dass es bei deinem Code Fehler gibt, sollte nicht wundern.

Die Schleife läuft von 10 bis 20 und du sprichst die Datenreihe (Schleifenvariable - 79), mithin also einen negativen Index an. Das kann nicht funktionieren.

Gruß
Axel


  


Betrifft: AW: For-Next-Schleife + Sheetname von: Piotr
Geschrieben am: 14.09.2004 18:31:01

Hallo Axel,

das war nur ein Beispiel. Normalerweise steht dort N=83 to X. Da ich vorher im Makro bereits manuell 3 Datenreihen eingegeben habe müsste die Schleife mit Index 4 fortlaufen.

Trotzdem wie bekommt man den Sheetnamen in diese Zeilen:
(die sheetnamen heißen bei mir "N", fangen bei 83 an und laufen bis 112 also: N112)
For i = 83 To 85
Ab hier gibt es bereits Probleme, Objektvariable oder With-
Blockvariable nicht festgelegt (Fehler 91)!
ActiveChart.SeriesCollection(i - 79).XValues = "='HIER MÜSSTE DER SHEETNAME HIN'!R3C3:R4C3"
ActiveChart.SeriesCollection(i - 79).Values = "='N83'!R3C4:R4C4"
ActiveChart.SeriesCollection(i - 79).Name = "='N83'!R1C2:R1C4"
ActiveChart.Location Where:=xlLocationAsNewSheet

Wie könnte die Lösung aussehen?

Im voraus vielen Dank,
Piotr



  


Betrifft: AW: For-Next-Schleife + Sheetname von: Axel
Geschrieben am: 15.09.2004 12:21:18

Hallo,

ich dachte mein Code-Beispiel wäre hinreichend gewesen.

Schau dir den doch noch einmal an. Mit "For Each ws In Worksheets" werden alle Tabellenblätter durchlaufen. Mit ws.Name kannst du dann direkt auf den Blattnamen zugreifen.

Eine Zuweisung wäre dann einfach mit Values = "=" & ws.Name & "'!R3C3:R4C3" innerhalb der Schleife möglich.

Jetzt verständlich?

Gruß
Axel


  


Betrifft: AW: For-Next-Schleife + Sheetname von: Piotr
Geschrieben am: 16.09.2004 09:15:36

Hallo Axel,

wie man merkt, bin ich ein blutiger VBA-Anfänger. Müsste das Makro jetzt so aussehen? Wenn ja, dann funktioniert es nicht. Kann weiter mit Ihrer Hilfe rechnen?


Im voraus vielen Dank,
Piotr

Sub Diagramme_erstellen()
'
' Makro2 Makro
' Makro am 10.09.2004 von Piotr aufgezeichnet
'
'
'
    Sheets("Sname80").Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmooth
    ActiveChart.SetSourceData Source:=Sheets("Sname80").Range("D21"), PlotBy:= _
        xlRows

    ActiveChart.SeriesCollection(1).XValues = "='Sname80'!R3C3:R4C3"
    ActiveChart.SeriesCollection(1).Values = "='Sname80'!R3C4:R4C4"
    ActiveChart.SeriesCollection(1).Name = "='Sname80'!R1C2:R1C4"
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).XValues = "='SName81'!R3C3:R4C3"
    ActiveChart.SeriesCollection(2).Values = "='SName81'!R3C4:R4C4"
    ActiveChart.SeriesCollection(2).Name = "='SName81'!R1C2:R1C4"
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).XValues = "='SName82'!R3C3:R4C3"
    ActiveChart.SeriesCollection(3).Values = "='SName82'!R3C4:R4C4"
    ActiveChart.SeriesCollection(3).Name = "='SName82'!R1C2:R1C4" 'Bis hierhin gibt es keine Probleme. Es wird ein Diagramm erstellt

        'ActiveChart.SeriesCollection.NewSeries 'Ab hier wird ein weiteres Diagramm hinzugefügt (Reihe 4)
            
            Dim ws As Worksheet
            For Each ws In Worksheets
                With ActiveChart.SeriesCollection.NewSeries
                        i = i + 4
                        ActiveChart.SeriesCollection(i).XValues = "=" & ws.Name & "'!R3C3:R4C3"
                        ActiveChart.SeriesCollection(i).Values = "=" & ws.Name & "'!R3C4:R4C4"
                        ActiveChart.SeriesCollection(i).Name = "=" & ws.Name & "'!R1C2:R1C4"
                End With
            Next
       

        ActiveChart.Location Where:=xlLocationAsNewSheet
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Sname80"
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "x-achse"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "y-achse"
        End With
  
    
End Sub