Solved: Vlookup on todays date

[rossmiddleto]

I am trying to obtain a value in a table based on todays date and save that value in the string variable 'unit' with the following code:

[vba]
Sub test2()
Dim rng As Range
Dim unit As String
Dim target As Date
target = Format(Date, "dd/mm/yyy")
Set rng = Sheets("CMTD_CALC").Columns("A:C")
unit = WorksheetFunction.VLookup(target, rng, 3, False)
End Sub
[/vba]

The error that I get is:

'Invalid procedure or call argument'

Any help would be more than appreciated.

Thanks

[p45cal]

[vba]Sub test2()
Dim rng As Range
Dim unit 'As String
Dim mytarget As Long
mytarget = Date
Set rng = Sheets("CMTD_CALC").Columns("A:C")
unit = Application.WorksheetFunction.VLookup(mytarget, rng, 3, False)
End Sub
[/vba]
I used mytarget instead of target as it could lead to confusion (in humans) with Target as in sheet event handlers.
I commented out the As String for the result as in Excel you never know what data type is in a cell. However, reinstate it if you're sure it'll alway be a string or you specifically want it to be a string.
The important bit is the As Long to convert the date implicitly into a Long.

[rossmiddleto]

Massive thank you!

I am using a whole range of vlookups in my workbook and all are working great now that I have made your advised changes.

Thanks again!