Worksheet_Change
06.05.2019 11:13:10
Franky
gibt es eine Möglichkeit diese beiden Worksheet_Change zu verbinden,
oder die Möglichkeit auf zwei verschiedene Sheets zuzugreifen?
Private Sub (ByVal Target As Range)
Dim var As Variant
If Target.Column 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
On Error GoTo ERRORHANDLER
With Worksheets("CC")
If Not IsEmpty(Target) Then
var = Application.Match(Target.Value, .Columns(1), 0)
If Not IsError(var) Then
Target.Offset(0, 4).Value = .Cells(var, 3).Value
Target.Offset(0, 8).Value = .Cells(var, 5).Value
Target.Offset(0, 9).Value = .Cells(var, 6).Value
Target.Offset(0, 10).Value = .Cells(var, 7).Value
Target.Offset(0, 11).Value = .Cells(var, 8).Value
Target.Offset(0, 12).Value = .Cells(var, 10).Value
Target.Offset(0, 13).Value = .Cells(var, 9).Value
Target.Offset(0, 14).Value = .Cells(var, 11).Value
End If
Else
Range(Target, Target.Offset(0, 17)).ClearContents
End If
End With
ERRORHANDLER:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim var As Variant
If Target.Column 5 Then Exit Sub
If Target.Cells.Count > 5 Then Exit Sub
Application.EnableEvents = False
On Error GoTo ERRORHANDLER
With Worksheets("Test")
If Not IsEmpty(Target) Then
var = Application.Match(Target.Value, .Columns(1), 0)
If Not IsError(var) Then
Target.Offset(0, 1).Value = .Cells(var, 2).Value
Target.Offset(0, 2).Value = .Cells(var, 4).Value
Target.Offset(0, 3).Value = .Cells(var, 5).Value
End If
Else
Range(Target, Target.Offset(0, 7)).ClearContents
End If
End With
ERRORHANDLER:
Application.EnableEvents = True
End Sub
Wünsche Euch einen schöne Start in die neue Woche.
Gruß Frank