ich habe da ein code zusammengestellt, jedoch dauert diese sehr lange, meine frage nun ob und wie ich diesen code ändern sollte damit es schneller abläuft?
der code:
Sub selo_berechnung()
Worksheets("cl_berechnungdiagrammversch").Activate
With ActiveSheet
Dim LoLetzte As Long
Dim LoI As Long
LoLetzte = IIf(IsEmpty(Cells(Rows.Count, 2)), Cells(Rows.Count, 2).End(xlUp).Row, Rows.Count)
UserForm8.ProgressBar1.Min = 1
UserForm8.ProgressBar1.Max = LoLetzte
UserForm8.Show
For LoI = 2 To LoLetzte
Cells(8, 16) = Worksheets("tabelle3").Cells(1, 7)
Cells(9, 17).FormulaLocal = "=p8*p7"
Cells(9, 16).FormulaLocal = "=tabelle3!$g$2"
Cells(10, 16).FormulaLocal = "=7"
Cells(10, 17).FormulaLocal = "=p9*p10"
Cells(11, 18).FormulaLocal = "=q11-10"
Cells(12, 18).FormulaLocal = "=q12+10"
Cells(11, 17).FormulaLocal = "=KKLEINSTE(c2:c" & LoLetzte & ";1)"
Cells(LoI, 5).FormulaLocal = "=WENN(d" & LoI & "<HEUTE();0;d" & LoI & "-c" & LoI & "-f" & LoI & ")"
Cells(LoI, 6).FormulaLocal = "=WENN((c" & LoI & ">HEUTE())*(d" & LoI & ">HEUTE());0;HEUTE()-c" & LoI & ")"
Cells(LoI, 7).FormulaLocal = "=d" & LoI & "-c" & LoI & ""
Cells(LoI, 7).NumberFormat = "General"
Cells(LoI, 8).FormulaLocal = "=$q$10*g" & LoI
Cells(LoI, 9).FormulaLocal = "=WENN(g" & LoI & "=0;0;h" & LoI & "/g" & LoI & "/$p$9)"
Cells(LoI, 10).FormulaLocal = "=DATUM(JAHR(KKLEINSTE(C1:C" & LoLetzte & ";1) );1;1)"
Cells(LoI, 10).NumberFormat = "dd/mm/yy"
Cells(LoI, 11).FormulaLocal = "=c" & LoI & "-j" & LoI
Cells(LoI, 12).FormulaLocal = "=d" & LoI & "-c" & LoI
Cells(LoI, 12).NumberFormat = "General"
Cells(LoI, 13).FormulaLocal = "=j" & LoI & "+k" & LoI & "+l" & LoI
Cells(LoI, 13).NumberFormat = "dd/mm/yy"
Cells(LoI, 15).FormulaLocal = "=g" & LoI & "+1"
Cells(1, 19).FormulaLocal = "start"
Cells(LoI, 19).FormulaLocal = "=c" & LoI
Cells(1, 21).FormulaLocal = "ende"
Cells(LoI, 21).FormulaLocal = "=d" & LoI
Cells(1, 20).FormulaLocal = "dauer"
Cells(LoI, 20).FormulaLocal = "=u" & LoI & "-s" & LoI & "+1"
Cells(LoI, 20).NumberFormat = "General"
Cells(1, 22).FormulaLocal = "heute"
Cells(LoI, 22).FormulaLocal = "=heute()"
Cells(1, 23).FormulaLocal = "abgelaufende Tage"
Cells(LoI, 23).FormulaLocal = "=WENN(s" & LoI & ">v" & LoI & ";0;(WENN(v" & LoI & ">(t" & LoI & "+s" & LoI & ");t" & LoI & ";(v" & LoI & "-s" & LoI & "))))"
Cells(LoI, 23).NumberFormat = "General"
Cells(1, 24).FormulaLocal = "restdauer"
Cells(LoI, 24).FormulaLocal = "=t" & LoI & "-w" & LoI & "-ad" & LoI & ""
Cells(LoI, 24).NumberFormat = "General"
Cells(1, 25).FormulaLocal = "start vor heute"
Cells(LoI, 25).FormulaLocal = "=wenn(HEUTE()<s" & LoI & ";v" & LoI & ";s" & LoI & ")"
Cells(LoI, 25).NumberFormat = "dd/mm/yy"
Cells(1, 26).FormulaLocal = "start nach heute"
Cells(LoI, 26).FormulaLocal = "=s" & LoI & "-y" & LoI & "-ab" & LoI
Cells(1, 27).FormulaLocal = "zeit nach ende"
Cells(LoI, 27).FormulaLocal = "=wenn(HEUTE()>u" & LoI & ";v" & LoI & "-u" & LoI & "-1 ;0)"
Cells(1, 28).FormulaLocal = "heute vor"
Cells(LoI, 28).FormulaLocal = "=wenn(s" & LoI & "-y" & LoI & ">1;1;0)"
Cells(1, 29).FormulaLocal = "heute nach"
Cells(LoI, 29).FormulaLocal = "=wenn(heute()>u" & LoI & ";1;0)"
Cells(1, 30).FormulaLocal = "heute während"
Cells(LoI, 30).FormulaLocal = "=wenn((summe(ab" & LoI & " ;ac" & LoI & "))=0;1;0)"
Range(Cells(LoI, 19), Cells(LoI, 30)).HorizontalAlignment = xlCenter
Range(Cells(LoI, 19), Cells(LoI, 30)).Font.ColorIndex = 4
Range(Cells(LoI, 5), Cells(LoI, 13)).HorizontalAlignment = xlCenter
Range(Cells(LoI, 5), Cells(LoI, 13)).Font.ColorIndex = 3
UserForm8.ProgressBar1.Value = UserForm8.ProgressBar1.Value + 1
Next LoI
Cells(7, 16).FormulaLocal = "=WENN(Arbeitstage!J7=WAHR;5;(WENN(Arbeitstage!K7=WAHR;6;7)))"
Cells(12, 17).FormulaLocal = "=max(d2:d" & LoLetzte & ";1)"
End With
Unload UserForm8
'Worksheets("cl_berechnungdiagrammversch").berechnung_derarbeitstage
End Sub