Re: Excel-Absturz
17.01.2003 06:55:09
Heinz A. Wankmüller
Hallo Martin,nach schrittweiser Ausführung stürzt er bei der Umwandlung der Formeln in Werte um, also in dem With-Konstrukt:
Cells(ez + 1, 8).FormulaArray = _
"=ROUND(SUM((LEFT('D4'!R2C6:R5000C6,3)=RC1)*('D4'!R2C15:R5000C15=RC2)*('D4'!R2C22:R5000C22>=VALUE(TEXT(TODAY(),""JJJJMMTT"")))),2)"
Cells(ez + 2, 8).FormulaArray = _
"=ROUND(SUM((LEFT('D4'!R2C6:R5000C6,2)=RC1)*('D4'!R2C15:R5000C15=RC2)*('D4'!R2C22:R5000C22>=VALUE(TEXT(TODAY(),""JJJJMMTT"")))),2)"
Cells(ez + 5, 8).FormulaArray = _
"=ROUND(SUM((LEFT('D4'!R2C6:R5000C6,3)=RC1)*('D4'!R2C15:R5000C15=RC2)*('D4'!R2C22:R5000C22>=VALUE(TEXT(TODAY(),""JJJJMMTT"")))),2)"
Cells(ez + 6, 8).FormulaArray = _
"=ROUND(SUM((LEFT('D4'!R2C6:R5000C6,4)=RC1)*('D4'!R2C15:R5000C15=RC2)*('D4'!R2C22:R5000C22>=VALUE(TEXT(TODAY(),""JJJJMMTT"")))),2)"
Cells(ez + 8, 8).FormulaArray = _
"=ROUND(SUM((LEFT('D4'!R2C9:R5000C9,2)=LEFT(RC3,2))*('D4'!R2C22:R5000C22>=VALUE(TEXT(TODAY(),""JJJJMMTT"")))),2)"
Cells(ez + 9, 8).FormulaArray = _
"=SUM((ISERROR(VALUE('D4'!R2C6:R5000C6)))*('D4'!R2C15:R5000C15&'D4'!R2C16:R5000C16=RC2)*('D4'!R2C22:R5000C22>=VALUE(TEXT(TODAY(),""JJJJMMTT""))))"
Range(Cells(ez + 9, 7), Cells(ez + 9, 8)).Copy
Range(Cells(ez + 10, 7), Cells(ez + 13, 8)).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False With Range(Cells(ez + 1, 5), Cells(ez + 2, 8))
.Copy
.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End With
Code eingefügt mit Syntaxhighlighter 1.16
Gruß
Heinz