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

On 16 Aug, 11:55, "
wrote:
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)

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


--
Regards,

Halim



" wrote:

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