Returning the wrong values...but not in the Immediate window?

[johnhildreth]

I am trying to use the following code to capture a couple of dates from
cells.

dStart = Application.WorksheetFunction.VLookup(c.Offset(0,
-COff).Value, _
Sheets("Update").Range(Cells(11, 2), Cells(LastActivity,
18)), 2, False)
dEnd = Application.WorksheetFunction.VLookup(c.Offset(0,
-COff).Value, _
Sheets("Update").Range(Cells(11, 2), Cells(LastActivity,
18)), 3, False)

Both dStart and dEnd are dimensioned as Dates and the cells to which
they reference contain dates. The references work fine. In fact, from
the Immediates Window:

?Application.WorksheetFunction.VLookup(c.Offset(0,
-COff).Value, _
Sheets("Update").Range(Cells(11, 2),
Cells(LastActivity, 18)), 3, False)
38825
?Application.WorksheetFunction.VLookup(c.Offset(0,
-COff).Value, _
Sheets("Update").Range(Cells(11, 2),
Cells(LastActivity, 18)), 2, False)
38813

The problem is that the values assigned to dStart and dEnd are
0....that is, #12:00:00 AM#. I've tried dimensioning them as integers
(which should have produced an overflow error) and variants. The
assigned values are always 0. So I don't think it is a data type
mismatch.

The dates I am trying to retrieve are calculated via formulas. But it
seems to me that is not an issue since I get the dateserial in the
immediates window. It may also be important to note that the sheet
from which the dates are being read is protected. The cells for dStart
are unlocked and dEnd cells are locked. I think I can rule out a
protection issue, since I get 0 for both.

I've hurt my little brain on this one. ANY help is greatly
appreciated.

Thanks,
John

 

[WhytheQ]

should the bit of code:
Sheets("Update").Range(Cells(11, 2), Cells(LastActivity,18))

read more like:
Sheets("Update").Range(Sheets("Update").Cells(11, 2),
Sheets("Update").Cells(LastActivity,18))


?
J

 

[johnhildreth]

Sheets("Update").Range(Cells(11, 2), Cells(LastActivity,18)) defines
the table array needed for the vlookup function.

I'm looking for {c.Offset(0,-COff).Value}, which is a text string, in
the first column of {Sheets("Update").Range(Cells(11, 2),
Cells(LastActivity,18))} and returning the date contained in the 2nd
(for dStart and 3rd for dEnd) column of that table array.

Thanks,
John