Ensure items in a list aren't entered twice

[martin.a.john]

Hi

I am using a user form so that users can enter a new customer into a
small program I am creating. I want to ensure that if the customer
already exists that it doesn't get added again. The code I have
created is as follows:
' ************ ADD CODE TO CHECK IF THE CUSTOMER IS ALREADY EXISTING
********
' IF EXISTING DISPLAY MSGBOX "CUTOMER ALREADY EXISTS, PLEASE PRESS
CANCEL AND LOOKUP THE CUSTOMER CODE"
'================================================================
Sheets("CompanyList").Select
Range("C:C").Select

Set rngFound = Selection.Find(What:=CompanyNameEnglish,
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False)

If rngFound.Value = CompanyNameEnglish Then
MsgBox "Customer already exists, please lookup customer
code"

This works if the customer already is in the list and the msgbox is
displayed. However, when a new customer is entered the program will
not pass the line with the set comand. I have looked through the help
file and found that the sytanx should include " | Nothing" at the end
of the set statement to clear the rngFound.

However, when the program runs it states that "|" is an invalid
characer.

How can I get the set method to blank if the item is not found so the
rest of the program can continue?

Thank,

Martin

 

[aidan.heritage]

Hi

I am using a user form so that users can enter a new customer into a
small program I am creating. I want to ensure that if the customer
already exists that it doesn't get added again. The code I have
created is as follows:
' ************ ADD CODE TO CHECK IF THE CUSTOMER IS ALREADY EXISTING
********
' IF EXISTING DISPLAY MSGBOX "CUTOMER ALREADY EXISTS, PLEASE PRESS
CANCEL AND LOOKUP THE CUSTOMER CODE"
'================================================================
Sheets("CompanyList").Select
Range("C:C").Select

Set rngFound = Selection.Find(What:=CompanyNameEnglish,
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False)

If rngFound.Value = CompanyNameEnglish Then
MsgBox "Customer already exists, please lookup customer
code"

This works if the customer already is in the list and the msgbox is
displayed. However, when a new customer is entered the program will
not pass the line with the set comand. I have looked through the help
file and found that the sytanx should include " | Nothing" at the end
of the set statement to clear the rngFound.

However, when the program runs it states that "|" is an invalid
characer.

How can I get the set method to blank if the item is not found so the
rest of the program can continue?

Thank,

Martin

Why worry about the find command at all - why not use the
worksheetfunction.countif command - this will count the number of
instances - if it's zero, new customer, else look up code (which by
the way you could code in as well, rather than getting the user to
make the correction)

 

[Guest]

Martin,

Try to replace your code:
If rngFound.Value = CompanyNameEnglish Then
MsgBox "Customer already exists, please lookup customer"
End If

to:

If Not rngFound Is Nothing Then
If rngFound.Value = CompanyNameEnglish Then
MsgBox "Customer already exists, please lookup customer"
End If
End If

''''Not rngFound Is Nothing is to do If rngFound.Value while found, _
if not found there error occured, cause of empty object assignment