Hi,

The standard approach when searching is to use code such as the sample shown
below:

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

Of course you would adjust this to meet your needs.

--
Thanks,
Shane Devenshire


"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