kann mir jemand mit dem unten stehenden Code behilflich sein?
Ich habe ihn für meine Bedürfnisse angepasst und bekomme immer die folgende Fehlermeldung:
Fehler beim kompilieren:
Falsche Anzahl an Argumenten oder ungültige Zuweisung zu einer Eigenschaft.
Der Fehler liegt irgendwo in Zeile 15 oder drunter...in der Zeile "Application.Union(Z.Cells(3-s), _"...eben dort, wo die ganzen spalten aufgelistet werden. Oder sind's einfach zu viele?
Viele Grüße
vom Jake.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim B As Range
Dim S As Integer
Dim Wert As Single
Dim Z As Range
If Not Application.Intersect(Target.Cells(1), Me.Columns("A:B")) Is Nothing _
And Target.Row >= 10 Then
Application.EnableEvents = False
Application.ScreenUpdating = False
On Error GoTo BeiFehler
S = Target.Column
For Each Z In Target.Rows.EntireRow
Application.Union(Z.Cells(3 - S), _
Z.Cells(4), Z.Cells(5), Z.Cells(7), Z.Cells(8), _
Z.Cells(10), Z.Cells(11), Z.Cells(13), Z.Cells(14), _
Z.Cells(16), Z.Cells(17), Z.Cells(19), Z.Cells(20), _
Z.Cells(22), Z.Cells(23), Z.Cells(25), Z.Cells(26), _
Z.Cells(28), Z.Cells(29), Z.Cells(31), Z.Cells(32), _
Z.Cells(34), Z.Cells(35), Z.Cells(37), Z.Cells(38), _
Z.Cells(40), Z.Cells(41), Z.Cells(43), Z.Cells(44), _
Z.Cells(46), Z.Cells(47), Z.Cells(49), Z.Cells(50), _
Z.Cells(52), Z.Cells(53), Z.Cells(55), Z.Cells(56), _
Z.Cells(58), Z.Cells(59), Z.Cells(61), Z.Cells(62), _
Z.Cells(64), Z.Cells(65), Z.Cells(67), Z.Cells(68), _
Z.Cells(70), Z.Cells(71), Z.Cells(73), Z.Cells(74)) = ""
Wert = Z.Cells(S).Value
Select Case Z.Cells(S).Value
Case Is = 1
If S = 1 Then
Application.Union(Z.Cells(4), Z.Cells(7)) = Wert
ElseIf S = 2 Then
Application.Union(Z.Cells(4), Z.Cells(10)) = Wert
End If
Case Is = 2
If S = 1 Then
Application.Union(Z.Cells(5), Z.Cells(8), Z.Cells(13), Z.Cells(16)) = Wert
ElseIf S = 2 Then
Application.Union(Z.Cells(5), Z.Cells(11), Z.Cells(13), Z.Cells(19)) = Wert
End If
Case Is = 3
If S = 1 Then
Application.Union(Z.Cells(5), Z.Cells(8), Z.Cells(14), Z.Cells(17), Z.Cells(22), Z.Cells(25)) = Wert
ElseIf S = 2 Then
Application.Union(Z.Cells(5), Z.Cells(11), Z.Cells(14), Z.Cells(20), Z.Cells(22), Z.Cells(28)) = Wert
End If
Case Is = 4
If S = 1 Then
Application.Union(Z.Cells(5), Z.Cells(8), Z.Cells(14), Z.Cells(17), Z.Cells(23), Z.Cells(26), Z.Cells(31), Z.Cells(34)) = Wert
ElseIf S = 2 Then
Application.Union(Z.Cells(5), Z.Cells(11), Z.Cells(14), Z.Cells(20), Z.Cells(23), Z.Cells(29), Z.Cells(31), Z.Cells(37)) = Wert
End If
Case Is = 5
If S = 1 Then
Application.Union(Z.Cells(5), Z.Cells(8), Z.Cells(14), Z.Cells(17), Z.Cells(23), Z.Cells(26), Z.Cells(32), Z.Cells(35), Z.Cells(40), Z.Cells(43)) = Wert
ElseIf S = 2 Then
Application.Union(Z.Cells(5), Z.Cells(11), Z.Cells(14), Z.Cells(20), Z.Cells(23), Z.Cells(29), Z.Cells(32), Z.Cells(38), Z.Cells(40), Z.Cells(46)) = Wert
End If
Case Is = 6
If S = 1 Then
Application.Union(Z.Cells(5), Z.Cells(8), Z.Cells(14), Z.Cells(17), Z.Cells(23), Z.Cells(26), Z.Cells(32), Z.Cells(35), Z.Cells(41), Z.Cells(44), Z.Cells(49), Z.Cells(52)) = Wert
ElseIf S = 2 Then
Application.Union(Z.Cells(5), Z.Cells(11), Z.Cells(14), Z.Cells(20), Z.Cells(23), Z.Cells(29), Z.Cells(32), Z.Cells(38), Z.Cells(41), Z.Cells(47), Z.Cells(49), Z.Cells(55)) = Wert
End If
Case Is = 7
If S = 1 Then
Application.Union(Z.Cells(5), Z.Cells(8), Z.Cells(14), Z.Cells(17), Z.Cells(23), Z.Cells(26), Z.Cells(32), Z.Cells(35), Z.Cells(41), Z.Cells(44), Z.Cells(50), Z.Cells(53), Z.Cells(58), Z.Cells(61)) = Wert
ElseIf S = 2 Then
Application.Union(Z.Cells(5), Z.Cells(11), Z.Cells(14), Z.Cells(20), Z.Cells(23), Z.Cells(29), Z.Cells(32), Z.Cells(38), Z.Cells(41), Z.Cells(47), Z.Cells(50), Z.Cells(56), Z.Cells(58), Z.Cells(64)) = Wert
End If
Case Is = 8
If S = 1 Then
Application.Union(Z.Cells(5), Z.Cells(8), Z.Cells(14), Z.Cells(17), Z.Cells(23), Z.Cells(26), Z.Cells(32), Z.Cells(35), Z.Cells(41), Z.Cells(44), Z.Cells(50), Z.Cells(53), Z.Cells(59), Z.Cells(62), Z.Cells(67), Z.Cells(70)) = Wert
ElseIf S = 2 Then
Application.Union(Z.Cells(5), Z.Cells(11), Z.Cells(14), Z.Cells(20), Z.Cells(23), Z.Cells(29), Z.Cells(32), Z.Cells(38), Z.Cells(41), Z.Cells(47), Z.Cells(50), Z.Cells(56), Z.Cells(59), Z.Cells(65), Z.Cells(67), Z.Cells(73)) = Wert
End If
Case Is > 8
If S = 1 Then
Application.Union(Z.Cells(5), Z.Cells(8), Z.Cells(14), Z.Cells(17), Z.Cells(23), Z.Cells(26), Z.Cells(32), Z.Cells(35), Z.Cells(41), Z.Cells(44), Z.Cells(50), Z.Cells(53), Z.Cells(59), Z.Cells(62), Z.Cells(68), Z.Cells(71)) = Wert
ElseIf S = 2 Then
Application.Union(Z.Cells(5), Z.Cells(11), Z.Cells(14), Z.Cells(20), Z.Cells(23), Z.Cells(29), Z.Cells(32), Z.Cells(38), Z.Cells(41), Z.Cells(47), Z.Cells(50), Z.Cells(56), Z.Cells(59), Z.Cells(65), Z.Cells(68), Z.Cells(74)) = Wert
End If
End Select
Next Z
End If
BeiFehler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub