First, since you're only trying to check to see if the value exists, I'd use
=match(). (But =vlookup() will work.)

Dim res as variant 'could return an error
res = application.match(storeno, sheets("store no's").range("b2:b2000"),0)
if iserror(res) then
'doesn't exist, do what you want here
else
'already exists, do that branch.
End if

===========
The difference is that I didn't include .worksheetfunction in my code. If I use
that, then both =match() and =vlookup() will cause runtime errors.

If you wanted to use .worksheetfunction, you could do something like:

dim res as variant
on error resume next
res = application.worksheetfunction.match _
(storeno, sheets("store no's").range("b2:b2000"),0)
if err.number < 0 then
err.clear
'not there processing
else
'already exists processing
end if
on error goto 0

I find using the application.vlookup or application.match easier to code.




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

--

Dave Peterson