Hi Mike,
I ran the code you supplied and it worked fine no error
messages, however I ran it a second time and it didn't find the store number
that was already within the list and allowed me to enter it a second time.
Not ideal
I've never used the each c in Range technique before so don't really
understand the meaning of it. but hey I'm here to learn....

I can confirm that StoreNo is difined as a standard Module level variable

Any ideas

Regards
"Mike H" wrote:

Hi,

It's doing that because it can't find storeno in the range. On the
assumption storeno is defined you could look for it in a different way

For Each c In Range("B2:B2000")
If c.Value = storeno Then
MsgBox "This Store Number already exists," & vbNewLine _
& "Please try an alternative Number", vbOKOnly + vbQuestion, _
"Store Number already exists"
Exit For
End If
Next

Mike

"leerem" wrote:

Hi all,
I have a problem, that Iam racking my head over with no light at
the end of the tunnel, I've overlooked something which I obviously can't see.
I have the lines of code as listed below.
I need to check if the store number being added to the list already exists
before continuing. if the store number does exist the code works fine. The
problem i have is if the store number is not in the current list I get the
error message as listed below. What am I missing? Do I need to adjust the
code somehow?

Please help!

If Application.WorksheetFunction.Vlookup(StoreNo, Sheets("Store No's"). _
Range("B2:B2000"), 1, False) = StoreNo Then

MsgBox "This Store Number already exists," & vbNewLine _
& "Please try an alternative Number", vbOKOnly + vbQuestion, _
"Store Number already exists"
Unload Me
UserForm10.Show

Else More code

End If

I get the Error Message "unable to get the Vlookup property of the
WorksheetFunction Class"

Regards

Lee