28.03.2016, 18:18
Hallo Atilla,
anbei mal der überarbeitete Code. Dauert nun nur noch 0,157 Sekunden, also ca. 10% Laufzeit gegenüber der ursprünglichen Version. Ich sammle die Gefärbten einer Zeile jetzt auch in einem String, weise den dann aber zeilenweise einer Collection zu und prüfe nur noch auf den Inhalt der Collection. Find nehme ich nicht, da nicht ausgeschlossen werden kann, dass eine Zahl im zu prüfenden Bereich gefärbt und ungefärbt enthalten ist. Das ist zumindest dann der Fall, wenn in AM1 nix steht. Das war im ersten Beispiel so und wurde bisher ja nicht ausgeschlossen, wenn ich nichts überlesen habe.
Modul mdl_schauan
anbei mal der überarbeitete Code. Dauert nun nur noch 0,157 Sekunden, also ca. 10% Laufzeit gegenüber der ursprünglichen Version. Ich sammle die Gefärbten einer Zeile jetzt auch in einem String, weise den dann aber zeilenweise einer Collection zu und prüfe nur noch auf den Inhalt der Collection. Find nehme ich nicht, da nicht ausgeschlossen werden kann, dass eine Zahl im zu prüfenden Bereich gefärbt und ungefärbt enthalten ist. Das ist zumindest dann der Fall, wenn in AM1 nix steht. Das war im ersten Beispiel so und wurde bisher ja nicht ausgeschlossen, wenn ich nichts überlesen habe.
Modul mdl_schauan
Option Explicit 'Deklaration der API-Funktion Private Declare Function GetTickCount Lib "kernel32.dll" () As Long Public Function CountColored4(ByVal iCalRow As Long, colnumbers As Collection) As Long 'Konstante fuer Debug-Kontrolle Const debCnt As Integer = 29 'Variablendeklarationen 'Long Dim cCount&, iCntC&, iCntR&, iColI&, iRowF& 'Bereich Dim rngCells As Range, Zellen As Range 'Boolean Dim bolTref As Boolean 'Collections Dim colNumb As Collection, colRows As Collection 'Collection initialisieren Set colNumb = New Collection Set colRows = New Collection 'Funktion in Zeile 1 mit Wert 0 verlassen If iCalRow = 1 Then CountColored4 = 0: Exit Function 'Alle zu pruefenden Zahlen im Bereich O:Z aufnehmen Set rngCells = Range("O1:Z" & iCalRow) 'Zahlen aufnehmen With rngCells 'Array redimensionieren Redim arrColR(1 To iCalRow) 'Schleife ueber alle Zeilen des Bereichs For iCntR = iCalRow To 1 Step -1 'Anzahl collectioneintraege in Variable speichern iColI = colNumb.Count 'Schleife ueber alle Zellen des Bereiches For Each Zellen In .Rows(iCntR).Cells 'Wenn nix in der Zelle steht, dann Schleife verlassen 'und weiter mit naechster Zeile If Zellen.Value = "" Then Exit For 'Wenn der Farbindex <> keine Fuellung ist, dann Zahl uebernehmen If Zellen.Interior.Color <> RGB(255, 204, 0) Then 'Bei Fehler weiter mit naechster Codezeile On Error Resume Next 'Zahl hinzufuegen, Fehler, wenn schon enthalten colNumb.Add Zellen.Value, CStr(Zellen.Value) 'Wenn kein Fehler, dann Zeilennummer merken If Err = 0 Then colRows.Add iCntR 'Fehlerbehandlung Ende On Error GoTo 0 'Oder Wenn der Farbindex = keine Fuellung ist, dann Else 'offene hochzaehlen cCount = cCount + 1 If iCalRow = debCnt Then Debug.Print iCalRow & vbTab & Zellen.Value & vbTab & Zellen.Address & " offen / farblos in O:Z" End If 'Ende Schleife ueber alle Zellen des Bereiches Next 'Ende Schleife ueber alle Zeilen des Bereichs Next 'Auszaehlen 'Schleife ueber alle collectioneintraege For iColI = 1 To colNumb.Count 'Wenn Zeilennumer = 1, dann 'Wenn Zeile des Eintrags in O:Z in AM eingetragen ist, dann 'If WorksheetFunction.CountIf(Columns("AM"), colRows(iColI)) > 0 Then If Cells(1, "AM") > 0 Then 'Startzeile = Eintrag iRowF = colRows(iColI) 'Alternativ Else 'Startzeile = 1 iRowF = 1 'Ende Wenn Zeile des Eintrags in O:Z in AM eingetragen ist, dann End If 'Wenn Suchzahl nicht im Bereich ist, dann If WorksheetFunction.CountIf(Range("D" & WorksheetFunction.Min(iRowF, iCalRow - 1) & ":I" & iCalRow - 1), colNumb(iColI)) = 0 Then cCount = cCount + 1 If iCalRow = debCnt Then Debug.Print iCalRow & vbTab & colNumb(iColI) & vbTab & colRows(iColI) & " offen / fehlt im Bereich D:I" 'Alternativ zu Wenn Suchzahl nicht im Bereich ist, dann Else 'Schleife ueber alle Zeilen For iCntR = iCalRow - 1 To WorksheetFunction.Min(iRowF, iCalRow - 1) Step -1 'Treffervariable auf false setzen bolTref = False 'Pruefzeile feststellen 'Schleife ueber die Eintraege der Zeile in Spalten D:I 'For iCntC = 4 To 9 'Wenn der Zelleninhalt der gesuchten Zahl entspricht und die Zelle eingefaerbt ist, dann 'If Cells(iCntR, iCntC).Value = colNumb(iColI) And Cells(iCntR, iCntC).Interior.ColorIndex <> xlNone Then If InStr(1, colnumbers(iCntR), "#" & colNumb(iColI) & "#") > 0 Then 'Treffervariable auf true setzen If iRowF <> iCalRow Then bolTref = True If iCalRow = debCnt Then Debug.Print iCalRow & vbTab & colNumb(iColI) & vbTab & colRows(iColI) & vbTab & _ "Treffer in Zeile " & iCntR '"Treffer in " & Cells(iCntR, iCntC).Address 'Schleife verlassen 'Exit For 'Ende Wenn der Zelleninhalt der gesuchten Zahl entspricht und die Zelle eingefaerbt ist, dann End If 'Ende Schleife ueber die Eintraege der Zeile in Spalten D:I 'Next 'Wenn Treffervariable true, dann Schleife verlassen If bolTref = True Then Exit For 'Ende Schleife ueber alle Collectioneintraege Next 'Wenn nix gefunden wurde, dann If bolTref = False Then 'Offene-Zaehler hochsetzen cCount = cCount + 1 If iCalRow = debCnt Then Debug.Print iCalRow & vbTab & colNumb(iColI) & vbTab & colRows(iColI) & " offen / farblos in D:I" 'Ende Wenn nix gefunden wurde, dann End If 'Ende Wenn Suchzahl nicht im Bereich ist, dann End If 'Ende Schleife ueber alle collectioneintraege Next End With 'Counter an Funktionswert geben CountColored4 = cCount End Function Sub AusZaehlen() 'Variablendeklarationen 'Long Dim iCnt&, lRow&, loStartTime& 'Collection Dim colnumbers As Collection 'Collection initialisieren Set colnumbers = New Collection 'Startzeit uebernehmen loStartTime = GetTickCount 'Spalte AN (40) Leeren Columns(40).ClearContents 'letzte Zeile mit Eintraegen >0 feststellen lRow = WorksheetFunction.Max(Application.Evaluate("LOOKUP(2,1/(D1:D1000>0),ROW(D1:D1000))"), _ Application.Evaluate("LOOKUP(2,1/(E1:E1000>0),ROW(E1:E1000))"), _ Application.Evaluate("LOOKUP(2,1/(F1:F1000>0),ROW(F1:F1000))"), _ Application.Evaluate("LOOKUP(2,1/(G1:G1000>0),ROW(G1:G1000))"), _ Application.Evaluate("LOOKUP(2,1/(H1:H1000>0),ROW(H1:H1000))"), _ Application.Evaluate("LOOKUP(2,1/(I1:I1000>0),ROW(I1:I1000))")) 'Schleife bis zur letzten belegten Zeile <> 0 - Eintraege im Bereich D:I For iCnt = 1 To lRow 'Wenn mindestens 3 Zellen gefaerbt sind, dann If CountColored(iCnt, colnumbers) >= 3 Then 'Wert fuer Spalte AN berechnen und eintragen Cells(iCnt, 40) = CountColored4(iCnt, colnumbers) 'Ende Wenn mindestens 3 Zellen gefaerbt sind, dann End If 'Ende Schleife bis zur letzten belegten Zeile (anhend Spalte D (4)) im Bereich D:I Next 'Meldung Laufzeit in Sekunden aus Differenz von Systemzeit 'und Startzeit / 1000 MsgBox "Laufzeit " & _ (GetTickCount - loStartTime) / 1000 & " Sekunden.", _ vbInformation, "Application.Wait Soll: 3 Sekunden" End Sub Public Function CountColored(ByVal lRow As Long, colnumbers As Collection) As Long 'Variablendeklarationen Dim Zellen, cCount&, rngCells As Range, strNumbers$ 'Rueckgabewert 0 zuweisen CountColored = 0 'Zeilenbereich setzen Set rngCells = Cells(lRow, 4).Resize(1, 6) 'Anfang des String fuer Collection # zuweisen strNumbers = "#" 'wenn die Summe der Zeileneintraege gt. 0 ist, dann If WorksheetFunction.Sum(rngCells) > 0 Then 'Schleife ueber alle Zellen des Bereiches For Each Zellen In rngCells 'Wenn der Farbindex <> keine Fuellung ist, dann If Zellen.Interior.ColorIndex <> xlNone Then 'Counter hochzaehlen cCount = cCount + 1 'Collection-Text erweitern strNumbers = strNumbers & Zellen.Value & "#" 'Ende Wenn der Farbindex <> keine Fuellung ist, dann End If 'Ende Schleife ueber alle Zellen des Bereiches Next 'Ende wenn die Summe der Zeileneintraege gt. 0 ist, dann End If 'Collection erweitern colnumbers.Add strNumbers, CStr(lRow) 'Counter an Funktionswert geben CountColored = cCount End Function
. \\\|/// Hoffe, geholfen zu haben.
( ô ô ) Grüße, André aus G in T
ooO-(_)-Ooo (Excel 97-2019+365)
( ô ô ) Grüße, André aus G in T
ooO-(_)-Ooo (Excel 97-2019+365)