AW: Probleme in Private Sub
21.12.2018 05:57:30
Harry
Danke Rob für den Hinweis also hier nochmal die Datei als xlsx
https://www.herber.de/bbs/user/126248.xlsx
Als Modul1
Sub Loeschen()
Dim zelle As Range
For Each zelle In Range("A92:E106")
zelle.Borders(xlEdgeLeft).ColorIndex = 2
zelle.Borders(xlEdgeRight).ColorIndex = 2
zelle.Borders(xlEdgeBottom).ColorIndex = 2
zelle.Value = ""
Next
End Sub
Als Private Sub()
Private Sub Worksheet_Calculate()
Dim zelle As Range
Dim zaehler As Integer
Dim Üst As Integer
Dim ÜstStart As Integer
Dim ÜL As Integer
Dim ÜLStart As Integer
Dim AB As Integer
Dim ABStart As Integer
Dim NF As Integer
Dim NFStart As Integer
Dim plusSpalte As Integer
Dim pluszeile As Integer
Üst = Range("B4").Value
ÜL = Range("B5").Value
AB = Range("D4").Value
NF = Range("D5").Value
ÜstStart = 7
ÜLStart = ÜstStart + Üst
ABStart = ÜLStart + ÜL
NFStart = ABStart + AB
plusSpalte = 0
pluszeile = 0
Select Case True 'BGQ4
Case Range("A2").Value = "Ausbildungsklasse D / BGQ 4"
GoTo Line1
Case Range("A2").Value = "Ausbildungsklasse D / DE / BGQ 4"
GoTo Line1 'Ende BGQ4
Case Range("A2").Value = "Ausbildungsklasse D / DE / BGQ 14" 'BGQ14
GoTo Line2
Case Range("A2").Value = "Ausbildungsklasse D / BGQ 14"
GoTo Line2
Case Else
GoTo Line3 'BGQohne
End Select
GoTo Line4
Line1: 'BGQ4
For Each zelle In Range("A93:E107")
zelle.Borders(xlEdgeLeft).ColorIndex = 2
zelle.Borders(xlEdgeRight).ColorIndex = 2
zelle.Borders(xlEdgeBottom).ColorIndex = 2
zelle.Value = ""
Next
For Each zelle In Range("A93:E97")
zelle.Borders(xlEdgeLeft).ColorIndex = 1
zelle.Borders(xlEdgeRight).ColorIndex = 1
zelle.Borders(xlEdgeTop).ColorIndex = 1
zelle.Borders(xlEdgeBottom).ColorIndex = 1
Next
Range("A92").Value = "IHK"
Range("B92").Value = "Stunden zu je 45 Minuten"
Range("B93").Value = "geplant"
Range("D93").Value = "Datum"
Range("E93").Value = "Fahrlehrer"
For zaehler = 0 To 3
Cells((94 + zaehler), 1).Value = 1 + zaehler
Next
GoTo Line4
Line2: 'BGQ14
For Each zelle In Range("A93:E107")
zelle.Borders(xlEdgeLeft).ColorIndex = 2
zelle.Borders(xlEdgeRight).ColorIndex = 2
zelle.Borders(xlEdgeBottom).ColorIndex = 2
zelle.Value = ""
Next
For Each zelle In Range("A93:E107")
zelle.Borders(xlEdgeLeft).ColorIndex = 1
zelle.Borders(xlEdgeRight).ColorIndex = 1
zelle.Borders(xlEdgeTop).ColorIndex = 1
zelle.Borders(xlEdgeBottom).ColorIndex = 1
Next
Range("A92").Value = "IHK"
Range("B92").Value = "Stunden zu je 45 Minuten"
Range("B93").Value = "geplant"
Range("D93").Value = "Datum"
Range("E93").Value = "Fahrlehrer"
For zaehler = 0 To 13
Cells((94 + zaehler), 1).Value = 1 + zaehler
Next
GoTo Line4
Line3: 'Ohne BGQ Case Else
For Each zelle In Range("A92:E107")
zelle.Borders(xlEdgeLeft).ColorIndex = 2
zelle.Borders(xlEdgeRight).ColorIndex = 2
zelle.Borders(xlEdgeBottom).ColorIndex = 2
zelle.Value = ""
Next
Line4:
For zaehler = ÜstStart To ÜstStart + Üst + ÜL + AB + NF - 1 'leer
Worksheets("Schüler 1").Cells((zaehler + pluszeile), (3 + plusSpalte)).Value = ""
If zaehler > 51 Then
pluszeile = -46
plusSpalte = 7
End If
Next
For zaehler = ÜstStart To ÜstStart + Üst - 1 'Üst
Worksheets("Schüler 1").Cells((zaehler + pluszeile), (3 + plusSpalte)).Value = "Üst"
If zaehler > 51 Then
pluszeile = -46
plusSpalte = 7
End If
Next
For zaehler = ÜLStart To ÜLStart + ÜL - 1 'ÜL
Worksheets("Schüler 1").Cells((zaehler + pluszeile), (3 + plusSpalte)).Value = "ÜL"
If zaehler > 51 Then
pluszeile = -46
plusSpalte = 7
End If
Next
For zaehler = ABStart To ABStart + AB - 1 'AB
Worksheets("Schüler 1").Cells((zaehler + pluszeile), (3 + plusSpalte)).Value = "AB"
If zaehler > 51 Then
pluszeile = -46
plusSpalte = 7
End If
Next
For zaehler = NFStart To NFStart + NF - 1 'NF
Worksheets("Schüler 1").Cells((zaehler + pluszeile), (3 + plusSpalte)).Value = "NF"
If zaehler > 51 Then
pluszeile = -46
plusSpalte = 7
End If
Next
End Sub