What's wrong with this?
 

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

What's wrong with this?
 

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


"Damien McBain" wrote:

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

What's wrong with this?
 

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 wrote:

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

--

Dave Peterson

What's wrong with this?
 

Thanks guys,

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

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

Nothing worked until I made that change

thanks for steering me in the right direction

Damo

"Dave Peterson" wrote in message
...
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 wrote:

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

--

Dave Peterson

What's wrong with this?
 

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

Damien McBain wrote:

Thanks guys,

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

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

Nothing worked until I made that change

thanks for steering me in the right direction

Damo

"Dave Peterson" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

What's wrong with this?
 

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

Thanks Dave,

cheers

Damo

"Dave Peterson" wrote in message
...
I bet that application.match() would work, too--with your other changes.

Damien McBain wrote:

Thanks guys,

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

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

Nothing worked until I made that change

thanks for steering me in the right direction

Damo

"Dave Peterson" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson