Office: (Office 365) Variabler Zeilensprung.

Halo Anton,
ich verstehe es, aber wenn D13= 35 ist Springt es schon bei E74 nach G32 Staat bei E79. Muss ich hier "ElseIf Not Intersect(Range("E74, E79, E84, E89, E94"), Target) Is Nothing Then
Range("G32").Select" noch was ändern, damit es funktioniert?

 

Hallo,

wenn du nicht haben willst, dass es schon bei E74 nach G32 springt, sondern erst bei E79, dann musst du E74 aus dieser Codezeile entfernen, zB:
Case 30, 35, 40, 45, 50:
If Not Intersect(Range("E56"), Target) Is Nothing Then
Range("E70").Select
ElseIf Not Intersect(Range("E79, E84, E89, E94"), Target) Is Nothing Then
Range("G32").Select
End If
Wenn du jedoch haben willst, dass bei einzelnen Case-Zahlen (für Zelle D13) das Sprungverhalten unterschiedlich sein soll, so darfst du nur jeweils die Zahlen in einer Case-Zeilen-Liste zusammenfassen, die gleiches Sprungverhalten haben (d.h. die gleichen VBA-Code zur Folge haben).

 

Kannst du mir bitte Zeigen wie ich das machen soll. das bei 30 auf E74 springt bei 35 Auf E79 u.s.w.
Ich habe von Programmieren keine Ahnung. Denn Ersten Code habe ich im Internet Forum gefunden und es an mich angepasst.

 

Hallo,

soll das heißen, du möchtest unabhängig davon, wo die sich die geänderte Zelle (Target) gerade befindet, zur Zelle E74 springen, falls in Zelle D13 der Wert 30 enthalten ist, und nach Zelle E79, falls in D13 der Wert 35 enthalten ist?

Code:

   Select Case Range("D13").Value
      Case 10:
         If Not Intersect(Range("E41"), target) Is Nothing Then Range("g32").Select
      Case 15:
         If Not Intersect(Range("E46"), target) Is Nothing Then Range("g32").Select
      Case 20:
         If Not Intersect(Range("E51"), target) Is Nothing Then Range("g32").Select
      Case 25:
         If Not Intersect(Range("E56"), target) Is Nothing Then Range("g32").Select
      Case 30:
         Range("E74").Select
      Case 35:
         Range("E79").Select
   End Select

 

Hallo,

Wenn d13 = 30, 35, 40, 45 oder 50 ist soll es von erst von E56 auf E70 Springen und dann je nach welche Zahl in D13 Steht bei erreichen (30)- E74, (35)-E79, (40)-E84, (45)- E89; (50)-E94 in die g32 Springen.

 

Vielleicht wenn es einfacher ist, kann es auch dynamisch eingestellt werden- Z.B Wenn D13= 21 ist Springt es bei erreichen von 51 zu g32 u.s.w (nicht festgelegt auf 10, 15, 20 sondern von 1-50) falls es einfacher ist

 

Hallo,

Code:

   Select Case Range("D13").Value
      Case 10:
         If Not Intersect(Range("E41"), Target) Is Nothing Then Range("g32").Select
      Case 15:
         If Not Intersect(Range("E46"), Target) Is Nothing Then Range("g32").Select
      Case 20:
         If Not Intersect(Range("E51"), Target) Is Nothing Then Range("g32").Select
      Case 25:
         If Not Intersect(Range("E56"), Target) Is Nothing Then Range("g32").Select
      Case 30, 35, 40, 45, 50:
         If Not Intersect(Range("E56"), Target) Is Nothing Then
            Range("E70").Select
         Else
            Select Case Range("D13").Value
              Case 30: If Not Intersect(Range("E74"), Target) Is Nothing Then Range("G32").Select
              Case 35: If Not Intersect(Range("E79"), Target) Is Nothing Then Range("G32").Select
              Case 40: If Not Intersect(Range("E84"), Target) Is Nothing Then Range("G32").Select
              Case 45: If Not Intersect(Range("E89"), Target) Is Nothing Then Range("G32").Select
              Case 50: If Not Intersect(Range("E94"), Target) Is Nothing Then Range("G32").Select
            End Select
         End If
   End Select

 

Vielen Dank Anton.
Jetzt Funktioniert es Perfekt!
Wüsche dir schönen Tag noch.

 

Hallo Anton,

Kann man so machen das bei diesen Code
"If Not Intersect(Range("e51"), Target) Is Nothing Then
Range("g32").Select
End If"
Es trotzdem weiter springt in vorgegebenes Feld auch wenn keine Eingabe von wert
erfolgt ist. Ohne Eingabe von Wert springt es nur eine Zeile runter .

 

Hallo Andre,

das ist nicht möglich, denn schließlich wird dieses Makro nur aktiv, wenn es in einer Zelle eine Zellwertänderung aufgrund einer Tastatureingabe gegeben hat. Die Variable "Target" im Makroaufruf enthält diese Zelle mit der Zellwertänderung.

Dass die aktive Zelle quasi "bei keiner Wert-Eingabe" trotzdem in die nächste Zeile springt, wird durch die Betätigung der EINGABE-Taste verursacht, wobei dieses Verhalten in den Excel-Optionen/Erweitert/Bearbeitungsoptionen festlegbar ist.

 

Hallo Anton,
Ich habe das jetzt vergrößert bis au 10 Seiten (insgesamt 60 solche Code Blöcke).
jetzt kommt es zur Fehlermeldung "Prozedur zu groß" Bei 2 Seiten war es noch in Ordnung. ab 3 Seiten kommt es zu Problemen. Was kann ich da machen?
Mit freundlichen grüßen
Andre

 

ich Habe was im Internet gefunden um das Problem zu umgehen jetzt funktioniert aber Zeilensprung nicht mehr.

 

Hallo,

ehrlich gesagt, obwohl ich bereits 40 Jahre programmiere (ca. 25 Jahre auch in Excel), ist mir diese Fehlermeldung noch nie begegnet. Aber ich habe nachgesehen im Internet bei Google ("Excel: Prozedur zu groß"), so gibt's dort jede Menge an Beschreibungen und Lösungen. Offenbar darf eine VBA-Prozedur nicht größer sein als 64 kB.

Die Lösung besteht in einer Aufteilung der großen Prozedur in mehrere kleinere Prozeduren, so wie du vorgeschlagen hast.

Die erste (zu)große Prozedur besitzt folgenden Prozedurkopf:
Private Sub Worksheet_Change(ByVal Target As Range)
wobei der Klammerausdruck auch sehr wichtig ist: Im Parameter "Target", der den Typ "Range" (=Zellbereich) hat, wird die Zelladresse der sich geändert habenden Zelle an die SUB übergeben, damit die SUB weiß, wo die Änderung stattgefunden hat.
Wenn du die große SUB zerstückelst in weitere 3 kleinere SUBs, musst du diesen das (ByVal Target As Range) auch übergeben, damit auch diese wissen, dass der geänderte Zellwert in "Target" vorlag/vorliegt.

Ich habe dir im Folgenden deinen übermittelten VBA-Code an den kritischen Stellen rot bzw. fett eingefärbt.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("d11"), Target) Is Nothing Then
Range("i13").Select
End If

If Not Intersect(Range("i13"), Target) Is Nothing Then
Range("B17").Select
End If

If Not Intersect(Range("b17"), Target) Is Nothing Then
Range("G17").Select
End If

If Not Intersect(Range("G17"), Target) Is Nothing Then
Range("L17").Select
End If

If Not Intersect(Range("L17"), Target) Is Nothing Then
Range("c32").Select
End If

Call Proc1(Target)
Call Proc2(Target)
Call Proc3(Target)

End Sub

Sub Proc1(ByVal Target As Range)

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("C36"), Target) Is Nothing Then Range("E32").Select
Case 10:
If Not Intersect(Range("C41"), Target) Is Nothing Then Range("E32").Select
Case 15:
If Not Intersect(Range("C46"), Target) Is Nothing Then Range("E32").Select
Case 20:
If Not Intersect(Range("C51"), Target) Is Nothing Then Range("E32").Select
Case 25:
If Not Intersect(Range("C56"), Target) Is Nothing Then Range("E32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("C56"), Target) Is Nothing Then
Range("C69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("C73"), Target) Is Nothing Then Range("E32").Select
Case 35: If Not Intersect(Range("C78"), Target) Is Nothing Then Range("E32").Select
Case 40: If Not Intersect(Range("C83"), Target) Is Nothing Then Range("E32").Select
Case 45: If Not Intersect(Range("C88"), Target) Is Nothing Then Range("E32").Select
Case 50: If Not Intersect(Range("C93"), Target) Is Nothing Then Range("E32").Select
Case 55: If Not Intersect(Range("C98"), Target) Is Nothing Then Range("E32").Select
Case 60: If Not Intersect(Range("C103"), Target) Is Nothing Then Range("E32").Select
Case 65: If Not Intersect(Range("C108"), Target) Is Nothing Then Range("E32").Select
Case 70: If Not Intersect(Range("C113"), Target) Is Nothing Then Range("E32").Select
Case 75: If Not Intersect(Range("C118"), Target) Is Nothing Then Range("E32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("E36"), Target) Is Nothing Then Range("G32").Select
Case 10:
If Not Intersect(Range("E41"), Target) Is Nothing Then Range("G32").Select
Case 15:
If Not Intersect(Range("E46"), Target) Is Nothing Then Range("G32").Select
Case 20:
If Not Intersect(Range("E51"), Target) Is Nothing Then Range("G32").Select
Case 25:
If Not Intersect(Range("E56"), Target) Is Nothing Then Range("G32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("E56"), Target) Is Nothing Then
Range("E69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("E73"), Target) Is Nothing Then Range("G32").Select
Case 35: If Not Intersect(Range("E78"), Target) Is Nothing Then Range("G32").Select
Case 40: If Not Intersect(Range("E83"), Target) Is Nothing Then Range("G32").Select
Case 45: If Not Intersect(Range("E88"), Target) Is Nothing Then Range("G32").Select
Case 50: If Not Intersect(Range("E93"), Target) Is Nothing Then Range("G32").Select
Case 55: If Not Intersect(Range("E98"), Target) Is Nothing Then Range("G32").Select
Case 60: If Not Intersect(Range("E103"), Target) Is Nothing Then Range("G32").Select
Case 65: If Not Intersect(Range("E108"), Target) Is Nothing Then Range("G32").Select
Case 70: If Not Intersect(Range("E113"), Target) Is Nothing Then Range("G32").Select
Case 75: If Not Intersect(Range("E118"), Target) Is Nothing Then Range("G32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("G36"), Target) Is Nothing Then Range("J32").Select
Case 10:
If Not Intersect(Range("G41"), Target) Is Nothing Then Range("J32").Select
Case 15:
If Not Intersect(Range("G46"), Target) Is Nothing Then Range("J32").Select
Case 20:
If Not Intersect(Range("G51"), Target) Is Nothing Then Range("J32").Select
Case 25:
If Not Intersect(Range("G56"), Target) Is Nothing Then Range("J32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("G56"), Target) Is Nothing Then
Range("G69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("G73"), Target) Is Nothing Then Range("J32").Select
Case 35: If Not Intersect(Range("G78"), Target) Is Nothing Then Range("J32").Select
Case 40: If Not Intersect(Range("G83"), Target) Is Nothing Then Range("J32").Select
Case 45: If Not Intersect(Range("G88"), Target) Is Nothing Then Range("J32").Select
Case 50: If Not Intersect(Range("G93"), Target) Is Nothing Then Range("J32").Select
Case 55: If Not Intersect(Range("G98"), Target) Is Nothing Then Range("J32").Select
Case 60: If Not Intersect(Range("G103"), Target) Is Nothing Then Range("J32").Select
Case 65: If Not Intersect(Range("G108"), Target) Is Nothing Then Range("J32").Select
Case 70: If Not Intersect(Range("G113"), Target) Is Nothing Then Range("J32").Select
Case 75: If Not Intersect(Range("G118"), Target) Is Nothing Then Range("J32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("J36"), Target) Is Nothing Then Range("K32").Select
Case 10:
If Not Intersect(Range("J41"), Target) Is Nothing Then Range("K32").Select
Case 15:
If Not Intersect(Range("J46"), Target) Is Nothing Then Range("K32").Select
Case 20:
If Not Intersect(Range("J51"), Target) Is Nothing Then Range("K32").Select
Case 25:
If Not Intersect(Range("J56"), Target) Is Nothing Then Range("K32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("J56"), Target) Is Nothing Then
Range("J69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("J73"), Target) Is Nothing Then Range("K32").Select
Case 35: If Not Intersect(Range("J78"), Target) Is Nothing Then Range("K32").Select
Case 40: If Not Intersect(Range("J83"), Target) Is Nothing Then Range("K32").Select
Case 45: If Not Intersect(Range("J88"), Target) Is Nothing Then Range("K32").Select
Case 50: If Not Intersect(Range("J93"), Target) Is Nothing Then Range("K32").Select
Case 55: If Not Intersect(Range("J98"), Target) Is Nothing Then Range("K32").Select
Case 60: If Not Intersect(Range("J103"), Target) Is Nothing Then Range("K32").Select
Case 65: If Not Intersect(Range("J108"), Target) Is Nothing Then Range("K32").Select
Case 70: If Not Intersect(Range("J113"), Target) Is Nothing Then Range("K32").Select
Case 75: If Not Intersect(Range("J118"), Target) Is Nothing Then Range("K32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("K36"), Target) Is Nothing Then Range("M32").Select
Case 10:
If Not Intersect(Range("K41"), Target) Is Nothing Then Range("M32").Select
Case 15:
If Not Intersect(Range("K46"), Target) Is Nothing Then Range("M32").Select
Case 20:
If Not Intersect(Range("K51"), Target) Is Nothing Then Range("M32").Select
Case 25:
If Not Intersect(Range("K56"), Target) Is Nothing Then Range("M32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("K56"), Target) Is Nothing Then
Range("k69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("K73"), Target) Is Nothing Then Range("M32").Select
Case 35: If Not Intersect(Range("K78"), Target) Is Nothing Then Range("M32").Select
Case 40: If Not Intersect(Range("K83"), Target) Is Nothing Then Range("M32").Select
Case 45: If Not Intersect(Range("K88"), Target) Is Nothing Then Range("M32").Select
Case 50: If Not Intersect(Range("K93"), Target) Is Nothing Then Range("M32").Select
Case 55: If Not Intersect(Range("K98"), Target) Is Nothing Then Range("M32").Select
Case 60: If Not Intersect(Range("K103"), Target) Is Nothing Then Range("M32").Select
Case 65: If Not Intersect(Range("K108"), Target) Is Nothing Then Range("M32").Select
Case 70: If Not Intersect(Range("K113"), Target) Is Nothing Then Range("M32").Select
Case 75: If Not Intersect(Range("K118"), Target) Is Nothing Then Range("M32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("M36"), Target) Is Nothing Then Range("P32").Select
Case 10:
If Not Intersect(Range("M41"), Target) Is Nothing Then Range("P32").Select
Case 15:
If Not Intersect(Range("M46"), Target) Is Nothing Then Range("P32").Select
Case 20:
If Not Intersect(Range("M51"), Target) Is Nothing Then Range("P32").Select
Case 25:
If Not Intersect(Range("M56"), Target) Is Nothing Then Range("P32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("M56"), Target) Is Nothing Then
Range("M69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("M73"), Target) Is Nothing Then Range("P32").Select
Case 35: If Not Intersect(Range("M78"), Target) Is Nothing Then Range("P32").Select
Case 40: If Not Intersect(Range("M83"), Target) Is Nothing Then Range("P32").Select
Case 45: If Not Intersect(Range("M88"), Target) Is Nothing Then Range("P32").Select
Case 50: If Not Intersect(Range("M93"), Target) Is Nothing Then Range("P32").Select
Case 55: If Not Intersect(Range("M98"), Target) Is Nothing Then Range("P32").Select
Case 60: If Not Intersect(Range("M103"), Target) Is Nothing Then Range("P32").Select
Case 65: If Not Intersect(Range("M108"), Target) Is Nothing Then Range("P32").Select
Case 70: If Not Intersect(Range("M113"), Target) Is Nothing Then Range("P32").Select
Case 75: If Not Intersect(Range("M118"), Target) Is Nothing Then Range("P32").Select
End Select
End If
End Select

End Sub

Sub Proc2(ByVal Target As Range)
Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("P36"), Target) Is Nothing Then Range("R32").Select
Case 10:
If Not Intersect(Range("P41"), Target) Is Nothing Then Range("R32").Select
Case 15:
If Not Intersect(Range("P46"), Target) Is Nothing Then Range("R32").Select
Case 20:
If Not Intersect(Range("P51"), Target) Is Nothing Then Range("R32").Select
Case 25:
If Not Intersect(Range("P56"), Target) Is Nothing Then Range("R32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("P56"), Target) Is Nothing Then
Range("P69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("P73"), Target) Is Nothing Then Range("R32").Select
Case 35: If Not Intersect(Range("P78"), Target) Is Nothing Then Range("R32").Select
Case 40: If Not Intersect(Range("P83"), Target) Is Nothing Then Range("R32").Select
Case 45: If Not Intersect(Range("P88"), Target) Is Nothing Then Range("R32").Select
Case 50: If Not Intersect(Range("P93"), Target) Is Nothing Then Range("R32").Select
Case 55: If Not Intersect(Range("P98"), Target) Is Nothing Then Range("R32").Select
Case 60: If Not Intersect(Range("P103"), Target) Is Nothing Then Range("R32").Select
Case 65: If Not Intersect(Range("P108"), Target) Is Nothing Then Range("R32").Select
Case 70: If Not Intersect(Range("P113"), Target) Is Nothing Then Range("R32").Select
Case 75: If Not Intersect(Range("P118"), Target) Is Nothing Then Range("R32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("R36"), Target) Is Nothing Then Range("T32").Select
Case 10:
If Not Intersect(Range("R41"), Target) Is Nothing Then Range("T32").Select
Case 15:
If Not Intersect(Range("R46"), Target) Is Nothing Then Range("T32").Select
Case 20:
If Not Intersect(Range("R51"), Target) Is Nothing Then Range("T32").Select
Case 25:
If Not Intersect(Range("R56"), Target) Is Nothing Then Range("T32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("R56"), Target) Is Nothing Then
Range("R69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("R73"), Target) Is Nothing Then Range("T32").Select
Case 35: If Not Intersect(Range("R78"), Target) Is Nothing Then Range("T32").Select
Case 40: If Not Intersect(Range("R83"), Target) Is Nothing Then Range("T32").Select
Case 45: If Not Intersect(Range("R88"), Target) Is Nothing Then Range("T32").Select
Case 50: If Not Intersect(Range("R93"), Target) Is Nothing Then Range("T32").Select
Case 55: If Not Intersect(Range("R98"), Target) Is Nothing Then Range("T32").Select
Case 60: If Not Intersect(Range("R103"), Target) Is Nothing Then Range("T32").Select
Case 65: If Not Intersect(Range("R108"), Target) Is Nothing Then Range("T32").Select
Case 70: If Not Intersect(Range("R113"), Target) Is Nothing Then Range("T32").Select
Case 75: If Not Intersect(Range("R118"), Target) Is Nothing Then Range("T32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("T36"), Target) Is Nothing Then Range("V32").Select
Case 10:
If Not Intersect(Range("T41"), Target) Is Nothing Then Range("V32").Select
Case 15:
If Not Intersect(Range("T46"), Target) Is Nothing Then Range("V32").Select
Case 20:
If Not Intersect(Range("T51"), Target) Is Nothing Then Range("V32").Select
Case 25:
If Not Intersect(Range("T56"), Target) Is Nothing Then Range("V32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("T56"), Target) Is Nothing Then
Range("T69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("T73"), Target) Is Nothing Then Range("V32").Select
Case 35: If Not Intersect(Range("T78"), Target) Is Nothing Then Range("V32").Select
Case 40: If Not Intersect(Range("T83"), Target) Is Nothing Then Range("V32").Select
Case 45: If Not Intersect(Range("T88"), Target) Is Nothing Then Range("V32").Select
Case 50: If Not Intersect(Range("T93"), Target) Is Nothing Then Range("V32").Select
Case 55: If Not Intersect(Range("T98"), Target) Is Nothing Then Range("V32").Select
Case 60: If Not Intersect(Range("T103"), Target) Is Nothing Then Range("V32").Select
Case 65: If Not Intersect(Range("T108"), Target) Is Nothing Then Range("V32").Select
Case 70: If Not Intersect(Range("T113"), Target) Is Nothing Then Range("V32").Select
Case 75: If Not Intersect(Range("T118"), Target) Is Nothing Then Range("V32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("V36"), Target) Is Nothing Then Range("X32").Select
Case 10:
If Not Intersect(Range("V41"), Target) Is Nothing Then Range("X32").Select
Case 15:
If Not Intersect(Range("V46"), Target) Is Nothing Then Range("X32").Select
Case 20:
If Not Intersect(Range("V51"), Target) Is Nothing Then Range("X32").Select
Case 25:
If Not Intersect(Range("V56"), Target) Is Nothing Then Range("X32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("V56"), Target) Is Nothing Then
Range("V69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("V73"), Target) Is Nothing Then Range("X32").Select
Case 35: If Not Intersect(Range("V78"), Target) Is Nothing Then Range("X32").Select
Case 40: If Not Intersect(Range("V83"), Target) Is Nothing Then Range("X32").Select
Case 45: If Not Intersect(Range("V88"), Target) Is Nothing Then Range("X32").Select
Case 50: If Not Intersect(Range("V93"), Target) Is Nothing Then Range("X32").Select
Case 55: If Not Intersect(Range("V98"), Target) Is Nothing Then Range("X32").Select
Case 60: If Not Intersect(Range("V103"), Target) Is Nothing Then Range("X32").Select
Case 65: If Not Intersect(Range("V108"), Target) Is Nothing Then Range("X32").Select
Case 70: If Not Intersect(Range("V113"), Target) Is Nothing Then Range("X32").Select
Case 75: If Not Intersect(Range("V118"), Target) Is Nothing Then Range("X32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("X36"), Target) Is Nothing Then Range("Z32").Select
Case 10:
If Not Intersect(Range("X41"), Target) Is Nothing Then Range("Z32").Select
Case 15:
If Not Intersect(Range("X46"), Target) Is Nothing Then Range("Z32").Select
Case 20:
If Not Intersect(Range("X51"), Target) Is Nothing Then Range("Z32").Select
Case 25:
If Not Intersect(Range("X56"), Target) Is Nothing Then Range("Z32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("X56"), Target) Is Nothing Then
Range("X69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("X73"), Target) Is Nothing Then Range("Z32").Select
Case 35: If Not Intersect(Range("X78"), Target) Is Nothing Then Range("Z32").Select
Case 40: If Not Intersect(Range("X83"), Target) Is Nothing Then Range("Z32").Select
Case 45: If Not Intersect(Range("X88"), Target) Is Nothing Then Range("Z32").Select
Case 50: If Not Intersect(Range("X93"), Target) Is Nothing Then Range("Z32").Select
Case 55: If Not Intersect(Range("X98"), Target) Is Nothing Then Range("Z32").Select
Case 60: If Not Intersect(Range("X103"), Target) Is Nothing Then Range("Z32").Select
Case 65: If Not Intersect(Range("X108"), Target) Is Nothing Then Range("Z32").Select
Case 70: If Not Intersect(Range("X113"), Target) Is Nothing Then Range("Z32").Select
Case 75: If Not Intersect(Range("X118"), Target) Is Nothing Then Range("Z32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("Z36"), Target) Is Nothing Then Range("AC32").Select
Case 10:
If Not Intersect(Range("Z41"), Target) Is Nothing Then Range("AC32").Select
Case 15:
If Not Intersect(Range("Z46"), Target) Is Nothing Then Range("AC32").Select
Case 20:
If Not Intersect(Range("Z51"), Target) Is Nothing Then Range("AC32").Select
Case 25:
If Not Intersect(Range("Z56"), Target) Is Nothing Then Range("AC32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("Z56"), Target) Is Nothing Then
Range("Z69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("Z73"), Target) Is Nothing Then Range("AC32").Select
Case 35: If Not Intersect(Range("Z78"), Target) Is Nothing Then Range("AC32").Select
Case 40: If Not Intersect(Range("Z83"), Target) Is Nothing Then Range("AC32").Select
Case 45: If Not Intersect(Range("Z88"), Target) Is Nothing Then Range("AC32").Select
Case 50: If Not Intersect(Range("Z93"), Target) Is Nothing Then Range("AC32").Select
Case 55: If Not Intersect(Range("Z98"), Target) Is Nothing Then Range("AC32").Select
Case 60: If Not Intersect(Range("Z103"), Target) Is Nothing Then Range("AC32").Select
Case 65: If Not Intersect(Range("Z108"), Target) Is Nothing Then Range("AC32").Select
Case 70: If Not Intersect(Range("Z113"), Target) Is Nothing Then Range("AC32").Select
Case 75: If Not Intersect(Range("Z118"), Target) Is Nothing Then Range("AC32").Select
End Select
End If
End Select
End Sub

Sub Proc3(ByVal Target As Range)
Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("AC36"), Target) Is Nothing Then Range("AE32").Select
Case 10:
If Not Intersect(Range("AC41"), Target) Is Nothing Then Range("AE32").Select
Case 15:
If Not Intersect(Range("AC46"), Target) Is Nothing Then Range("AE32").Select
Case 20:
If Not Intersect(Range("AC51"), Target) Is Nothing Then Range("AE32").Select
Case 25:
If Not Intersect(Range("AC56"), Target) Is Nothing Then Range("AE32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("AC56"), Target) Is Nothing Then
Range("AC69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("AC73"), Target) Is Nothing Then Range("AE32").Select
Case 35: If Not Intersect(Range("AC78"), Target) Is Nothing Then Range("AE32").Select
Case 40: If Not Intersect(Range("AC83"), Target) Is Nothing Then Range("AE32").Select
Case 45: If Not Intersect(Range("AC88"), Target) Is Nothing Then Range("AE32").Select
Case 50: If Not Intersect(Range("AC93"), Target) Is Nothing Then Range("AE32").Select
Case 55: If Not Intersect(Range("AC98"), Target) Is Nothing Then Range("AE32").Select
Case 60: If Not Intersect(Range("AC103"), Target) Is Nothing Then Range("AE32").Select
Case 65: If Not Intersect(Range("AC108"), Target) Is Nothing Then Range("AE32").Select
Case 70: If Not Intersect(Range("AC113"), Target) Is Nothing Then Range("AE32").Select
Case 75: If Not Intersect(Range("AC118"), Target) Is Nothing Then Range("AE32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("AE36"), Target) Is Nothing Then Range("AG32").Select
Case 10:
If Not Intersect(Range("AE41"), Target) Is Nothing Then Range("AG32").Select
Case 15:
If Not Intersect(Range("AE46"), Target) Is Nothing Then Range("AG32").Select
Case 20:
If Not Intersect(Range("AE51"), Target) Is Nothing Then Range("AG32").Select
Case 25:
If Not Intersect(Range("AE56"), Target) Is Nothing Then Range("AG32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("AE56"), Target) Is Nothing Then
Range("AE69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("AE73"), Target) Is Nothing Then Range("AG32").Select
Case 35: If Not Intersect(Range("AE78"), Target) Is Nothing Then Range("AG32").Select
Case 40: If Not Intersect(Range("AE83"), Target) Is Nothing Then Range("AG32").Select
Case 45: If Not Intersect(Range("AE88"), Target) Is Nothing Then Range("AG32").Select
Case 50: If Not Intersect(Range("AE93"), Target) Is Nothing Then Range("AG32").Select
Case 55: If Not Intersect(Range("AE98"), Target) Is Nothing Then Range("AG32").Select
Case 60: If Not Intersect(Range("AE103"), Target) Is Nothing Then Range("AG32").Select
Case 65: If Not Intersect(Range("AE108"), Target) Is Nothing Then Range("AG32").Select
Case 70: If Not Intersect(Range("AE113"), Target) Is Nothing Then Range("AG32").Select
Case 75: If Not Intersect(Range("AE118"), Target) Is Nothing Then Range("AG32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("AG36"), Target) Is Nothing Then Range("AI32").Select
Case 10:
If Not Intersect(Range("AG41"), Target) Is Nothing Then Range("AI32").Select
Case 15:
If Not Intersect(Range("AG46"), Target) Is Nothing Then Range("AI32").Select
Case 20:
If Not Intersect(Range("AG51"), Target) Is Nothing Then Range("AI32").Select
Case 25:
If Not Intersect(Range("AG56"), Target) Is Nothing Then Range("AI32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("AG56"), Target) Is Nothing Then
Range("AG69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("AG73"), Target) Is Nothing Then Range("AI32").Select
Case 35: If Not Intersect(Range("AG78"), Target) Is Nothing Then Range("AI32").Select
Case 40: If Not Intersect(Range("AG83"), Target) Is Nothing Then Range("AI32").Select
Case 45: If Not Intersect(Range("AG88"), Target) Is Nothing Then Range("AI32").Select
Case 50: If Not Intersect(Range("AG93"), Target) Is Nothing Then Range("AI32").Select
Case 55: If Not Intersect(Range("AG98"), Target) Is Nothing Then Range("AI32").Select
Case 60: If Not Intersect(Range("AG103"), Target) Is Nothing Then Range("AI32").Select
Case 65: If Not Intersect(Range("AG108"), Target) Is Nothing Then Range("AI32").Select
Case 70: If Not Intersect(Range("AG113"), Target) Is Nothing Then Range("AI32").Select
Case 75: If Not Intersect(Range("AG118"), Target) Is Nothing Then Range("AI32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("AI36"), Target) Is Nothing Then Range("AK32").Select
Case 10:
If Not Intersect(Range("AI41"), Target) Is Nothing Then Range("AK32").Select
Case 15:
If Not Intersect(Range("AI46"), Target) Is Nothing Then Range("AK32").Select
Case 20:
If Not Intersect(Range("AI51"), Target) Is Nothing Then Range("AK32").Select
Case 25:
If Not Intersect(Range("AI56"), Target) Is Nothing Then Range("AK32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("AI56"), Target) Is Nothing Then
Range("AI69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("AI73"), Target) Is Nothing Then Range("AK32").Select
Case 35: If Not Intersect(Range("AI78"), Target) Is Nothing Then Range("AK32").Select
Case 40: If Not Intersect(Range("AI83"), Target) Is Nothing Then Range("AK32").Select
Case 45: If Not Intersect(Range("AI88"), Target) Is Nothing Then Range("AK32").Select
Case 50: If Not Intersect(Range("AI93"), Target) Is Nothing Then Range("AK32").Select
Case 55: If Not Intersect(Range("AI98"), Target) Is Nothing Then Range("AK32").Select
Case 60: If Not Intersect(Range("AI103"), Target) Is Nothing Then Range("AK32").Select
Case 65: If Not Intersect(Range("AI108"), Target) Is Nothing Then Range("AK32").Select
Case 70: If Not Intersect(Range("AI113"), Target) Is Nothing Then Range("AK32").Select
Case 75: If Not Intersect(Range("AI118"), Target) Is Nothing Then Range("AK32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("AK36"), Target) Is Nothing Then Range("AM32").Select
Case 10:
If Not Intersect(Range("AK41"), Target) Is Nothing Then Range("AM32").Select
Case 15:
If Not Intersect(Range("AK46"), Target) Is Nothing Then Range("AM32").Select
Case 20:
If Not Intersect(Range("AK51"), Target) Is Nothing Then Range("AM32").Select
Case 25:
If Not Intersect(Range("AK56"), Target) Is Nothing Then Range("AM32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("AK56"), Target) Is Nothing Then
Range("AK69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("AK73"), Target) Is Nothing Then Range("AM32").Select
Case 35: If Not Intersect(Range("AK78"), Target) Is Nothing Then Range("AM32").Select
Case 40: If Not Intersect(Range("AK83"), Target) Is Nothing Then Range("AM32").Select
Case 45: If Not Intersect(Range("AK88"), Target) Is Nothing Then Range("AM32").Select
Case 50: If Not Intersect(Range("AK93"), Target) Is Nothing Then Range("AM32").Select
Case 55: If Not Intersect(Range("AK98"), Target) Is Nothing Then Range("AM32").Select
Case 60: If Not Intersect(Range("AK103"), Target) Is Nothing Then Range("AM32").Select
Case 65: If Not Intersect(Range("AK108"), Target) Is Nothing Then Range("AM32").Select
Case 70: If Not Intersect(Range("AK113"), Target) Is Nothing Then Range("AM32").Select
Case 75: If Not Intersect(Range("AK118"), Target) Is Nothing Then Range("AM32").Select
End Select
End If
End Select

Select Case Range("D13").Value
Case 5:
If Not Intersect(Range("AM36"), Target) Is Nothing Then Range("AP32").Select
Case 10:
If Not Intersect(Range("AM41"), Target) Is Nothing Then Range("AP32").Select
Case 15:
If Not Intersect(Range("AM46"), Target) Is Nothing Then Range("AP32").Select
Case 20:
If Not Intersect(Range("AM51"), Target) Is Nothing Then Range("AP32").Select
Case 25:
If Not Intersect(Range("AM56"), Target) Is Nothing Then Range("AP32").Select
Case 30, 35, 40, 45, 50, 55, 60, 65, 70, 75:
If Not Intersect(Range("AM56"), Target) Is Nothing Then
Range("AM69").Select
Else
Select Case Range("D13").Value
Case 30: If Not Intersect(Range("AM73"), Target) Is Nothing Then Range("AP32").Select
Case 35: If Not Intersect(Range("AM78"), Target) Is Nothing Then Range("AP32").Select
Case 40: If Not Intersect(Range("AM83"), Target) Is Nothing Then Range("AP32").Select
Case 45: If Not Intersect(Range("AM88"), Target) Is Nothing Then Range("AP32").Select
Case 50: If Not Intersect(Range("AM93"), Target) Is Nothing Then Range("AP32").Select
Case 55: If Not Intersect(Range("AM98"), Target) Is Nothing Then Range("AP32").Select
Case 60: If Not Intersect(Range("AM103"), Target) Is Nothing Then Range("AP32").Select
Case 65: If Not Intersect(Range("AM108"), Target) Is Nothing Then Range("AP32").Select
Case 70: If Not Intersect(Range("AM113"), Target) Is Nothing Then Range("AP32").Select
Case 75: If Not Intersect(Range("AM118"), Target) Is Nothing Then Range("AP32").Select
End Select
End If
End Select

End Sub

 

End Sub
[/QUOTE]
Vielen Dank Anton
Das funktioniert Bestens
habe auch für 4 Seiten erstellt und es funktioniert auch.
Wünsche dir schönen Tag noch.
Mit freundlichen Grüßen
Andre