What's wrong with this?

[Damien McBain]

LevBR = Application.WorksheetFunction.VLookup(daDate, Range("A5:A35"), 1,
False)

where LevBR is a declared variable
and daDate is a declared (as Range) variable which has been set
(with Set daDate = Worksheets("Main").Range("date"))

I get the "Unable to get the VLookup property of the WorksheetFunction
class" error

 

[Guest]

Hi Damien
Try this, I manually defined the range 'date' in "D5":
Sub testit()
Dim LevBR, dadate As Range
Set dadate = Worksheets("Main").Range("date")
On Error Resume Next
LevBR = Application.WorksheetFunction.VLookup(dadate, Range("A5:A35"),
1, False)
If Not LevBR = 0 Then
MsgBox "Date is " & dadate
Else
MsgBox "Not Found"
End If
End Sub

HTH

Geoff

 

[Dave Peterson]

If you drop the .worksheetfunction, you can use something like:

dim levBR as Variant 'could return an error
levbr = application.vlookup(dadate,range("a5:a35"),1,false)
if iserror(levbr) then
msgbox "not found"
else
msgbox "found"
end if

==
If you want to keep the .worksheetfunction, you have to trap that error:

dim levbr as date
on error resume next
levbr = application.vlookup(dadate,range("a5:a35"),1,false)
if err.number <> 0 then
msgbox "not found"
err.clear
else
msgbox "Found"
end if
on error goto 0

==========
But if I were only looking to see if it's there, I'd use application.match()

dim levBR as Variant 'could return an error
levbr = application.match(dadate,range("a5:a35"),0)
if iserror(levbr) then
msgbox "not found"
else
msgbox "found"
end if

And sometimes VBA and dates don't play nice. Sometimes this works better:


dim levBR as Variant 'could return an error
levbr = application.match(clng(dadate),range("a5:a35"),0)
if iserror(levbr) then
msgbox "not found"
else
msgbox "found"
end if

And I'd be more specific about what worksheet to look at:

levbr = application.match(clng(dadate),worksheets("sheet1").range("a5:a35"),0)

=======

And one more option...

if application.countif(worksheets("sheet1").range("a5:a35"),dadate) > 0 then
'found it
else
'not found
end if

 

[Damien McBain]

Thanks guys,

I had to refer to the worksheet in the vlookup like:

levbr =
application.vlookup(dadate,WORKSHEETS("SHEET1").range("a5:a35"),1,false)

Nothing worked until I made that change

thanks for steering me in the right direction

Damo

 

[Dave Peterson]

I bet that application.match() would work, too--with your other changes.

 

[Damien McBain]

I've copied it into my little cache of handy code for ron (ie lateron)

Thanks Dave,

cheers

Damo