Gruppe
Ereignis
Problem
Wird in Zeile 1 ein Wert aus Tabelle Data, Spalte A und in Zeile 2 ein Wert aus Spalte Data, Spalte B eingesetzt, soll der Wert in Zeile 1 in Klammern gesetzt werden.
ClassModule: Tabelle1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet
Dim iCol As Integer
Dim sText As String
If Target.Row > 2 Then Exit Sub
Set wks = Worksheets("Data")
iCol = Target.Column
Application.EnableEvents = False
On Error GoTo ERRORHANDLER
If Not IsError(Application.Match(Cells(1, iCol).Value, wks.Columns(1), 0)) And _
Not IsError(Application.Match(Cells(2, iCol).Value, wks.Columns(2), 0)) Then
Cells(1, iCol) = "(" & Cells(1, iCol).Value & ")"
End If
sText = Cells(1, iCol)
sText = Replace(sText, "(", "")
sText = Replace(sText, ")", "")
If Not IsError(Application.Match(sText, wks.Columns(1), 0)) And _
IsError(Application.Match(Cells(2, iCol).Value, wks.Columns(2), 0)) Then
Cells(1, iCol) = sText
End If
ERRORHANDLER:
Application.EnableEvents = True
End Sub