Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Select Case Target.Value
Case "Meier"
Target.Value = 123456 'hier die Mitgl.nr. von Meier
Case "Müller"
Target.Value = 112233 'hier die Mitgl.nr. von Müller
Case "Schulze"
Target.Value = 44789 'hier die Mitgl.nr. von Schulze
End Select
Application.EnableEvents = True
End If
End Sub
Option Explicit 'wenn nur die Nummer ausgegeben werden soll '****************************************************************************************** ''wenn Name und Nummer ausgegeben werden soll '****************************************************************************************** Private Sub Worksheet_Change(ByVal Target As Range) Dim Bereich As Integer Dim lngLastRow As Long If Target.Column = 1 And Selection.Count = 1 Then Application.EnableEvents = False With Sheets("Tabelle2") lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row On Error Resume Next Bereich = Application.Match(Target, .Range(.Cells(1, 1), .Cells(lngLastRow, 1))) Target = Target & " " & .Cells(Bereich, 2) End With Application.EnableEvents = True End If End Sub'Dim Bereich As Integer 'Dim lngLastRow As Long 'If Target.Column = 1 And Selection.Count = 1 Then ' Application.EnableEvents = False ' With Sheets("Tabelle2") ' lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row ' On Error Resume Next ' Bereich = Application.Match(Target, .Range(.Cells(1, 1), .Cells(lngLastRow, 1))) ' Target = .Cells(Bereich, 2) ' End With ' Application.EnableEvents = True 'End If 'End SubPrivate Sub Worksheet_Change(ByVal Target As Range)
