Es sind immer genau 60 oder 30. Ich habe 3 Programme, da laufen die Datenblätter paralel. Also brauch ich immer 3 Mappen, wo 60 Spalten drin sind, weil die Programme diese 60 Spalten abarbeiten. In einer Mappe sind immer 60*8000= 480.000 Zufallszahlen drin ( von Hand ermittelt, also kein Zufallsgenerator ! )
Ist ein Programm fertig, dann werden wieder neue 60 neue Spalten generiert.
dies ist das Makro 1
Sub Ro_26_R_27()
' 26+27 Makro
Workbooks.Open Filename:="G:\Roulette\Datenblätter\db26.csv"
Windows("db26.csv").Activate
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array( _
13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), _
Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, _
1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), _
Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, _
1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), _
Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, _
1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), _
Array( _
59, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array( _
13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), _
Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, _
1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), _
Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, _
1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), _
Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, _
1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), _
Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, _
1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), _
Array( _
72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, _
1), _
Array(79, 1), Array(80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), _
Array( _
85, 1), Array(86, 1), Array(87, 1), Array(88, 1), Array(89, 1)), TrailingMinusNumbers _
:=True
Application.Run "PERSONAL.xlsb!format"
If Application.WorksheetFunction.CountA(Range("A1:BH1")) 60 Then Exit Sub
'Ende blatt 26
Workbooks.Open Filename:="G:\Roulette\Datenblätter\db27.csv"
Windows("db27.csv").Activate
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array( _
13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), _
Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, _
1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), _
Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, _
1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), _
Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, _
1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), _
Array( _
59, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array( _
13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), _
Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, _
1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), _
Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, _
1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), _
Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, _
1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), _
Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, _
1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), _
Array( _
72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, _
1), _
Array(79, 1), Array(80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), _
Array( _
85, 1), Array(86, 1), Array(87, 1), Array(88, 1), Array(89, 1)), TrailingMinusNumbers _
:=True
Application.Run "PERSONAL.xlsb!format"
Windows("db27.csv").Activate
Windows("db26.csv").Activate
Application.Run "PERSONAL.xlsb!Modul21.DreisigSpaltenSollstDuWaehlen"
Selection.copy
Windows("db27.csv").Activate
Range("BH1").Select
ActiveSheet.Paste
Application.Run "PERSONAL.xlsb!einfuegen"
Application.Run "PERSONAL.xlsb!spaltenvergleich"
Windows("db26.csv").Activate
Application.Run "PERSONAL.xlsb!Modul3.löschen"
Application.Run "PERSONAL.xlsb!format"
End Sub
Sub Ro_27_R_28()
' 26+27 Makro
Workbooks.Open Filename:="G:\Roulette\Datenblätter\db27.csv"
Windows("db27.csv").Activate
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array( _
13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), _
Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, _
1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), _
Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, _
1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), _
Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, _
1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), _
Array( _
59, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array( _
13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), _
Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, _
1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), _
Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, _
1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), _
Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, _
1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), _
Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, _
1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), _
Array( _
72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, _
1), _
Array(79, 1), Array(80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), _
Array( _
85, 1), Array(86, 1), Array(87, 1), Array(88, 1), Array(89, 1)), TrailingMinusNumbers _
:=True
Application.Run "PERSONAL.xlsb!format"
If Application.WorksheetFunction.CountA(Range("A1:BH1")) 60 Then Exit Sub
'Ende blatt 27
Workbooks.Open Filename:="G:\Roulette\Datenblätter\db28.csv"
Windows("db28.csv").Activate
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array( _
13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), _
Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, _
1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), _
Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, _
1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), _
Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, _
1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), _
Array( _
59, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array( _
13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), _
Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, _
1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), _
Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, _
1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), _
Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, _
1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), _
Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, _
1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), _
Array( _
72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, _
1), _
Array(79, 1), Array(80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), _
Array( _
85, 1), Array(86, 1), Array(87, 1), Array(88, 1), Array(89, 1)), TrailingMinusNumbers _
:=True
Application.Run "PERSONAL.xlsb!format"
Windows("db28.csv").Activate
Windows("db27.csv").Activate
Application.Run "PERSONAL.xlsb!Modul21.DreisigSpaltenSollstDuWaehlen"
Selection.copy
Windows("db28.csv").Activate
Range("BH1").Select
ActiveSheet.Paste
Application.Run "PERSONAL.xlsb!einfuegen"
Application.Run "PERSONAL.xlsb!spaltenvergleich"
Windows("db27.csv").Activate
Application.Run "PERSONAL.xlsb!Modul3.löschen"
Application.Run "PERSONAL.xlsb!format"
End Sub
dies ist das "Format Makro
Sub format()
' format Makro
' Makro am 10.04.2008 von darlee aufgezeichnet
' Tastenkombination: Strg+f
Cells.Select
Selection.Columns.AutoFit
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.ColumnWidth = 2.86
Range("A1").Select
End Sub
dies hier das Makro welches 30 Spalten aus den 60 auswählt.
Sub DreisigSpaltenSollstDuWaehlen()
Dim varColumns() As Integer
Dim intIndex As Integer, intRnd As Integer
Dim rngCol As Range
'Array dimensionieren
ReDim varColumns(58)
'Array mit den Zahlen von 2 bis 60 füllen
For intIndex = 0 To UBound(varColumns)
varColumns(intIndex) = intIndex + 2
Next
'Zufallsgeneratot 'anstossen!
Randomize
For intIndex = 1 To 30
'Zufällig eine Zahl zwischen 0 und Obergrenze des Arrays wählen
intRnd = Int(Rnd() * UBound(varColumns))
'Die Zufällig gewählte Spalte der Range-Variablen zuwisen
If rngCol Is Nothing Then
Set rngCol = Columns(varColumns(intRnd))
Else
Set rngCol = Union(rngCol, Columns(varColumns(intRnd)))
End If
'Das letzte Element des Arrays in die eben gewählte Position schreiben
'und das Array um das letzte Element kürzen. (damit keine Zahl doppelt gewählt wird!)
varColumns(intRnd) = varColumns(UBound(varColumns))
ReDim Preserve varColumns(UBound(varColumns) - 1)
Next
'Ausgewählte Spalten markieren
rngCol.Select
End Sub
dies hier fügt die ausgewählten 30 Spalten in die freien Spalten ein
Sub einfuegen()
Application.ScreenUpdating = False
Columns("BI:BI").Cut Destination:=Range("AF1")
Columns("BK:BK").Cut Destination:=Range("AD1")
Columns("BL:BL").Cut Destination:=Range("AH1")
Columns("BJ:BJ").Cut Destination:=Range("AB1")
Columns("CK:CK").Cut Destination:=Range("AJ1")
Columns("CJ:CJ").Cut Destination:=Range("BF1")
Columns("CI:CI").Cut Destination:=Range("BD1")
Columns("CH:CH").Cut Destination:=Range("BB1")
Columns("CG:CG").Cut Destination:=Range("AZ1")
Columns("CF:CF").Cut Destination:=Range("AX1")
Columns("CE:CE").Cut Destination:=Range("AV1")
Columns("CD:CD").Cut Destination:=Range("AT1")
Columns("CC:CC").Cut Destination:=Range("AR1")
Columns("CB:CB").Cut Destination:=Range("AP1")
Columns("CA:CA").Cut Destination:=Range("AN1")
Columns("BZ:BZ").Cut Destination:=Range("AL1")
Columns("BY:BY").Cut Destination:=Range("Z1")
Columns("BX:BX").Cut Destination:=Range("X1")
Columns("BW:BW").Cut Destination:=Range("V1")
Columns("BV:BV").Cut Destination:=Range("T1")
Columns("BU:BU").Cut Destination:=Range("R1")
Columns("BT:BT").Cut Destination:=Range("P1")
Columns("BS:BS").Cut Destination:=Range("N1")
Columns("BR:BR").Cut Destination:=Range("L1")
Columns("BM:BM").Cut Destination:=Range("J1")
Columns("BQ:BQ").Cut Destination:=Range("H1")
Columns("BP:BP").Cut Destination:=Range("F1")
Columns("BO:BO").Cut Destination:=Range("D1")
Columns("BN:BN").Cut Destination:=Range("B1")
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub
Um Fehler zu vermeiden , doppelte gleiche Spalten wären schlecht, kommt das hier zum Einsatz
Sub SpaltenVergleich()
Dim Spalte1 As Long, Spalte2 As Long, Spalten As Long
Dim Zeile1 As Long, Zeile2 As Long
Zeile1 = 1
Zeile2 = 100
Spalten = 60
Application.Calculation = xlCalculationManual
With Cells(1, Spalten + 1)
For Spalte1 = 1 To Spalten - 1
For Spalte2 = Spalte1 + 1 To Spalten
.FormulaR1C1 = "=SUMPRODUCT((R" & Zeile1 & "C" & Spalte1 _
& ":R" & Zeile2 & "C" & Spalte1 & " R" & Zeile1 & "C" & Spalte2 & ":R" _
& Zeile2 & "C" & Spalte2 & ")*1)"
.Calculate
If .Value = 0 Then
If MsgBox("Spalte " & Spalte1 & " und " & Spalte2 & " sind identisch", _
vbInformation + vbRetryCancel, "Spaltenvergleich") = vbCancel Then GoTo Beenden
End If
Next
Next
Beenden:
.ClearContents
End With
Application.Calculation = xlCalculationAutomatic
End Sub
Zum guten Schluss, soll der mir die ausgewählten 30 Spalten löschen, weil diese ja dann in der neuen Mappe sind und aus der alten raus müssen
Sub spielblatt_loeschen()
' spielblatt_loeschen Makro
' löscht die falschen Einträge pair impair
Range("J4").Select
Selection.ClearContents
Range("K6").Select
Selection.ClearContents
Range("J4:K4").Select
Selection.ClearContents
Range("J6:K6").Select
Selection.ClearContents
Range("J8:K8").Select
Selection.ClearContents
Range("J10:K10").Select
Selection.ClearContents
Range("J12:K12").Select
Selection.ClearContents
Range("J14:K14").Select
Selection.ClearContents
Range("J16:K16").Select
Selection.ClearContents
Range("J18:K18").Select
Selection.ClearContents
Range("J20:K20").Select
Selection.ClearContents
Range("J22:K22").Select
Selection.ClearContents
Range("J24:K24").Select
Selection.ClearContents
Range("J26:K26").Select
Selection.ClearContents
Range("J28:K28").Select
Selection.ClearContents
Range("J30:K30").Select
Selection.ClearContents
Range("J32:K32").Select
Selection.ClearContents
Range("J34:K34").Select
Selection.ClearContents
Range("J36:K36").Select
Selection.ClearContents
Range("J38:K38").Select
Selection.ClearContents
Range("J40:K40").Select
Selection.ClearContents
Range("J42:K42").Select
Selection.ClearContents
Range("J44:K44").Select
Selection.ClearContents
Range("J46:K46").Select
Selection.ClearContents
Range("J48:K48").Select
Selection.ClearContents
Range("J50:K50").Select
Selection.ClearContents
Range("J52:K52").Select
Selection.ClearContents
Range("J54:K54").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=27
Range("J56:K56").Select
Selection.ClearContents
Range("J58:K58").Select
Selection.ClearContents
Range("J60:K60").Select
Selection.ClearContents
Range("J62:K62").Select
Selection.ClearContents
Range("J64:K64").Select
Selection.ClearContents
Range("J66:K66").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-154
Range("P3:Q3").Select
Selection.ClearContents
Range("P5:Q5").Select
Selection.ClearContents
Range("P7:Q7").Select
Selection.ClearContents
Range("P9:Q9").Select
Selection.ClearContents
Range("P11:Q11").Select
Selection.ClearContents
Range("P13:Q13").Select
Selection.ClearContents
Range("P15:Q15").Select
Selection.ClearContents
Range("P17:Q17").Select
Selection.ClearContents
Range("P19:Q19").Select
Selection.ClearContents
Range("P21:Q21").Select
Selection.ClearContents
Range("P23:Q23").Select
Selection.ClearContents
Range("P25:Q25").Select
Selection.ClearContents
Range("P27:Q27").Select
Selection.ClearContents
Range("P29:Q29").Select
Selection.ClearContents
Range("P31:Q31").Select
Selection.ClearContents
Range("P33:Q33").Select
Selection.ClearContents
Range("P35:Q35").Select
Selection.ClearContents
Range("P37:Q37").Select
Selection.ClearContents
Range("P39:Q39").Select
Selection.ClearContents
Range("P41:Q41").Select
Selection.ClearContents
Range("P43:Q43").Select
Selection.ClearContents
Range("P45:Q45").Select
Selection.ClearContents
Range("P47:Q47").Select
Selection.ClearContents
Range("P49:Q49").Select
Selection.ClearContents
Range("P51:Q51").Select
Selection.ClearContents
Range("P53:Q53").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=39
Range("P55:Q55").Select
Selection.ClearContents
Range("P57:Q57").Select
Selection.ClearContents
Range("P59:Q59").Select
Selection.ClearContents
Range("P61:Q61").Select
Selection.ClearContents
Range("P63:Q63").Select
Selection.ClearContents
Range("P65:Q65").Select
Selection.ClearContents
End Sub
Auf diese Art und Weise hab ich immer "neue" 480.000 Zufallszahlen , die sich nie wiederholen können, dafür lebe ich nicht lange genug :-)
übrigens funzt das auf excel 2010 mit 64 bit gar nicht gut. Es dauert länger als 15 sec, nachdem das Makro abgearbeitet ist. Der ganze PC ist blockiert. ( Hab nen Phenon Prozessor 3,2 GHZ vier Kerne) Und die Ausführung des Makros dauert auch ewig. Deswegen hab ich excel 2010 wieder deinstalliert und arbeite wieder mit excel 2007.
Gruss Udo