Neulich gerade ...
22.12.2017 19:49:49
lupo1
http://www.clever-excel-forum.de/thread-13029.html
Sub AuftragsdauerVonBis() 'Konstruktionsmakro; erfordert xl2007+ wegen WENNFEHLER
Workbooks.Add xlWorksheet: ActiveSheet.Name = "T": [A2:C2] = Split("lfdNr von bis")
w = InputBox("Geben Sie eine ganze Zahl ab 2 ein! (Anzahl berührter Wochen)") * 7
[D:G].NumberFormat = "[h]:mm": [F:F].NumberFormat = "MM/DD/YYYY"
[B3] = "11/23/2017 11:10": [C3] = "11/28/2017 14:45" '2 Feiertage enth.
[B4] = "11/30/2017 11:10": [C4] = "12/05/2017 14:45"
[B5] = "11/27/2017 05:59": [C5] = "12/01/2017 12:31" '1 Feiertag enth.
'Datumsformat muss andersrum (dt. statt engl.) definiert sein bei vorangestelltem Wochentag:
[B:C].NumberFormat = "DDD DD/MM/YYYY hh:mm": Rows("2:" & w + 1).EntireRow.Insert
'Umweg über defN aufgrund des alten Arrayformel-Erstellungslimits 256 Zeichen in VBA:
ActiveWorkbook.Names.Add Name:="WT", RefersToR1C1:= _
Replace("=SUM(IFERROR(EXP(LN(R1C[1]:R#C[1]-" & _
"IFERROR(EXP(LN(R1C[1]:R#C[1]-MOD(RC3-TRUNC(TRUNC(RC2)/7)*7,#))),)-R1C:R#C-" & _
"IFERROR(EXP(LN(MOD(RC2-TRUNC(TRUNC(RC2)/7)*7,#)-R1C:R#C)),))),))", "#", w)
ActiveWorkbook.Names.Add Name:="FT", RefersToR1C1:= _
"=SUM((ROW(INDIRECT(TRUNC(RC2)&"":""&TRUNC(RC3)))" & _
"=TRANSPOSE(R1C6:R14C6))*TRANSPOSE(R1C7:R14C7))"
[D1] = "54:": Range("D2:D" & w) = "=R[-1]C+1"
'Ohne explizites ".FormulaR1C1" muss im xl12-Modell "+R[0]C4" statt "+RC4" referenziert werden:
Range("E1:E" & w) = "=LOOKUP(MOD(ROW()+1,7),{0,2,6},{0,10,6.5})/24+R[0]C4"
'Fr 24.11. und Mo 27.11. sind demonstrationshalber FT, so dass aus 30:05 nur 13:35 Std werden:
[F1] = "1/1/17": [F2] = "5/1/17": [F3] = "10/3/17": [F4] = "11/24/17": [F5] = "11/27/17"
[G1:G10] = "=INDEX(R1C[-2]:R7C[-2],MOD(RC[-1]-2,7)+1)-INDEX(R1C[-3]:R7C[-3],MOD(RC[-1]-2,7)+1)"
Range("2:" & w - 2).EntireRow.Hidden = True: [B:G].Columns.AutoFit
'Select wegen FreezePanes. Außerdem: Keine Arrayformel nötig, da defN diese automatisch _
erstellen:
Range("D" & w + 3).Select: ActiveWindow.FreezePanes = True: Selection.Resize(3, 1) = "=WT-FT"
End Sub
Du könntest nun den Zielwert in C mit einer Schleife annähern, die sich immer mehr dem Wert von D annähert. Damit hättest Du die Umformulierung des Problems auch erreicht.