Type mismatch error in Excel

[r_fordjr]

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'

 

[JE McGimpsey]

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)