05.12.2020, 13:30 (Dieser Beitrag wurde zuletzt bearbeitet: 05.12.2020, 14:02 von schauan.
Bearbeitungsgrund: Schriftgröße
)
Hallo zusammen, leider bekomme ich einen Laufzeitfehler " '-2147417848(80010108)' Method 'Range' of object '_Worksheet' " failed und finde die Ursache nicht dafür: Fehler wird immer nach einigen Minuten angezeigt, nachdem das Makro bereits mehrmals durchlaufen wurde. Kann von Euch Experten jemand einen Fehler erkennen, welchen den Laufzeitfehler auslöst? Danke Anbei das Makro:
Sub Prüfung_1() With Worksheets("Sheet1") If .Range("BPK2").Value = "x" Then For y = .Range("BQC1").Column To .Range("DTL1").Column If .Cells(1, y).Value < .Range("KC2").Value Or _ .Cells(1, y).Value > .Range("KD2").Value Or _ .Cells(3, y).Value = 1 _ And .Cells(1, y).Value >= .Range("KC2").Value _ And .Cells(1, y).Value <= .Range("KD2").Value _ Then .Cells(2, y).Value = 0 Else .Cells(2, y).Value = 1 End If Next .Range("BQA2").Value = WorksheetFunction.Sum(.Range("BQC2:DTL2")) If .Range("BQA2").Value = 0 Then Call Ende(3, 10, 13, 14, "BPN2", "BPO2") Else: Call Prüfung_2 End If Else: Call Prüfung_2 End If End With End Sub
Dafür wird Auftrag für Auftrag berechnet und verplant.
Der Prozess sieht folgendermaßen aus:
Schritt 1: Prüfung welche Mitarbeiter die benötigte Qualifikation für den Auftrag haben und ob er im Dienstzeitraum liegt, bei Ja wird ein x gesetzt, Nein ""
Schritt 2: Makro wo das Problem auftaucht und gepostet wurde! Schicht für Schicht (130 möglich) wird geprüft ob ein x vorliegt, bei Nein wird die nächste Schicht geprüft, bei Ja erfolgt eine Prüfung ob der Mitarbeiter noch frei ist (JA) oder bereits verplant ist (NEIN). Bei JA wird Schritt 3 aufgerufen, bei NEIN wird die nächste Schicht geprüft.
Schritt 3: Auftragsdaten werden in entsprechende Schicht eingefügt.
Ist der Prozess vollendet wird der nächste Auftrag berechnet und der Prozess beginnt von vorne.
Da es sich um mehrere 100 Aufträge handeln kann, werden die einzelnen Makros demensprechend oft durchlaufen.
Die ersten Minuten läuft alles Problemlos, nach ca. guten 100 Aufträgen taucht das Problem mit der Fehlermeldung auf.
Immer die Kopfzeile von Schritt 2 wird markiert.
Betroffen ist einmal Makro Pürfung_40, dann wieder Prüfung_90, immer unterschiedlich.
If Sheets("Sheet1").Range("BPK3").Value = "x" Then
es passiert zuweilen, dass ein aufgerufenes Makro abstürzt und man sich irgendwo im aufrufenden Makro wiederfindet nebst einer Fehlermeldung. Bei Deinen vielen Aufrufen greifst Du immer wieder auf das Blatt zu, obwohl das bei einigen Adressen und/oder Inhalten nicht nötig wäre und anderes vielleicht durch Übernahme in ein Array verbessert werden könnte.
Zitat:Betroffen ist einmal Makro Pürfung_40, dann wieder Prüfung_90, immer unterschiedlich.
Gepostet hast Du Prüfung_1.
Hier kannst Du die jeweiligen Spaltennummern fest programmieren For y = .Range("BQC1").Column To .Range("DTL1").Column
Ich würde ggf. die Daten in ein Array nehmen und dieses dann durchlaufen.
Hier mal ein paar Code-Gedanken dazu
Code:
Sub tester() Dim iCnt1%, arrDaten 'Startspalte -1 (Grund: Array beginnt hier mit Index 1) Const iCol = 1796 'Bereich in Array uebernehmen arrDaten = Range("BQC1:DTL3").Value 'Schleife ueber alle "Spalten" For iCnt1 = 1 To UBound(arrDaten, 2) 'Wenn Bedingungen erfuellt sind, dann If arrDaten(1, iCnt1) < 1 Or arrDaten(1, iCnt1) > 2 Or arrDaten(3, iCnt1) = 1 _ And arrDaten(1, iCnt1) < 1 Or arrDaten(1, iCnt1) > 2 Then 'In Zeile 3 was eintragen Cells(3, iCol + iCnt1) = "x" 'Alternativ Wenn Bedingungen nicht erfuellt sind, dann Else 'Ende Wenn Bedingungen erfuellt sind, dann End If 'Ende Schleife ueber alle "Spalten" Next End Sub
Zumindest die Werte, die Du mehrfach verwendest, könntest Du in Variable packen
.Range("KC2").Value .Range("KD2").Value eventuell auch .Range("BPK2").Value
Ich hoffe mal, dass hier die Logik stimmt.
.Cells(1, y).Value < .Range("KC2").Value Or _ .Cells(1, y).Value > .Range("KD2").Value Or _ .Cells(3, y).Value = 1 _ And .Cells(1, y).Value >= .Range("KC2").Value _ And .Cells(1, y).Value <= .Range("KD2").Value _
Es muss eine der ersten 3 Bedingungen erfüllt sein sowie die 4. und 5. Ich frag mich aber schon, wozu im Or - auf < KC2 oder > KD2 geprüft wird wenn es doch im And - Zweig >= KC2 und <= KD2 sein muss.
. \\\|/// Hoffe, geholfen zu haben. ( ô ô ) Grüße, André aus G in T ooO-(_)-Ooo (Excel 97-2019+365)
05.12.2020, 19:30 (Dieser Beitrag wurde zuletzt bearbeitet: 05.12.2020, 19:30 von karlk.)
Hallo,
Danke für die Tipps.
Die erste If Formel habe ich mittlerweile ausgegliedert und sieht jetzt folgendermaßen aus:
Code:
Sub Prüfung_1()
With Worksheets("Sheet1")
If .Range("BPK2").Value = "x" _ Then Call Prüfung_Schicht_1 ElseIf .Range("BPK3").Value = "x" _ Then Call Prüfung_Schicht_2 ElseIf .Range("BPK4").Value = "x" _ Then Call Prüfung_Schicht_3 ElseIf .Range("BPK5").Value = "x" _ Then Call Prüfung_Schicht_4 ElseIf .Range("BPK6").Value = "x" _ Then Call Prüfung_Schicht_5 ElseIf .Range("BPK7").Value = "x" _ Then Call Prüfung_Schicht_6 ElseIf .Range("BPK8").Value = "x" _ Then Call Prüfung_Schicht_7 ElseIf .Range("BPK9").Value = "x" _ Then Call Prüfung_Schicht_8 ElseIf .Range("BPK10").Value = "x" _ Then Call Prüfung_Schicht_9 ElseIf .Range("BPK11").Value = "x" _ Then Call Prüfung_Schicht_10 ElseIf .Range("BPK12").Value = "x" _ Then Call Prüfung_Schicht_11 ElseIf .Range("BPK13").Value = "x" _ Then Call Prüfung_Schicht_12 ElseIf .Range("BPK14").Value = "x" _ Then Call Prüfung_Schicht_13 ElseIf .Range("BPK15").Value = "x" _ Then Call Prüfung_Schicht_14 ElseIf .Range("BPK16").Value = "x" _ Then Call Prüfung_Schicht_15 ElseIf .Range("BPK17").Value = "x" _ Then Call Prüfung_Schicht_16 ElseIf .Range("BPK18").Value = "x" _ Then Call Prüfung_Schicht_17 ElseIf .Range("BPK19").Value = "x" _ Then Call Prüfung_Schicht_18 ElseIf .Range("BPK20").Value = "x" _ Then Call Prüfung_Schicht_19 ElseIf .Range("BPK21").Value = "x" _ Then Call Prüfung_Schicht_20 ElseIf .Range("BPK22").Value = "x" _ Then Call Prüfung_Schicht_21 ElseIf .Range("BPK23").Value = "x" _ Then Call Prüfung_Schicht_22 ElseIf .Range("BPK24").Value = "x" _ Then Call Prüfung_Schicht_23 ElseIf .Range("BPK25").Value = "x" _ Then Call Prüfung_Schicht_24 ElseIf .Range("BPK26").Value = "x" _ Then Call Prüfung_Schicht_25 ElseIf .Range("BPK27").Value = "x" _ Then Call Prüfung_Schicht_26 ElseIf .Range("BPK28").Value = "x" _ Then Call Prüfung_Schicht_27 ElseIf .Range("BPK29").Value = "x" _ Then Call Prüfung_Schicht_28 ElseIf .Range("BPK30").Value = "x" _ Then Call Prüfung_Schicht_29 ElseIf .Range("BPK31").Value = "x" _ Then Call Prüfung_Schicht_30 ElseIf .Range("BPK32").Value = "x" _ Then Call Prüfung_Schicht_31 ElseIf .Range("BPK33").Value = "x" _ Then Call Prüfung_Schicht_32 ElseIf .Range("BPK34").Value = "x" _ Then Call Prüfung_Schicht_33 ElseIf .Range("BPK35").Value = "x" _ Then Call Prüfung_Schicht_34 ElseIf .Range("BPK36").Value = "x" _ Then Call Prüfung_Schicht_35 ElseIf .Range("BPK37").Value = "x" _ Then Call Prüfung_Schicht_36 ElseIf .Range("BPK38").Value = "x" _ Then Call Prüfung_Schicht_37 ElseIf .Range("BPK39").Value = "x" _ Then Call Prüfung_Schicht_38 ElseIf .Range("BPK40").Value = "x" _ Then Call Prüfung_Schicht_39 ElseIf .Range("BPK41").Value = "x" _ Then Call Prüfung_Schicht_40 ElseIf .Range("BPK42").Value = "x" _ Then Call Prüfung_Schicht_41 ElseIf .Range("BPK43").Value = "x" _ Then Call Prüfung_Schicht_42 ElseIf .Range("BPK44").Value = "x" _ Then Call Prüfung_Schicht_43 ElseIf .Range("BPK45").Value = "x" _ Then Call Prüfung_Schicht_44 ElseIf .Range("BPK46").Value = "x" _ Then Call Prüfung_Schicht_45 ElseIf .Range("BPK47").Value = "x" _ Then Call Prüfung_Schicht_46 ElseIf .Range("BPK48").Value = "x" _ Then Call Prüfung_Schicht_47 ElseIf .Range("BPK49").Value = "x" _ Then Call Prüfung_Schicht_48 ElseIf .Range("BPK50").Value = "x" _ Then Call Prüfung_Schicht_49 ElseIf .Range("BPK51").Value = "x" _ Then Call Prüfung_Schicht_50 Else: Call Prüfung_2 End If
End With
End Sub
Beim Durchlauf bleibt das Makro jetzt immer nach 7min an der ersten Stelle stehen.
Wäre hier ein Array besser um es zu verhindern?
Das andere Makro war falsch geschrieben, richtig sollte es folgendermaßen lauten:
Code:
For s2 = .Range("BQC1").Column To .Range("DTL1").Column If .Cells(3, s2).Value = 1 _ And .Cells(1, s2).Value >= .Range("KC2").Value _ And .Cells(1, s2).Value <= .Range("KD2").Value _ Then .Cells(2, s2).Value = 0 ElseIf .Cells(1, s2).Value > .Range("KD2").Value _ Then .Cells(2, s2).Value = 0 ElseIf .Cells(1, s2).Value < .Range("KC2").Value _ Then .Cells(2, s2).Value = 0 Else .Cells(2, s2).Value = 1 End If Next
wenn es immer bei Call Prüfung_Schicht_1 stecken bleibt dann mache mal ans Ende von Prüfung_Schicht_1 einen Haltepunkt. Dann siehst Du erst mal, ob es wirklich durchläuft ...
. \\\|/// Hoffe, geholfen zu haben. ( ô ô ) Grüße, André aus G in T ooO-(_)-Ooo (Excel 97-2019+365)
Anbei eine Beispieldatei mit allen relevanten Daten, in diesen Bereich kommt es zum Laufzeitfehler. Kurze Erklärung zu den Makros: Berechnung Prüfung - Hier wird geprüft ob der Mitarbeiter die benötigte Qualifikation für den Auftrag besitzt und zum benötigten Zeitpunkt im Dienst befindet. Prüfung 1 und Prüfung 2 - Das System weiß jetzt, für welche Mitarbeiter der Auftrag in Frage kommt (x). Für Mitarbeiter welche nicht in Frage kommen, soll keine weitere Berechnung statt finden. Prüfung 2 ist immer im Dienst und hat alle benötigten Qualifikationen, hier landen Aufträge wenn kein Mitarbeiter verfügbar ist. Prüfung S1 bis Prüfung S10 und Prüfung O1 bis Prüfung O10 - Fällt der letzte Schritt positiv aus, wird jetzt geprüft ob der Mitarbeiter im Zeitraum des Auftrages noch verfügbar ist oder bereits mit einen anderen Auftrag belegt ist. Fällt die Berechnung positiv aus, geht die Berechnung zum Makro verplanen weiter, fällt sie negativ aus geht es zurück zur Prüfung und der nächste mögliche Mitarbeiter wird geprüft. Um zu verhinden, den gleichen Mitarbeiter nochmals zu prüfen, wird das x entfernt. Verplanen_S und Verplanen_O - Ergibt die letzte Berechnung einen Treffer, werden jetzt diverse Daten des Auftrages in das Feld des Dienstes verplant. Nach der Verplanung folgt eine Neuberechnung des Dienstes, um für den nächsten Auftrag zu wissen wann der Mitarbeiter verfügbar ist und wann nicht. Es erfolgt eine minütliche Überprüfung. Ende - Zuerst wird der nächste Auftrag in Zeile 2 kopiert, kopieren wird schneller ausgeführt als löschen und hochziehen. Danach wird der Wert, welcher die Anzahl der Aufträge anzeigt, um -1 reduziert. Wiederholdung - Hier erkennt das System ob weitere Aufträge berechnet werden müssen, oder ob die Endberechnung ausgeführt werden. Falls Ja, wird erneut das Makro Berechnung Prüfung gestartet. Funktioniert dieser Ablauf für mehrere 100 Aufträge ohne Laufzeitfehler, wäre das Problem gelöst. Das folgende Beispiel ist mit 13 Diensten ausgelegt, in der richtigen Datei wird mit bis zu 150 Diensten geplant.
Evtl. lässt sich durch umschreiben der Makros und Reihenfolge das Problem ebenfalls lösen?
Laufzeitfehler 80010108
![[-]](https://www.clever-excel-forum.de/images/collapse.png)