Type mismatch error in Excel

Hello,
I receive a type-mismatch error when compiling. The cells being
referenced only have dates. Can someone help? Heres some of the code

Application.Windows("rpt_BigTicket.xls").Activate
a = Range("D:J")
b = DateValue(Format(Now(), "mm-dd-yyyy")) - Weekday(Now(), 1)
c = Application.VLookup(b, a, 2, False) "This is where the ERROR-13
'Tpye Mismatch occurs'

The default property for a range is the .Value property, so

a = Range("D:J") is returning an array of values, not a range object.

Try something like:

Dim a As Range
Dim b As Long
Dim c As Variant
Application.Windows("rpt_BigTicket.xls").Activate
Set a = ActiveSheet.Range("D:J")
b = CLng(Date - WeekDay(Date, 1))
c = Application.VLookup(b, a, 2, False)

In article <[email protected]>,
"[email protected]" <[email protected]> wrote:

> Hello,
> I receive a type-mismatch error when compiling. The cells being
> referenced only have dates. Can someone help? Heres some of the code
>
> Application.Windows("rpt_BigTicket.xls").Activate
> a = Range("D:J")
> b = DateValue(Format(Now(), "mm-dd-yyyy")) - Weekday(Now(), 1)
> c = Application.VLookup(b, a, 2, False) "This is where the ERROR-13
> 'Tpye Mismatch occurs'