Vba Vlookup with date returns a type mismatch error

I'm trying to find a value with a Vlookup function in VBA but I get a type mismatch error on the msgbox line.

The lookup_value is tomorrow

Here is the code

Sub FINDDATA()
Dim Giov As Date
Dim ImpPag As Variant

Giov = Format(Date + 1, "Short Date")
ImpPag = Application.VLookup(Giov, Range("A5:B9"), 2, False)
MsgBox ImpPag
End Sub

And here is the array

date        value
05-mar-15   €1.000,00 
12-mar-15   €2.000,00 
19-mar-15   €3.000,00 
26-mar-15   €4.000,00

Can I have a little help? thanks

Solution

In Excel, dates are usually stored as a number and only shown as a string in formatting. You can check this by changing the number formatting on the date cells in your spreadsheet.

In this case, the date you are looking up (05/03/2015) is actually stored as 42068. If you declare Giov as a Long data type you can pass the current date + 1 into the lookup. I have added a msgbox so you can see the stored date as a Long Integer before doing the lookup:

Sub FINDDATA()
Dim Giov As Long
Dim ImpPag As Variant

Giov = Date + 1
MsgBox Giov
ImpPag = Application.VLookup(Giov, Range("A5:B9"), 2, False)
MsgBox ImpPag
End Sub