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

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

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

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" <ec35720@netscapeXSPAM.com> wrote in message
news:42A21F5D.7AB4BC90@netscapeXSPAM.com...
> 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

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").range("a5:a35"),1,false)
>
> Nothing worked until I made that change
>
> thanks for steering me in the right direction
>
> Damo
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> news:42A21F5D.7AB4BC90@netscapeXSPAM.com...
> > 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

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

Thanks Dave,

cheers

Damo

"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:42A5979F.AA75E582@netscapeXSPAM.com...
>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").range("a5:a35"),1,false)
>>
>> Nothing worked until I made that change
>>
>> thanks for steering me in the right direction
>>
>> Damo
>>
>> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
>> news:42A21F5D.7AB4BC90@netscapeXSPAM.com...
>> > 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