Frage zu Code
30.08.2015 16:17:30
Frank
Ich habe mir folgenen Code zusammen geschustert:
For i = 2 To 19
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("C1") Then
Sheets("Platzierungen").Cells(i, 3) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 21 To 38
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("D1") Then
Sheets("Platzierungen").Cells(i - 19, 4) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 40 To 57
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("E1") Then
Sheets("Platzierungen").Cells(i - 38, 5) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 59 To 76
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("F1") Then
Sheets("Platzierungen").Cells(i - 57, 6) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 78 To 95
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("G1") Then
Sheets("Platzierungen").Cells(i - 76, 7) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 97 To 114
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("H1") Then
Sheets("Platzierungen").Cells(i - 95, 8) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 116 To 133
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("I1") Then
Sheets("Platzierungen").Cells(i - 114, 9) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 135 To 152
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("J1") Then
Sheets("Platzierungen").Cells(i - 133, 10) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 154 To 171
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("K1") Then
Sheets("Platzierungen").Cells(i - 152, 11) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 173 To 190
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("L1") Then
Sheets("Platzierungen").Cells(i - 171, 12) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 192 To 209
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("M1") Then
Sheets("Platzierungen").Cells(i - 190, 13) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 211 To 228
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("N1") Then
Sheets("Platzierungen").Cells(i - 209, 14) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 230 To 247
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("O1") Then
Sheets("Platzierungen").Cells(i - 228, 15) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 249 To 266
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("P1") Then
Sheets("Platzierungen").Cells(i - 247, 16) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 268 To 285
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("Q1") Then
Sheets("Platzierungen").Cells(i - 266, 17) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 287 To 304
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("R1") Then
Sheets("Platzierungen").Cells(i - 285, 18) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 306 To 323
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("S1") Then
Sheets("Platzierungen").Cells(i - 304, 19) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 325 To 342
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("T1") Then
Sheets("Platzierungen").Cells(i - 323, 20) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 344 To 361
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("U1") Then
Sheets("Platzierungen").Cells(i - 342, 21) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 363 To 380
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("V1") Then
Sheets("Platzierungen").Cells(i - 361, 22) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 382 To 399
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("W1") Then
Sheets("Platzierungen").Cells(i - 380, 23) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 401 To 418
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("X1") Then
Sheets("Platzierungen").Cells(i - 399, 24) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 420 To 437
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("Y1") Then
Sheets("Platzierungen").Cells(i - 418, 25) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 439 To 456
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("Z1") Then
Sheets("Platzierungen").Cells(i - 437, 26) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 458 To 475
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("AA1") Then
Sheets("Platzierungen").Cells(i - 456, 27) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 477 To 494
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("AB1") Then
Sheets("Platzierungen").Cells(i - 475, 28) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 496 To 513
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("AC1") Then
Sheets("Platzierungen").Cells(i - 494, 29) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 515 To 532
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("AD1") Then
Sheets("Platzierungen").Cells(i - 513, 30) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 534 To 551
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("AE1") Then
Sheets("Platzierungen").Cells(i - 532, 31) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 553 To 570
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("AF1") Then
Sheets("Platzierungen").Cells(i - 551, 32) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 572 To 589
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("AG1") Then
Sheets("Platzierungen").Cells(i - 570, 33) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 591 To 608
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("AH1") Then
Sheets("Platzierungen").Cells(i - 589, 34) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 610 To 627
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("AI1") Then
Sheets("Platzierungen").Cells(i - 608, 35) = Sheets("Tabellen").Cells(i, 4)
End If
Next
For i = 629 To 646
If Sheets("Platzierungen").Range("A2") = Sheets("Tabellen").Cells(i, 5) And Sheets("Tabellen").Cells(i, 8) = Sheets("Platzierungen").Range("AJ1") Then
Sheets("Platzierungen").Cells(i - 627, 36) = Sheets("Tabellen").Cells(i, 4)
End If
Next
Also der Code bewirkt, dass mir im Blatt Platzierungen immer die Platzierung einer von mir vorher ausgewählten Mannschaft anzeigt.
Kann ich diesen Code nicht enorm verkleinern, ich versuche es schon seit Stunden mehrer For Schleifen miteinander zu verbinden. Gelingt mir aber nicht.
Es wäre schön wenn mir jemand helfen könnte! Danke!
Gruß Frank H.