|
Oder für schnelle Eingaben mit Enter als auslöser und ohne command button:
Option Explicit
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 13
Dim rng As Range, AV, R&, C&, LR&, S$
Dim bereitsVorhanden As Boolean
Dim tSh As Worksheet
'set
S = TextBox1.Text
Set tSh = ActiveSheet
With tSh
LR = .Cells(Rows.Count, 1).End(xlUp).Row
Set rng = .Range(.Cells(1, 1), .Cells(LR, 2))
End With
AV = rng.Value
'nach vorhandenen suchen
For R = 1 To UBound(AV)
For C = 1 To UBound(AV, 2)
If AV(R, C) = S Then
tSh.Cells(R, 2) = AV(R, 2) + 1
bereitsVorhanden = True
End If
Next
Next
'neue einfügen
If Not bereitsVorhanden Then
If Not AV(1, 2) = "" Or Not LR = 1 Then LR = LR + 1
With tSh
.Cells(LR, 1) = TextBox1.Value
.Cells(LR, 2) = 1
End With
End If
'reset textbox
TextBox1 = vbNullString
End Select
End Sub
|