Office: For Schleife mit Array

Helfe beim Thema For Schleife mit Array in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo ich versuche gerade meinen bisherigen Code zusammenzufassen/verkleinern. Kurze Zusammenfassung: In Tabelle "Rohdaten" stehen in der Spalte G... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von d3nz, 3. Oktober 2016.

Seite 1 von 2

D3NZ

d3nz

For Schleife mit Array

Hallo

ich versuche gerade meinen bisherigen Code zusammenzufassen/verkleinern.

Kurze Zusammenfassung:
In Tabelle "Rohdaten" stehen in der Spalte G 3-stellige Zahlen (Klasse) und in Spalte J andere Zahlen (Gewicht).
Das Sub soll nun jeweils alle Gewichte einer Klasse summieren.

Grundsätzlich funktioniert es. Nun möchte ich das Ganze wie gesagt zusammenfassen - mithilfe einer For-Schleife. Momentan sieht es wie folgt aus:

Code:

Sub SumSchrott()

Dim zeileMax As Long
Dim intKlasse As Integer
Dim intZeile As Integer
Dim i As Integer
Dim arrayKlasse
Dim arraySum

intZeile = 13
i = 1
arrayKlasse = Array(201, 202, 203, 204, 205, 206, 207, 270, 280, 301, 302)
arraySum = Array(sum201, sum202, sum203, sum204, sum205, sum206, sum207, sum270, sum280, sum301, sum302)

With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row

sum201 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 201, .Range("J2:J" & zeileMax))
sum202 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 202, .Range("J2:J" & zeileMax))
sum203 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 203, .Range("J2:J" & zeileMax))
sum204 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 204, .Range("J2:J" & zeileMax))
sum205 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 205, .Range("J2:J" & zeileMax))
sum206 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 206, .Range("J2:J" & zeileMax))
sum207 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 207, .Range("J2:J" & zeileMax))
sum270 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 270, .Range("J2:J" & zeileMax))
sum280 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 280, .Range("J2:J" & zeileMax))
sum301 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 301, .Range("J2:J" & zeileMax))
sum302 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 302, .Range("J2:J" & zeileMax))

For intKlasse = 1 To 11
Tabelle1.Cells(intZeile, 4) = arraySum(i)
intZeile = intZeile + 1
i = i + 1
Next intKlasse

End With

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Ich weiß ich kann es noch kompakter machen, wenn ich auch die sumIf-Funktion in die Schleife packe, aber ich wollte das lieber nach und nach machen, damit ich nicht gleich 10 Fehler habe die ineinander verstrickt sind.

Jetzt bekomme ich jedenfalls den Laufzeitfehler 9 "Index außerhalb des gültigen Bereichs".
Folgende Zeile ist markiert: Code:

Tabelle1.Cells(intZeile, 4) = arraySum(i)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Tabelle1 existiert und ist richtig geschrieben
intZeile existiert auch, ist richtiggeschrieben, wurde deklariert und definiert
ebenso arraySum und i

Liegt der Array irgendwie außerhalb des Bereichs?

Vielen Dank im Voraus!

:)

d3nz, 3. Oktober 2016

#1
CRAZY TOM

Crazy Tom

Hallo

zum einen läuft ein Array von 0 bis 10 und nicht von 1 bis 11
es sei denn du hast oben im Modul "Option Base" stehen dann zählt es von 1 an
zum zweiten gehört die Zuweisung zum Array nach den SumIf
denn vorher ist das Array leer

so habe ich es getestet
Code:

Sub SumSchrott()

Dim zeileMax As Long
Dim intKlasse As Integer
Dim intZeile As Integer
Dim i As Integer
Dim arrayKlasse
Dim arraySum

intZeile = 13
i = 1
arrayKlasse = Array(201, 202, 203, 204, 205, 206, 207, 270, 280, 301, 302)

With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row

sum201 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 201, .Range("J2:J" & zeileMax))
sum202 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 202, .Range("J2:J" & zeileMax))
sum203 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 203, .Range("J2:J" & zeileMax))
sum204 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 204, .Range("J2:J" & zeileMax))
sum205 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 205, .Range("J2:J" & zeileMax))
sum206 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 206, .Range("J2:J" & zeileMax))
sum207 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 207, .Range("J2:J" & zeileMax))
sum270 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 270, .Range("J2:J" & zeileMax))
sum280 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 280, .Range("J2:J" & zeileMax))
sum301 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 301, .Range("J2:J" & zeileMax))
sum302 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 302, .Range("J2:J" & zeileMax))

arraySum = Array(sum201, sum202, sum203, sum204, sum205, sum206, sum207, sum270, sum280, sum301, sum302)

For intKlasse = 1 To 11
Tabelle1.Cells(intZeile, 4) = arraySum(i - 1)
intZeile = intZeile + 1
i = i + 1
Next intKlasse

End With

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

MfG Tom

Crazy Tom, 5. Oktober 2016

#2
R J

R J
...ergänzend zu Tom's Bemerkungen...

Wozu wird arrayKlasse benötigt? Ich ahne zwar, was Du damit bezwecken wolltest, aber die Ahnung wird nicht bestätigt....*Smilie

Auch dieses Konstrukt ist fragwürdig:
Code:

For intKlasse = 1 To 11
Tabelle1.Cells(intZeile, 4) = arraySum(i - 1)
intZeile = intZeile + 1
i = i + 1
Next intKlasse
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Du benutzt für die Schleife intKlasse als Schleifenzähler, aber im Array arraySum benutzt Du i. Das ist unnötig.

Mach es gleich so:
Code:

Code:

Code:

For [b]intKlasse [/b]= 0 To 10 Tabelle1.Cells([b]intKlasse [/b]+ 13, 4) = arraySum([b]intKlasse [/b]) Next intKlasse

Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.
R J, 5. Oktober 2016

#3
CRAZY TOM

Crazy Tom

For Schleife mit Array

Hallo

so erfüllt es auch seinen Zweck

Code:

Sub SumSchrott()

Dim zeileMax As Long
Dim intKlasse As Integer
Dim intZeile As Integer
Dim i As Integer
Dim arrayKlasse
Dim arraySum(0 To 10)

i = 1
intZeile = 13
arrayKlasse = Array(201, 202, 203, 204, 205, 206, 207, 270, 280, 301, 302)

With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row
For i = 0 To 10
arraySum(i) = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), arrayKlasse(i), .Range("J2:J" & zeileMax))
Next
i = 1
For intKlasse = 0 To 10
Tabelle1.Cells(intZeile, 4) = arraySum(intKlasse)
intZeile = intZeile + 1
i = i + 1
Next intKlasse
End With
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

MfG Tom

Crazy Tom, 5. Oktober 2016

#4
CRAZY TOM

Crazy Tom

Hallo

und noch ein bissel verkürzt und überflüssiges entfernt

Code:

Sub SumSchrott()
Dim zeileMax As Long
Dim intKlasse As Integer
Dim intZeile As Integer
Dim i As Integer
Dim arrayKlasse
Dim arraySum(0 To 10)

intZeile = 13
arrayKlasse = Array(201, 202, 203, 204, 205, 206, 207, 270, 280, 301, 302)

With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row
For i = 0 To 10
arraySum(i) = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), _
arrayKlasse(i), .Range("J2:J" & zeileMax))
Next
For intKlasse = 0 To 10
Tabelle1.Cells(intZeile, 4) = arraySum(intKlasse)
intZeile = intZeile + 1
Next intKlasse
End With
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

MfG Tom

Crazy Tom, 5. Oktober 2016

#5
D3NZ

d3nz

Hallo,

@Tom: Das mit ab 0 hätte ich wissen müssen. *rolleyes.gif*
Zum Ändern der Reihenfolge. Ich dachte wenn ich im zweiten Schritt auch sumIf in die For-Schleife packe dann muss in der Schleife ja das Array durchlaufen werden und dafür müsste es ja vorher eine Zuweisung gegeben haben. *confused.gif*

@R J: Im zweiten Schritt möchte ich sumIf auch in die For-Schleife packen und da muss sich zum Einen der Speicherort ändern (daher arraySum) und zum Anderen auch das Kriterium und dafür benötige ich dann arrayKlasse.
Vielen Dank für den Tipp mit den 2 unnötigen Variablen. Ich war mir nicht sicher ob es schneller geht wenn das Programm eine eigene variable hat oder wenn es jedes mal 13 hinzuzählen muss. Im Nachhinein leuchtet es mir aber ein *grins

Gruß Dennis

d3nz, 5. Oktober 2016

#6
EARLFRED

EarlFred

Hallo zusammen,

und noch ein bissel verkürzt und überflüssiges entfernt
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Code:

Option Explicit

Sub SumSchrott()

Dim fVarRet As Variant, arrayKlasse As Variant
Dim zeileMax As Long

arrayKlasse = Array(201, 202, 203, 204, 205, 206, 207, 270, 280, 301, 302)

With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row
fVarRet = Application.SumIf(.Range("G2:G" & zeileMax), arrayKlasse, .Range("J2:J" & zeileMax))
Tabelle1.Cells(13, 4).Resize(UBound(fVarRet)).Value = Application.Transpose(fVarRet)
End With
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Ich war mir nicht sicher ob es schneller geht
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Zellen einzeln zu befüllen ist nie schneller, als dies in einem Rutsch zu tun.

Grüße
EarlFred

EarlFred, 5. Oktober 2016

#7
XLPH

xlph

For Schleife mit Array

Geht auch mit reiner Array-Formel:

Code:

Public Sub xlph_FormulaArray()

Dim avntCriteria() As Variant
Dim lngRowLast As Long
Dim strFormula As String

avntCriteria = Array(201, 202, 203, 204, 205, 206, 207, 270, 280, 301, 302)

With ThisWorkbook.Worksheets("Rohdaten")
lngRowLast = .Cells(.Rows.Count, 7).End(xlUp).Row
strFormula = "=SUMIF(" & _
.Range("G2:G" & lngRowLast).Address(0, 0, , 1) & _
",{" & Join(avntCriteria, ";") & "}," & _
.Range("J2:J" & lngRowLast).Address(0, 0, , 1) & ")"
End With

With Tabelle1.Cells(13, 4).Resize(UBound(avntCriteria) + 1)
.FormulaArray = strFormula
.FormulaArray = .Value
End With

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

xlph, 5. Oktober 2016

#8
D3NZ

d3nz

Guten Morgen,

ok. *boah

also die letzten beiden Beiträge versteh ich nicht ganz. Wo ist da die Schleife? *confused.gif*

Ich habe das Ganze jetzt so verkürzt (inkl. dem Beispiel von Tom (Loop für sumIf), dass es für mich nachvollziehbar ist, auch wenn ich mir das in 8 Monaten nochmal anschaue^^

Code:

Sub SumSchrott()

Dim zeileMax As Long
Dim i As Integer
Dim arrayKlasse
Dim arraySum

With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row

arrayKlasse = Array(201, 202, 203, 204, 205, 206, 207, 270, 280, 301, 302)
arraySum = Array(sum201, sum202, sum203, sum204, sum205, sum206, sum207, sum270, sum280, sum301, sum302)

For i = 0 To 10
arraySum(i) = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), arrayKlasse(i), .Range("J2:J" & zeileMax))
Tabelle1.Cells(i + 13, 4) = arraySum(i)
Next i

End With

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Vielen Dank!*hands

d3nz, 5. Oktober 2016

#9
CRAZY TOM

Crazy Tom

Hallo

ich muss ja sagen dass ich grundsätzlich keinen Plan von Arrays habe
wenn ich aber deinen Code anschaue, da fehlt in deinem Modul sicher
ganz oben "Option Explicit"
denn sonst würde dein Code in einen Fehler laufen

diese Zeile
Code:

arraySum = Array(sum201, sum202, sum203, sum204, sum205, sum206, sum207, sum270, sum280, sum301, sum302)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

brauchst du nicht wenn du arraySum so deklarierst
wenn du dir im Lokalfenster die Werte der einzelnen sum201 bis sum302 anschaust, wirst du feststellen, dass die Variablen leer sind
Code:

Dim arraySum(0 To 10)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

dann würde der Code so aussehen und funktioniert auch ohne Fehlermeldung
Code:

Sub SumSchrott()
Dim zeileMax As Long
Dim i As Integer
Dim arrayKlasse
Dim arraySum(0 To 10)

With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row
arrayKlasse = Array(201, 202, 203, 204, 205, 206, 207, 270, 280, 301, 302)
For i = 0 To 10
arraySum(i) = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), arrayKlasse(i), .Range("J2:J" & zeileMax))
Tabelle1.Cells(i + 13, 4) = arraySum(i)
Next i
End With
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

MfG Tom

Crazy Tom, 5. Oktober 2016

#10
D3NZ

d3nz

Also ich bekomme auch ohne Option Explicit keine Fehlermeldung.

Aber danke für den Tipp mit Code:

Dim arraySum(0 To 79)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Hatte mir zwar gerade schon die Arbeit gemacht, alles per Hand einzu tippen, aber so sieht's auch schöner aus *Smilie

d3nz, 5. Oktober 2016

#11
CRAZY TOM

Crazy Tom

Hallo

kopier das hier mal in ein Modul und lass den Code laufen

Code:

Option Explicit

Sub SumSchrott()
Dim zeileMax As Long
Dim i As Integer
Dim arrayKlasse
Dim arraySum
With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row
arrayKlasse = Array(201, 202, 203, 204, 205, 206, 207, 270, 280, 301, 302)
arraySum = Array(sum201, sum202, sum203, sum204, sum205, sum206, sum207, sum270, sum280, sum301, sum302)
For i = 0 To 10
arraySum(i) = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), arrayKlasse(i), .Range("J2:J" & zeileMax))
Tabelle1.Cells(i + 13, 4) = arraySum(i)
Next i
End With
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

und da bekommst du keine Fehlermeldung?

MfG Tom

Crazy Tom, 5. Oktober 2016

#12
D3NZ

d3nz

For Schleife mit Array

Hallo Tom,

doch nun schon.
Ich hatte verstanden, dass ich 'ne Fehlermeldung bekomme, wenn ich NICHT das Option Explicit habe.

