Try this:
Take out the line
Dim c as String

(if you've got Option Explicit active then replace it with
Dim c as Date)

Tweak
z = WorksheetFunction.VLookup(c, Sheets("Consolidate").Range("A3:I1000"), 3,
False)

to:
z = WorksheetFunction.VLookup(CLng(c),Sheets("Consolidate").Range("A3:I1000"),
3, False)

It took quite a long time to find  this solution, and it seems that excel
considers the Sheets("Consolidate").Range("A3:I1000") part  of the vlookup  to be
an array of numbers even if they are dates.