PDA

View Full Version : Solved: Vlookup on todays date



rossmiddleto
01-19-2011, 08:25 AM
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:


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


The error that I get is:

'Invalid procedure or call argument'

Any help would be more than appreciated.

Thanks

p45cal
01-19-2011, 11:07 AM
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

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
01-20-2011, 03:30 AM
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!