d3nz, 5. Oktober 2016

#13
EARLFRED

EarlFred

Hallo Dennis,

warum nur so kompliziert? Die Ergebnisse erst in ein Array zwischenspeichern und dann sofort wieder herausholen und einzeln in die Tabelle zu schreiben ist nicht nur umständlich, sondern auch langsamer (was man bei den wenigen Daten zugegebenermaßen nicht merkt, bei größeren Daten sind einzelne Zellzugriffe aber ätzend langsam).

Wenn, dann geh den direkten Weg:
Code:

Option Explicit

Sub SumSchrott()

Dim zeileMax As Long
Dim i As Long
Dim arrayKlasse As Variant

With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row
arrayKlasse = Array(201, 202, 203, 204, 205, 206, 207, 270, 280, 301, 302)

For i = LBound(arrayKlasse, 1) To UBound(arrayKlasse, 1)
Tabelle1.Cells(i + 13, 4) = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), arrayKlasse(i), .Range("J2:J" & zeileMax))
Next i

End With

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

wenn ich NICHT das Option Explicit habe.
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Option Explicit erzwingt die Deklaration der verwendeten Variablen / Funktionen usw.

dass es für mich nachvollziehbar ist, auch wenn ich mir das in 8 Monaten nochmal anschaue^^
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Beschäftige Dich halt damit und stelle Fragen, wenn Du etwas nicht verstehst - dann erweiterst Du Deinen Horizont und es wird kein Problem mehr für Dich darstellen, es noch in 2 Jahren nachzuvollziehen.
Wenn es Dich interessiert, geh den Code im Einzelschritt durch und schau Dir an, was sich bei den Variablen und im Arbeitsblatt so alles tut nach jeder Zeile.

Grüße
EarlFred

EarlFred, 5. Oktober 2016

#14
D3NZ

d3nz

Hallo,

@ EarlFred: Danke. ich werd mir das nochmal angucken. Komme aber die nächsten Tage nicht dazu.

d3nz, 6. Oktober 2016

#15

(Du musst angemeldet oder registriert sein, um eine Antwort erstellen zu können.)

Seite 1 von 2

Thema:

For Schleife mit Array

Die Seite wird geladen...

For Schleife mit Array - Similar Threads - For Schleife Array
For Next Schleife
in Microsoft Excel Hilfe
For Next Schleife: Hallo zusammen, da bin ich wieder und wie kann es anders sein mit einem Problem. Ich habe eine Tabelle (im Anhang) dort soll die Werte in Zellen a/b/f kopiert werden und in die erste frei Zeile...
Access For next Schleife
in Microsoft Access Hilfe
Access For next Schleife: Ich habe in einer Tabelle meine Spieler mit einer Spielernummer enthalten. Jeder Spieler hat als Bericht ein "Stammblatt", welches mit der Spielernummer aufgerufen wird. Ich möchte jetzt gerne...
For Each Schleife Anstatt ActiveSheet festen Tabellenblattnamen benutzen
in Microsoft Excel Hilfe
For Each Schleife Anstatt ActiveSheet festen Tabellenblattnamen benutzen: Hallo , habe folgende Schleife geschrieben : Dim rngBereich As Range Dim rngZelle As Range Sub Test() Set rngBereich = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _...
For-Next-Schleife für Textfeldübertragung
in Microsoft Access Hilfe
For-Next-Schleife für Textfeldübertragung: Hallo zusammen, ich habe 20 Textfelder (txt1 bis txt20) und muss die in 20 andere Textfelder (txt1S bis txt20S) übertragen. Nun habe ich mir gedacht, dass ich das mit einer For-Next-Zähler machen...
For Schleife nicht initialisiert
in Microsoft Access Hilfe
For Schleife nicht initialisiert: Hi, bräuchte dringend Hilfe. Ich sende per folgenden Befehl Emails raus.(per cdo) Blos jetzt auf einmal bringt er mir den Laufzeitfehler 92 mit For Schleife nicht initalisiert. Code: Option...
Glücksrad mit VBA und For-Schleifen
in Microsoft Excel Hilfe
Glücksrad mit VBA und For-Schleifen: Hallo zusammen! Da ich hier immer Hilfe finde wende ich mich mal wieder mit folgendem Problem an euch: Ich möchte in Excel etwas auslosen. Das ansich ist auch kein Problem. Damit es etwas...
"find and replace" mit "for-Schleife" kombinieren
in Microsoft Word Hilfe
"find and replace" mit "for-Schleife" kombinieren: Guten Tag miteinander, Ich bin grade dabei ein kleines Programm zu erstellen, dass alphabetisch durchgearbeitet wird und jedesmal eine Ersetzung durchführt. Hier ist der Code: Sub...

Anmelden oder registrieren

Suche

Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.

Akzeptieren Weitere Informationen...

Information ausblenden