Hallo Chris,
habe die Lösung gefunden.
Habe in eurem Archiv gesucht und für meine Zwecke umgestaltet.
Sub Test_Anfangzeit_Lehrerzeit()
Dim sollZeitZ1 As String
Dim sollZeitZ2 As String
Dim sollZeitZ3 As String
Dim sollZeitZ4 As String
Dim sollZeitZ5 As String
Dim sollZeitZ6 As String
Dim sollZeitZ7 As String
Dim sollZeitZ8 As String
Dim sollZeitZ9 As String
Dim sollZeitZ10 As String
Dim sollZeitZ11 As String
Dim sollZeitZ12 As String
Dim sollZeitZ13 As String
Dim sollZeitZ14 As String
Dim sollZeitZ15 As String
Dim sollZeitZ16 As String
Dim sollZeitZ17 As String
Dim sollZeitZ18 As String
Dim sollZeitZ19 As String
Dim sollZeitZ20 As String
Dim sollZeitZ21 As String
Dim sollZeitZ22 As String
Dim sollZeitZ23 As String
Dim sollZeitZ24 As String
Dim sollZeitZ25 As String
Dim sollZeitZ26 As String
Dim sollZeitZ27 As String
Dim sollZeitZ28 As String
Dim sollZeitZ29 As String
Dim sollZeitZ30 As String
Dim sollZeitZ31 As String
Dim sollZeitZ32 As String
Dim sollZeitZ33 As String
Dim sollZeitZ34 As String
Dim sollZeitZ35 As String
Dim sollZeitZ36 As String
Dim sollZeitZ37 As String
Dim sollZeitZ38 As String
Dim sollZeitZ39 As String
Dim sollZeitZ40 As String
Dim istZeit As String
Dim sollLehrer As String
Dim istLehrer As String
sollZeitZ1 = Worksheets("Tabelle2").Cells(2, 3)
sollZeitZ2 = Worksheets("Tabelle2").Cells(3, 3)
sollZeitZ3 = Worksheets("Tabelle2").Cells(4, 3)
sollZeitZ4 = Worksheets("Tabelle2").Cells(5, 3)
sollZeitZ5 = Worksheets("Tabelle2").Cells(6, 3)
sollZeitZ6 = Worksheets("Tabelle2").Cells(7, 3)
sollZeitZ7 = Worksheets("Tabelle2").Cells(8, 3)
sollZeitZ8 = Worksheets("Tabelle2").Cells(9, 3)
sollZeitZ9 = Worksheets("Tabelle2").Cells(10, 3)
sollZeitZ10 = Worksheets("Tabelle2").Cells(11, 3)
sollZeitZ11 = Worksheets("Tabelle2").Cells(12, 3)
sollZeitZ12 = Worksheets("Tabelle2").Cells(13, 3)
sollZeitZ13 = Worksheets("Tabelle2").Cells(14, 3)
sollZeitZ14 = Worksheets("Tabelle2").Cells(15, 3)
sollZeitZ15 = Worksheets("Tabelle2").Cells(16, 3)
sollZeitZ16 = Worksheets("Tabelle2").Cells(17, 3)
sollZeitZ17 = Worksheets("Tabelle2").Cells(18, 3)
sollZeitZ18 = Worksheets("Tabelle2").Cells(19, 3)
sollZeitZ19 = Worksheets("Tabelle2").Cells(20, 3)
sollZeitZ20 = Worksheets("Tabelle2").Cells(21, 3)
sollZeitZ21 = Worksheets("Tabelle2").Cells(22, 3)
sollZeitZ22 = Worksheets("Tabelle2").Cells(23, 3)
sollZeitZ23 = Worksheets("Tabelle2").Cells(24, 3)
sollZeitZ24 = Worksheets("Tabelle2").Cells(25, 3)
sollZeitZ25 = Worksheets("Tabelle2").Cells(26, 3)
sollZeitZ26 = Worksheets("Tabelle2").Cells(27, 3)
sollZeitZ27 = Worksheets("Tabelle2").Cells(28, 3)
sollZeitZ28 = Worksheets("Tabelle2").Cells(29, 3)
sollZeitZ29 = Worksheets("Tabelle2").Cells(30, 3)
sollZeitZ30 = Worksheets("Tabelle2").Cells(31, 3)
sollZeitZ31 = Worksheets("Tabelle2").Cells(32, 3)
sollZeitZ32 = Worksheets("Tabelle2").Cells(33, 3)
sollZeitZ33 = Worksheets("Tabelle2").Cells(34, 3)
sollZeitZ34 = Worksheets("Tabelle2").Cells(35, 3)
sollZeitZ35 = Worksheets("Tabelle2").Cells(36, 3)
sollZeitZ36 = Worksheets("Tabelle2").Cells(37, 3)
sollZeitZ37 = Worksheets("Tabelle2").Cells(38, 3)
sollZeitZ38 = Worksheets("Tabelle2").Cells(39, 3)
sollZeitZ39 = Worksheets("Tabelle2").Cells(40, 3)
sollZeitZ40 = Worksheets("Tabelle2").Cells(41, 3)
Worksheets("Tabelle4").Activate
'such = Worksheets("Tabelle3").Cells(2, 1) 'Suchwort aus A2
''such = Worksheets("Tabelle3").Cells(3, 1) 'Suchwort aus A2
' Dim Zelle As Range
Dim Such As Range
Dim Bereich As Range
' Set Bereich = Range("A2:A10")
Set Bereich = Range("A2:A" & Range("A65536").End(xlUp).Row)
' ActiveWorkbook.Names.Add Name:="Bereich", RefersTo:=Bereich
'Jetzt geht's los
' For Each Zelle In Bereich
For Each Such In Bereich
' Zelle.Select
Such.Select
' Zelle.Value = Zelle.Value + 1
' Next Zelle
letzteZ = Worksheets("Tabelle4").Range("A65536").End(xlUp).Row
For i = 2 To letzteZ
If Cells(i, 1) = Such Then
' If Cells(i, 1) = Zelle Then
Cells(i, 1).Select
' Selection.Interior.Color = 255 'rot
If Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ1 Then
Sheets("Tabelle4").Cells(i, 2).Select
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ2 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 3).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -1)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ3 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 4).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -2)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ4 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 5).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -3)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ5 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 6).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -4)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ6 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 7).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -5)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ6 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 8).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -6)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ7 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 8).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -6)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ8 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 9).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -7)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ9 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 10).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -8)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ10 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 11).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -9)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ11 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 12).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -10)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ12 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 13).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -11)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ13 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 14).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -12)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ14 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 15).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -13)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ15 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 16).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -14)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ16 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 17).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -15)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ17 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 18).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -16)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ18 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 19).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -17)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ19 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 20).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -18)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ20 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 21).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -19)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ21 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 22).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -20)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ22 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 23).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -21)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ23 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 23).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -21)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ24 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 25).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -23)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ25 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 26).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -24)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ26 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 27).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -25)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ27 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 28).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -26)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ28 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 29).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -27)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ29 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 30).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -28)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ30 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 31).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -29)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ31 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 32).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -30)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ32 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 33).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -31)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ33 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 34).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -32)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ34 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 35).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -33)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ35 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 36).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -34)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ36 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 37).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -35)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ37 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 38).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -36)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ38 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 39).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -37)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ39 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 40).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -38)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
ElseIf Sheets("Tabelle3").Cells(i, 3).Value = sollZeitZ40 Then
Worksheets("Tabelle1").Range("F2").Copy
Sheets("Tabelle4").Cells(i, 41).Select
With ActiveCell
Range(.Offset(0, -1), .Offset(0, -39)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End If
End If
Next
' Next Zelle
Next Such
End Sub
Funktioniert einwandfrei.
Gruss
Peter