AW: Probleme mit Schleife und Datentransfer
13.08.2007 10:02:00
Nils
Na gut, aber es sind halt nur aufgenommene Macros hintereinander gestellt. Laie halt ;)
Sub Zufallszahlen1()
' Überschriften Makro
Range("D1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "Datum"
Range("E1").Select
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "MSCI"
Range("F1").Select
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "CGBI"
Range("G1").Select
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "Berechnung"
Range("H1").Select
' DatumZukunft Makro
Range("D3").Select
ActiveCell.FormulaR1C1 = "1/15/2008"
Range("D4").Select
ActiveCell.FormulaR1C1 = "2/15/2008"
Range("D3:D4").Select
Selection.AutoFill Destination:=Range("D3:D502"), Type:=xlFillDefault
Range("D3:D502").Select
' Zufallszahlen Makro
Application.Run "ATPVBAEN.XLA!Sample", ActiveSheet.Range("$B$3:$B$146"), Range("$E$3:$E$146"), " _
R", 500, False
' SVerweis Makro
Range("F3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R3C2:R146C3,2,FALSE)"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F502"), Type:=xlFillDefault
Range("F3:F502").Select
' BerechnungohneKostenMSCIinklFörd Makro
Range("G3").Select
ActiveCell.FormulaR1C1 = "=84.12*(RC[-2]/100+1)"
Range("G4").Select
ActiveCell.FormulaR1C1 = "=(84.12+R[-1]C)*(RC[-2]/100+1)"
Range("G4").Select
Selection.AutoFill Destination:=Range("G4:G19"), Type:=xlFillDefault
Range("G4:G19").Select
Range("G19").Select
ActiveCell.FormulaR1C1 = "=(84.12+R[-1]C+154)*(RC[-2]/100+1)"
Range("G8:G19").Select
Selection.AutoFill Destination:=Range("G8:G470"), Type:=xlFillDefault
Range("G8:G470").Select
End Sub
Die Wiederholung sollte vor dem "Zufallszahlenziehen" kommen und hier am Ende sollten dann alle Werte in eine neue Zeile jeweils aussortiert werden, d.h. bei 500 Versuchen alle 500 Zahlen extra.
Gruß und danke Nils