Pass value from Excel 2016 userform textbox to a named range if not already in range

Question

I have an Excel 2016 Userform that contains a textbox and command button. I want to be able to type a name or names in the textbox and have the userform add them to a named range after checking for duplicates. If the name is already in the named range I want the name to be added to my MsgAdd string and to proceed to the next line of the textbox (if appl.).

***New Attempt: This is my first time attempting to use a dictionary. When I attempt to use .Add instead of .Item I get an error message for the value already existing. The dictionary should be empty at the start of the macro? My named range is looped through and added. Then dict.exist should trigger, if the value exists it should add to my msg string and if not it should be added to the bottom of the named range. However, the value is now adding to "A2", instead of at end of range and overwriting itself if there is more than one line in the textbox.

Private Sub AddAnalyst()
' Select Tools->References from the Visual Basic menu.
' Check box beside "Microsoft Scripting Runtime" in the list.
Dim ws          As Worksheet
Dim i           As Long
Dim FreeRow     As String
Dim TBLines()   As String
Dim MsgAdd      As String
Dim xFound      As Integer
Dim Cell        As Range
Dim Rng         As Range
Dim dict        As Object

Set Rng = Range("Name")

'Build Dictionary
Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare  'Capitalization does not apply

    For Each Cell In Rng.Cells 'Loop through range & add to dictionary
        dict.Item(Cell.Value) = Cell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    Next Cell

    TBLines = Split(Add_Analyst_Form.AddAnalystTB.Text, vbCrLf)

    For i = LBound(TBLines) To UBound(TBLines)

        If dict.Exists(i) Then 'Add to message string for end msgbox
            xFound = xFound + 1
            MsgAdd = MsgAdd & vbCrLf & UBound(TBLines, i)
        Else
            With ws
                FreeRow = WorksheetFunction.CountA(Range("A:A")) + 1
                Sheets("Lists").Range("A" & FreeRow) = TBLines(i)
            End With
        End If
    Next i

If xFound <> 0 Then MsgBox ("Analyst(s)," & MsgAdd & ", is/are already entered into the database and will not be added.") 'msg name already exists

Set dict = Nothing   
End Sub

Previously tried (prior to dictionary):

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''   
Private Sub AddAnalyst()
Dim ws             As Worksheet
Dim i              As Long
Dim FreeRow        As String
Dim TBLines()      As String
Dim MsgAdd         As String
Dim sFind          As String
Dim rFound         As Range
Dim valueFound     As Integer

TBLines = Split(Add_Analyst_Form.AddAnalystTB.Text, vbCrLf) 

For i = LBound(TBLines) To UBound(TBLines) 'Cycle through all lines of the textbox

    On Error Resume Next 'Skip error that will occur if rFound does not exist.
    sFind = UBound(TBLines, i)
    Set rFound = Sheets("Lists").Range("Name").Find(sFind, LookIn:=xlValues, LookAt:=xlWhole)

    If Not rFound Is Nothing Then 'Add value to string for later MsgBox & increase integer
        valueFound = valueFound + 1
        MsgAdd = MsgAdd & vbCrLf & UBound(TBLines, i)
        GoTo NextIteration
    Else
        With ws 'Name is not duplicated in range, add to range.
            FreeRow = WorksheetFunction.CountA(Range("A:A")) + 1
            Sheets("Lists").Range("A" & FreeRow) = TBLines(i)
        End With
    End If
NextIteration:
Next i

'Msgbox will be displayed if 1 or more of the values previously existed.
If valueFound <> 0 Then MsgBox ("Analyst(s)," & MsgAdd & ", is/are already entered into the database and will not be added.") 'msg name already exists

End Sub

My script does not seem to be checking for duplicates. It just automatically adds to the bottom of my named range. I think it is due to my On Error Resume but I cannot seem to find a way around it. If anyone has some input, it would be appreciated.

Answer 1

For anyone else working on something similar. Fully running after adding the dictionary and working out a few other kinks.

Private Sub AddAnalyst()
' Select Tools->References from the Visual Basic menu.
' Check box beside "Microsoft Scripting Runtime" in the list.
Dim ws          As Worksheet
Dim i           As Integer
Dim FreeRow     As String
Dim TBLines()   As String
Dim MsgAdded    As String
Dim MsgExist    As String
Dim xFound      As Integer
Dim yFound      As Integer
Dim Cell        As Range
Dim dict        As Scripting.Dictionary

'Build Dictionary
Set dict = New Scripting.Dictionary
    dict.CompareMode = vbTextCompare  'Capitalization does not apply to dictionary

    For Each Cell In Range("Name").Cells 'Add named range to dictionary
        With Cell
            dict(Cell.Value) = Cell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
        End With
    Next Cell

    TBLines = Split(Add_Analyst_Form.AddAnalystTB.Text, vbCrLf) 'Split string when there are multiple lines

    For i = LBound(TBLines) To UBound(TBLines) 'Loop through split string
        If dict.Exists(TBLines(i)) Then
            xFound = xFound + 1
            MsgExist = MsgExist & vbCrLf & TBLines(i)
        Else
            With Sheets("Lists")
                FreeRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 'First free row in Column A of Reasoning&Lists sheet
                .Range("A" & FreeRow) = TBLines(i)
            End With
            yFound = yFound + 1
            MsgAdded = MsgAdded & vbCrLf & TBLines(i)
        End If
    Next i
Set dict = Nothing

Unload Add_Analyst_Form 'Close out userform

If xFound <> 0 And yFound <> 0 Then
    MsgBox ("Analyst(s):" & MsgExist & vbCrLf & "already exists in the database and will not be added." & vbCrLf & vbCrLf & "Analyst(s):" & MsgAdded & vbCrLf & "have been added to the database.")
ElseIf xFound <> 0 And yFound = 0 Then
    MsgBox ("Analyst(s):" & MsgExist & vbCrLf & "already exists in the database and will not be added.") 'msg name already exists
ElseIf xFound = 0 And yFound <> 0 Then
    MsgBox ("Analyst(s):" & MsgAdded & vbCrLf & "have been added to the database.") 'msg name was added to database
End If

End Sub