VBA preventing adding a name to a list if the name is already in the list

[brianfosterblack]

I use the following code to enter a name selected from a listbox to the end of a list of names named "nameList"
I please need some help to prevent the name being added if it already appears in the list and a messagebox to appear advising that the name was not added because it is already in the list, can someone please help. After the messagebox appears, the code will continue running - it must not exit the subroutine.
> Application.Goto Reference:="R1000C3"
Range(ActiveCell, ActiveCell.End(xlUp)).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Unprotect
ActiveCell.FormulaLocal = ListBox1.Value <

 

[Anthony47]

Re: VBA preventuing adding a name to a list if the name is already in the list

For example:

If Application.WorksheetFunction.CountIf(Range("nameList"), ListBox1.Value) = 0 Then
    ActiveCell.FormulaLocal = ListBox1.Value
Else
    'What to do if it already Exists? I don't know
End If

Consider replacing all your code with

If Application.WorksheetFunction.CountIf(Range("nameList"), ListBox1.Value) = 0 Then
    lastc = Cells(Rows.Count, "C").End(xlUp).Row
    ActiveSheet.Unprotect
    Cells(lastc + 1, "C").FormulaLocal = ListBox1.Value
Else
    'What to do if it already Exists? I don't know
End If

Bye

 

[brianfosterblack]

Re: VBA preventuing adding a name to a list if the name is already in the list

For example:

If Application.WorksheetFunction.CountIf(Range("nameList"), ListBox1.Value) = 0 Then
    ActiveCell.FormulaLocal = ListBox1.Value
Else
    'What to do if it already Exists? I don't know
End If

Consider replacing all your code with

If Application.WorksheetFunction.CountIf(Range("nameList"), ListBox1.Value) = 0 Then
    lastc = Cells(Rows.Count, "C").End(xlUp).Row
    ActiveSheet.Unprotect
    Cells(lastc + 1, "C").FormulaLocal = ListBox1.Value
Else
    'What to do if it already Exists? I don't know
End If

Bye

Thank you - my coding comes from a bygone era and I have replaced it with your suggestion which works perfectly. thank you.