WorksheetFunction.Vlookup

[leerem]

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

 

[Gary''s Student]

So where is StoreNo initially defined??

 

[leerem]

StoreNo is initally difined at the top of the module as an integer and given
its value from the textbox1.value eg.

StoreNo = TextBox1.Value

 

[Mike H]

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]

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

 

[leerem]

Hi Mike

False Alarm... ran it a second time after modify the code slightly
to identify the range correctly eg what sheet its on. and it worked perfectly.

many thanks for your assistance

Lee

 

[Dave Peterson]

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.

 

[Vijay Chary]

Hi Leerem !!
Assuming that the variable 'StoreNo' does have a
value assigned to it, the Vlookup worksheet function gives an error if it
cannot find this value in the table array !! To get around this kind of error
you'll have to specify a condition If(Not(IsErr(Vlookup...)),Vlookup(...

D'you think that helps !

Vijay

 

[ShaneDevenshire]

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.