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
1544to1548
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
1544to1548
1544to1548
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

each statt for to

each statt for to
15.03.2017 16:13:57
Markus
Hallo Leute,
ich hätte da mal ne Frage zu Schleifen.
Ich verwende, was auch funktioniert, folgenden Code, hier ein Auszug:
Dim ANr As Range
Dim Prod As Range
Dim Soll As Range
Dim Ist As Range
LzQuelle = Sheets("Quelle").Cells(Rows.Count, 1).End(xlUp).Row
Set ANr = Sheets("Quelle").Range("F2:F" & LzQuelle)
Set Prod = Sheets("Quelle").Range("G2:G" & LzQuelle)
Set Soll = Sheets("Quelle").Range("J2:J" & LzQuelle)
Set Ist = Sheets("Quelle").Range("K2:K" & LzQuelle)
LzBer = Sheets("Ber").Cells(Rows.Count, 1).End(xlUp).Row
With WorksheetFunction
For i = 2 To LzBer
erg = .CountIfs(ANr, Sheets("Ber").Cells(i, 1), Prod, "Kühlelement", Soll, 1, Ist, 1)
Sheets("Ber").Cells(i, 4) = erg
Next i
End With
Ihr wisst natürlich alle sofort um was es geht.
Das Problem:
Die Quellseite hat gut 30000, die Ber Seite gut 15000 Zeilen. Und ich muss die suche mit unterschiedlichen Parametern acht mal laufen lassen, was gut über zehnn Minuten dauert. Gibt es hier eine schnellere Methode. z.B. mit Each?
Danke an euch
Susmark

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: each statt for to
15.03.2017 16:23:41
Michael
Hallo!
Vermutlich wäre es hilfreicher, wenn Du aufzeigst, was die eigentliche Aufgabe ist, die der Code für Dich lösen soll, ggf. auch mit einer Bsp-Mappe - dann werden sich mit Sicherheit Helferinnen finden, die Dir effizienten Code schreiben. Denn aus diesem Code-Fragment allein (ohne Kenntnis der Verhältnisse) ist höhere Effizienz nicht unbedingt abzuleiten.
Bzgl. For Each vs For...To... - Mit einer For Each wird's nicht unbedingt schneller, die Frage ist eher, ob die Berechnungen etc. nicht in einem Array anstatt in Blatt-Bereichen abgearbeitet werden können.
LG
Michael
Anzeige
AW: Arrays sind ca 40 x schneller (owT)
15.03.2017 16:24:36
Fennek
AW: each statt for to
15.03.2017 17:24:28
Daniel
Hi
um das bei dieser Datenmengen zu beschleunigen, müsste man Arrays und das Dictionary-Objekt einsetzen.
mit dem Dictionary-Objekt kannst du die Anzahl der gewünschten Vorkommen in einem Schleifendurchlauf über die Quelle ermitteln und dann brauchst du die nur noch abrufen.
das Dictionary-Objekt ist im Prinzip ein eindimensionales Array, das anstelle des Zahlenindex einen Freitext als Index verwendest.
Damit lassen sich solche Aufgaben schnell und elegant lösen.
der Code (ungetestet) sieht dann in etwa so aus:
Sub test()
Dim dic
Dim arr
Dim ID As String
Dim Z As Long
With Sheets("Quelle")
arr = .Range("F2:K" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value
End With
Set dic = CreateObject("Scripting.Dictionary")
For Z = 1 To UBound(arr, 1)
ID = Join(Array(arr(Z, 1), arr(Z, 2), arr(Z, 5), arr(Z, 6)), "|")
dic(ID) = dic(ID) + 1
Next
With Sheets("Ber")
With .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
arr = .Value
For Z = 1 To UBound(arr, 1)
ID = arr(Z, 1) & "|Kühlelement|1|1)"
arr(Z, 1) = dic(ID)
Next
.Offset(0, 3).Value = arr
End With
End With
End Sub
Gruß Daniel
Anzeige
AW: each statt for to
16.03.2017 08:20:28
Markus
Hallo zusammen, Hallo Daniel
danke für die Mühe. Ich denke, ich verstehe die Formel, leider funktioniert sie nicht.
Zum Verständnis:
Wir legen Array fest.
Die Programmzeile
ID = Join(Array(arr(Z, 1), arr(Z, 2), arr(Z, 5), arr(Z, 6)), "|")
bezieht sich auf die Spalten F,G,J und K. Wozu benötigen wir hier die Pipe?
Die Programmzeile
ID = arr(Z, 1) & "|Kühlelement|1|1)"
sucht im Array Z1 (Quelle Spalte F, Ber spalte A) nach Übereinstimmung und prüft dann die Spalten G,J und K auf die angegeben Parameter. Liege ich hier richtig?
Das Makro läuft in einer Sekunde durch, jedoch ohne Ergebnis und ohne Fehlermeldung.
Ich lade mal einen Beispieldatei hoch. Im Sheet Ber werden lediglich die A-Nr sortiert
https://www.herber.de/bbs/user/112219.xlsm
Danke nochmals
Anzeige
AW: each statt for to
16.03.2017 09:21:08
Daniel
Hi
ich fasse hier die Spalten zu einem ID-Begriff. Damit die Spalteninhalte auch sauber voneinander getrennt sind, verwende ich ein Trennzeichen, von dem ich annehmen kann, dass es in den Spaltenwerten nicht vorkommt.
Da hat sich die Pipe ganz gut bewährt, vorallem weil es die Inhalte auch optisch gut trennt (vorteilhaft, wenn man den Code testen muss)
btw deine Beispielmappe enthält keinen Code den man testen kann, diesen bitte noch einfügen.
wenn ich meinen gezeigten Code einfüge, dann muss das Ergebnis "leer" sein, weil es in der Liste keinen eintrag mit "Kühlelement" gibt.
auch bei den Zahlen musst du mal schauen, wie diese in den ID-String übernommen werden und dann im zweiten Teil des Codes auch genauso in den ID-String schreiben.
Gruß Daniel
Anzeige
AW: each statt for to
16.03.2017 09:47:08
Markus
Hallo Daniel.
Es geht natürlich nicht um Kühlmittel, Lüfter und Kleiteile. Aus Datenschutzgründen musste ich mir hier etwas einfallen lassen.
Die Datei ist zu groß zu Uploaden, hier der Code, den ich bis jetzt habe und der noch nicht vollständig ist.
Public Sub Berechnung()
Dim LzQuelle As Integer
Dim LzBer As Integer
Dim ANr As Range
Dim Prod As Range
Dim Soll As Range
Dim Ist As Range
Dim Klasse As Range
Dim VbKz As Range
Dim erg As String
Dim i As Integer
Sheets("Ber").Range("2:20000").Clear
LzQuelle = Sheets("Quelle").Cells(Rows.Count, 6).End(xlUp).Row
Sheets("Quelle").Range("F2:F" & LzQuelle).Copy Destination:=Worksheets("Ber").Range("A2")
Sheets("Quelle").Range("D2:D" & LzQuelle).Copy Destination:=Worksheets("Ber").Range("B2")
Sheets("Quelle").Range("E2:E" & LzQuelle).Copy Destination:=Worksheets("Ber").Range("C2")
Sheets("Ber").Range("A1:C20000").RemoveDuplicates Columns:=1, Header:=xlYes
Set ANr = Sheets("Quelle").Range("F2:F" & LzQuelle)
Set Prod = Sheets("Quelle").Range("G2:G" & LzQuelle)
Set Soll = Sheets("Quelle").Range("J2:J" & LzQuelle)
Set Ist = Sheets("Quelle").Range("K2:K" & LzQuelle)
LzBer = Sheets("Ber").Cells(Rows.Count, 1).End(xlUp).Row
'FbH 11
With WorksheetFunction
For i = 2 To LzBer
erg = .CountIfs(ANr, Sheets("Ber").Cells(i, 1), Prod, "Kühlmittel", Soll, 1, Ist, 1)
Sheets("Ber").Cells(i, 4) = erg
Next i
End With
'FbH 10
With WorksheetFunction
For i = 2 To LzBer
erg = .CountIfs(ANr, Sheets("Ber").Cells(i, 1), Prod, "Kühlmittel", Soll, 1, Ist, 0)
Sheets("Ber").Cells(i, 5) = erg
Next i
End With
'FbH 01
With WorksheetFunction
For i = 2 To LzBer
erg = .CountIfs(ANr, Sheets("Ber").Cells(i, 1), Prod, "Kühlmittel", Soll, 0, Ist, 1)
Sheets("Ber").Cells(i, 6) = erg
Next i
End With
'CoH
With WorksheetFunction
For i = 2 To LzBer
erg = .CountIfs(ANr, Sheets("Ber").Cells(i, 1), Prod, "Schlauch", Soll, 0, Ist, 1)
Sheets("Ber").Cells(i, 7) = erg
Next i
End With
End Sub

Anzeige
AW: each statt for to
16.03.2017 10:35:51
Daniel
Hi
CountIfs ist bei der genannten Datenmenge zu langsam.
jedes CountIfs beinhaltet im Prinzp eine Schleife über jedes Zeile des angegebenen Zellbereichs.
schau dir mein Dictionary-Beispiel nochmal an.
Gruß Daniel
AW: each statt for to
16.03.2017 11:16:03
Markus
Hallo Daniel,
Hab ich gemach. Trotzdem bekomme ich keine Ergebnis mit deiner Lösung.
Nochmal zu Verständnis einen Frage
Mit
arr = .Range("F2:K" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value
und
ID = Join(Array(arr(Z, 1), arr(Z, 2), arr(Z, 5), arr(Z, 6)), "|")
Setzen wir 4 Arrays in die Spalten F (Z,1) G (z,2), J (Z,5) und K (Z,6). Brauchen die Arrays keine fortlaufenden Nummern?
warum werden in
ID = arr(Z, 1) & "|Kühlgerät|1|1)"
nur die letzten drei Parameter abgefragt? Bezieht sich hier Z1 auf Z1? Weshalb wird die Zeile mit einer klammer zu abgeschlossen, müsste hier auch nicht irgendwo eine Klammer auf sein?
Kuriosum: Wenn ich diese Klammer entferne, berechnet er mir die Zeile 2 in "Ber", sonst aber nichts
Danke
Anzeige
AW: each statt for to
16.03.2017 11:44:04
Markus
Hallo Daniel,
vergiss meine Letze Antwort, wenn ich in der Quelldatei das Format in Spalte j und k von original 1,0 auf 1 ändere funktioniert das ganze, Aber:
Wenn ich die Format auf 1,0 ändere klappt es nicht mehr. Hat das Komma hier eine Funktion?
Danke
AW: each statt for to
16.03.2017 12:11:56
Daniel
Hi
das kann da schon eine Auswirkung haben.
du musst mal in der Schleife, in welcher der ID-String generiert wird, nach dem JOIN einen Haltepunkt setzen und dir anschauen, wie die Zahlenwerte in den Text übernommen werden (hierzu dann, wenn der code gestoppt hat, im Direktfenster den Befehl: ?ID eingeben oder mit der Maus über den Variablennamen hovern.
genauso wie es hier angezeigt wird (mit Kommas und NK-Stellen) musst du dann die Zahl auch im zweiten Teil an den ID-String vergeben.
Gruß Daniel
Anzeige
AW: each statt for to
16.03.2017 12:46:10
Markus
Hallo Daniel
super, jetzt haut das ganze wunderbar hin. Mein Held.
Jetzt noch, auf die Gefahr hin dich langsam zu Nerven, eine allerletzte Fragen.
Ist es Möglich, hier eine Widecard zu verwenden, "Kühl*" funktioniert schon mal nicht.
Dann lass ich dich in Ruhe und bedanke mich vielmals für deine Geduld mit einem VBA Anfänger
AW: each statt for to
16.03.2017 13:05:32
Daniel
Hi
schwierig.
bei dieser Methode brauchst du dann schon eine genaue Übereinstimmung im ID-Begriff.
da bin ich jetzt übfraqt, bzw ich müsste für eine Lösung mehr zeit aufwenden, als ich es für lau bereit bin.
Gruß Daniel
AW: each statt for to
16.03.2017 14:19:06
Markus
Alles Klar. Dachte ich mir eh schon.
Tausend Dank
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige