Handling Dates in VLookup

[eager_beaver]

Hi,

I'm having problems trying to match dates using VLookup in a Macro - any help will be much appreciated!

I have two worksheets. The first worksheet (Prices) has two columns; the first having a list of dates, and the second having corresponding prices (numbers). The second worksheet (cleanPrices) has a set of dates (overlapping partially with the dates in the Prices spreadsheet. I want to match up prices for each of the dates in the cleanPrices spreadsheet based on the prices mentioned in the Prices worksheet.

To make it clear:
Price worksheet has:
Date Price
7/7/2005 100.75
7/6/2005 98.50
7/1/2005 99.00

cleanPrices has
Date Price
7/6/2005
7/1/2005
6/30/2005

I want the Price colum in cleanPrices to have 99.50, 99.00 and "#NA"

I am using the following code (or something like this),

Dim r1 As Range
Dim x As Integer

Set r1 = Worksheets("Price").Range("A2:B5")

For x = 1 to 3
Worksheets("cleanPrices").Range("B1").Offset(x, 0).Value = Application.WorksheetFunction.VLookup(Worksheets("cleanPrices").Range("B1").Offset(x, 0).Value, Worksheets("Price").Range(r1),2, False)
Next

I have tried several combinations (used DateValue and the likes, but I am always getting errors.

Thanks!

[abcd]

Don't know but thinking with you:
First we may rewrite the same code, more clearly:

r1 = "A2:B5"
Range(r1)
or
set R1 = Worksheets("Price").Range("A2:B5")
(why naming a fixed a r1 variable and not really using it ?)

Then you could do the same with a r2 and
Worksheets("cleanPrices").Range("B1")

Then using a with:

with Application.WorksheetFunction
For x = 1 to 3
r2.Offset(x, 0).Value
.VLookup(r2.Offset(x, 0).Value, r1 ,2, False)
Next
end with

this is not so important for only a 3 cells loop, but this is a great
occasion to learn. This way is faster and easy to read (so to debug)


and then, you are getting errors... dates are not easy numbers to be
find: try to forget the r2.Offset(x, 0).Value and repalce it by
r2.Offset(x, 0) (put a range and not a value in the function)

So, excel will convert the proper way the date values

[Bob Phillips]

This works for me

Dim r1 As Range
Dim x As Long

Set r1 = Worksheets("Price").Range("A2:B5")

For x = 2 To 4
With Worksheets("cleanPrices")
.Range("B" & x).Value = Application.VLookup( _
.Range("A" & x), r1, 2, False)
End With
Next


--

HTH

RP
(remove nothere from the email address if mailing direct)


"eager_beaver" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> I'm having problems trying to match dates using VLookup in a Macro -
> any help will be much appreciated!
>
> I have two worksheets. The first worksheet (Prices) has two columns;
> the first having a list of dates, and the second having corresponding
> prices (numbers). The second worksheet (cleanPrices) has a set of dates
> (overlapping partially with the dates in the Prices spreadsheet. I want
> to match up prices for each of the dates in the cleanPrices spreadsheet
> based on the prices mentioned in the Prices worksheet.
>
> To make it clear:
> Price worksheet has:
> Date Price
> 7/7/2005 100.75
> 7/6/2005 98.50
> 7/1/2005 99.00
>
> cleanPrices has
> Date Price
> 7/6/2005
> 7/1/2005
> 6/30/2005
>
> I want the Price colum in cleanPrices to have 99.50, 99.00 and "#NA"
>
> I am using the following code (or something like this),
>
> Dim r1 As Range
> Dim x As Integer
>
> Set r1 = Worksheets("Price").Range("A2:B5")
>
> For x = 1 to 3
> Worksheets("cleanPrices").Range("B1").Offset(x, 0).Value =
>
Application.WorksheetFunction.VLookup(Worksheets("cleanPrices").Range("B1").
Offset(x,
> 0).Value, Worksheets("Price").Range(r1),2, False)
> Next
>
> I have tried several combinations (used DateValue and the likes, but I
> am always getting errors.
>
> Thanks!
>
>
> --
> eager_beaver
> ------------------------------------------------------------------------
> eager_beaver's Profile:
http://www.excelforum.com/member.php...o&userid=25515
> View this thread: http://www.excelforum.com/showthread...hreadid=389567
>

[abcd]

> .Range("A" & x), r1, 2, False)

see: no .value in the function

[Bob Phillips]

Can you explain that one?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"abcd" <[email protected]> wrote in message
news:[email protected]...
>
> > .Range("A" & x), r1, 2, False)
>
> see: no .value in the function

[abcd]

I think his error was to use a range().value inside the VLookup
because with a date, it's difficult to be sure to have exactly the
same number (and I think the error source is because the Vlookup do not
find any equal value). Letting Excel reading the cell (it knows it's a
date) make it do a better compare method.

With simple numbers (integers) the method with .value is ok. With date
It happens to have errors (none without the .value : since the dates are
in the list). I think his problem is around this .value inside the vlookup

[Bob Phillips]

There were a few problems

Application.WorksheetFunction.VLookup

was one

Range("B1").Offset(x,0).Value

was another, and

Range(r1)

was another one

but I don't think it was anything to do with .Range. I used that in the
solution I posted which works fine. Why do you mean by '... with a date,
it's difficult to be sure to have exactly the same number ...'?

Did you try the solution I posted, it works fine, regardless of the fact
that they are dates.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"abcd" <[email protected]> wrote in message
news:[email protected]...
> I think his error was to use a range().value inside the VLookup
> because with a date, it's difficult to be sure to have exactly the
> same number (and I think the error source is because the Vlookup do not
> find any equal value). Letting Excel reading the cell (it knows it's a
> date) make it do a better compare method.
>
> With simple numbers (integers) the method with .value is ok. With date
> It happens to have errors (none without the .value : since the dates are
> in the list). I think his problem is around this .value inside the vlookup

[abcd]

> Why do you mean by '... with a date,
> it's difficult to be sure to have exactly the same number ...'?

because dates are (inside) float numbers, and the end of the decimal
part (even for a same day, hour or second) may somtimes be different.

To know if two dates are ok, you may not substract them and look for a
zero value. IT will not be always ok with every date.

I do not know what VLookup do exactly (its engine inside) but I do
know I tryed the code (ok with no-date data), I supposed this kinda of
idea about dates, and it starts working with dates since I'd change this
..value thing

So ... May I can not explain exactly the details, but this change
things. What I mean is that the .value "may works" but it "may also
not". Depend on the values in the cells.

[Dave Peterson]

Dates are whole numbers. I've never had any trouble comparing two dates to find
out if they're equal. Do you have an example where you did?

Dates with times can have decimals, though.

I have had trouble using .Find with dates.

But if you're worried about errors cropping up in vlookup() or match(), maybe
you could use .value2 or even clng(rng.value) in your formula.

abcd wrote:
>
> > Why do you mean by '... with a date,
> > it's difficult to be sure to have exactly the same number ...'?
>
> because dates are (inside) float numbers, and the end of the decimal
> part (even for a same day, hour or second) may somtimes be different.
>
> To know if two dates are ok, you may not substract them and look for a
> zero value. IT will not be always ok with every date.
>
> I do not know what VLookup do exactly (its engine inside) but I do
> know I tryed the code (ok with no-date data), I supposed this kinda of
> idea about dates, and it starts working with dates since I'd change this
> .value thing
>
> So ... May I can not explain exactly the details, but this change
> things. What I mean is that the .value "may works" but it "may also
> not". Depend on the values in the cells.

--

Dave Peterson

[abcd]

Do you have an example where you did?

yes, somewhere below in an other post in excel newsgroups
this is famous
and we solved it (don't remember, maybe a datedif...)

so, now you know a vba date or two similar date may be differents in the
memory.

[Dave Peterson]

If you can find that sample, I'd still like to see it.

abcd wrote:
>
> Do you have an example where you did?
>
> yes, somewhere below in an other post in excel newsgroups
> this is famous
> and we solved it (don't remember, maybe a datedif...)
>
> so, now you know a vba date or two similar date may be differents in the
> memory.

--

Dave Peterson

[abcd]

Dave Peterson a =E9crit :
> If you can find that sample, I'd still like to see it.

only because it's you...


'microsoft.public.excel.programming
'10-07-2005 18:05
'Re: compare TIME in IF statement
'by okaizawa
'
'Hi,
'since time serial number is floating point number,
'you should not compare time serial numbers simply.
'I recommend to compare strings or integers.

Sub Test()
Range("A1").Value =3D TimeSerial(1, 40, 0)

'This shows False
MsgBox Range("A1").Value =3D TimeSerial(1, 40, 0)

'compare strings
MsgBox Format(Range("A1").Value, "hh:nn") =3D "01:40"

'compare total minute
MsgBox CLng(Range("A1").Value * (24 * 60)) =3D 1 * 60 + 40

'calculate the difference
If Abs(Range("A1").Value - TimeSerial(1, 40, 0)) _
< TimeSerial(0, 0, 1) Then
MsgBox "same"
End If
End Sub

[abcd]

MsgBox Range("A1").Value = TimeSerial(1, 40, 0)
is still false and it's better to know that fact


and if you try your example with a date:

Sub test()
Range("A1").Value = DateSerial(2001, 12, 1)
Range("B1").Value = DateSerial(2001, 12, 1)
[C2] = Application.VLookup(Range("A1").Value, Range("B1:C1"), 2, False)
[C3] = Application.VLookup(Range("A1"), Range("B1:C1"), 2, False)
End Sub

you have a problem with the .value but no problem without it


I can not be sure of the reason, but whatever you should admit dates are
not so easy to be manipulated: so i am ok to have an other explanation
if you think mine is wrong, but i will not accept the idea this .value
change nothing, because it does...

[abcd]

Bob Phillips a =E9crit :
> Yes, maybe true, but VLOOKUP is comparing dates in two cells, so the
> situation does not apply

my point of view is (maybe ! not sure) Vlookup compare dates *only*=20
because he knows these are dates without the .value but the "date=20
problem" occures when putting the .value inside the vlookup

because this have change things (range.value or range alone)

Maybe not sure of the explaination, but a difference is observed

[Bob Phillips]

Yes, maybe true, but VLOOKUP is comparing dates in two cells, so the
situation does not apply

Range("A1").Value = TimeSerial(1, 40, 0)
Range("B1").Value = TimeSerial(1, 40, 0)
Range("C1").Value = "Found okay"

MsgBox Range("A1").Value = Range("B1").Value

MsgBox Application.VLookup(Range("A1").Value, Range("B1:C10"), 2, False)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"abcd" <[email protected]> wrote in message
news:[email protected]...

Dave Peterson a écrit :
> If you can find that sample, I'd still like to see it.

only because it's you...


'microsoft.public.excel.programming
'10-07-2005 18:05
'Re: compare TIME in IF statement
'by okaizawa
'
'Hi,
'since time serial number is floating point number,
'you should not compare time serial numbers simply.
'I recommend to compare strings or integers.

Sub Test()
Range("A1").Value = TimeSerial(1, 40, 0)

'This shows False
MsgBox Range("A1").Value = TimeSerial(1, 40, 0)

'compare strings
MsgBox Format(Range("A1").Value, "hh:nn") = "01:40"

'compare total minute
MsgBox CLng(Range("A1").Value * (24 * 60)) = 1 * 60 + 40

'calculate the difference
If Abs(Range("A1").Value - TimeSerial(1, 40, 0)) _
< TimeSerial(0, 0, 1) Then
MsgBox "same"
End If
End Sub

[Bob Phillips]

No there are problems with dates, we all know that. It is just a matter of
knowing what works and what doesn't, and using that. If you look at my
original response, I didn't use the .Value, do you think that was
coincidental? And .Value2 works, as Dave suggested.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"abcd" <[email protected]> wrote in message
news:[email protected]...
> MsgBox Range("A1").Value = TimeSerial(1, 40, 0)
> is still false and it's better to know that fact
>
>
> and if you try your example with a date:
>
> Sub test()
> Range("A1").Value = DateSerial(2001, 12, 1)
> Range("B1").Value = DateSerial(2001, 12, 1)
> [C2] = Application.VLookup(Range("A1").Value, Range("B1:C1"), 2, False)
> [C3] = Application.VLookup(Range("A1"), Range("B1:C1"), 2, False)
> End Sub
>
> you have a problem with the .value but no problem without it
>
>
> I can not be sure of the reason, but whatever you should admit dates are
> not so easy to be manipulated: so i am ok to have an other explanation
> if you think mine is wrong, but i will not accept the idea this .value
> change nothing, because it does...

[Dave Peterson]

Thanks for posting your example. I understand the problem with times and
decimal representations, but my point was about Dates (no times included).

(I think there was a minor misconnect on the subject--sorry.)

abcd wrote:
>
> Dave Peterson a écrit :
> > If you can find that sample, I'd still like to see it.
>
> only because it's you...
>
> 'microsoft.public.excel.programming
> '10-07-2005 18:05
> 'Re: compare TIME in IF statement
> 'by okaizawa
> '
> 'Hi,
> 'since time serial number is floating point number,
> 'you should not compare time serial numbers simply.
> 'I recommend to compare strings or integers.
>
> Sub Test()
> Range("A1").Value = TimeSerial(1, 40, 0)
>
> 'This shows False
> MsgBox Range("A1").Value = TimeSerial(1, 40, 0)
>
> 'compare strings
> MsgBox Format(Range("A1").Value, "hh:nn") = "01:40"
>
> 'compare total minute
> MsgBox CLng(Range("A1").Value * (24 * 60)) = 1 * 60 + 40
>
> 'calculate the difference
> If Abs(Range("A1").Value - TimeSerial(1, 40, 0)) _
> < TimeSerial(0, 0, 1) Then
> MsgBox "same"
> End If
> End Sub

--

Dave Peterson

[Dave Peterson]

Thanks for posting this.

But to be honest, I use this if I'm working with dates:

Range("c4").Value _
= Application.VLookup(CLng(Range("A1").Value), Range("B1:C1"), 2, False)

But it's good to learn--I had never noticed the difference between using the
..value and not using it.

I guess my question (rhetorical???) is why?

I would have guessed that specifying the default property would be the
equivalent of letting it default.

(Excel is a strange beast!)



abcd wrote:
>
> MsgBox Range("A1").Value = TimeSerial(1, 40, 0)
> is still false and it's better to know that fact
>
> and if you try your example with a date:
>
> Sub test()
> Range("A1").Value = DateSerial(2001, 12, 1)
> Range("B1").Value = DateSerial(2001, 12, 1)
> [C2] = Application.VLookup(Range("A1").Value, Range("B1:C1"), 2, False)
> [C3] = Application.VLookup(Range("A1"), Range("B1:C1"), 2, False)
> End Sub
>
> you have a problem with the .value but no problem without it
>
> I can not be sure of the reason, but whatever you should admit dates are
> not so easy to be manipulated: so i am ok to have an other explanation
> if you think mine is wrong, but i will not accept the idea this .value
> change nothing, because it does...

--

Dave Peterson

[abcd]

I think because this does not give the same transtypage

[okaizawa]

> Range("A1").Value = TimeSerial(1, 40, 0)
> 'This shows False
> MsgBox Range("A1").Value = TimeSerial(1, 40, 0)

that post doesn't mean all floating point numbers are inaccurate and
doubtful. if it impressed so, my fault.
time serial number is inaccurate (not all but most), because of its
definition and the IEEE 754 format. and because of them, date number
without time part is accurate and there is no rounding error.
of course, we shouldn't believe a black box simply. for instance,
we know that 0.12345 and 0.123450 are the same number, but in excel
we should doubt it at first.
(put numbers and formula, A1: 0.87006, B1: 0.870060, C1: =A1-B1+0
C1 would return non-zero value.)
however, i have not seen that date number without time (i.e. an integer)
has had rounding error.


in passing 'Date' type value from vba to worksheet functions,
date number seems to be converted to a text, and then VLookup
(most other functions also) uses that text.
perhaps, VLookup in vba would find '7/6/2005 put in a cell as a text,
and ignore all date numbers.

i guess, in passing range object type value, it would be converted to
a cell-range reference. (perhaps R1C1 format text is used)
and VLookup would take the cell's value as a simple number.
so, no problem occurs.

--
HTH,

okaizawa

[Dave Peterson]

I don't understand: transtypage

Can you try again?

abcd wrote:
>
> I think because this does not give the same transtypage

--

Dave Peterson

[Bob Phillips]

I don't think it did that. The guy who raised the problem raised a problem
that whilst being a relevant point did not apply to the post, and confused
rather than helped IMO.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"okaizawa" <[email protected]> wrote in message
news:[email protected]...
> > Range("A1").Value = TimeSerial(1, 40, 0)
> > 'This shows False
> > MsgBox Range("A1").Value = TimeSerial(1, 40, 0)
>
> that post doesn't mean all floating point numbers are inaccurate and
> doubtful. if it impressed so, my fault.
> time serial number is inaccurate (not all but most), because of its
> definition and the IEEE 754 format. and because of them, date number
> without time part is accurate and there is no rounding error.
> of course, we shouldn't believe a black box simply. for instance,
> we know that 0.12345 and 0.123450 are the same number, but in excel
> we should doubt it at first.
> (put numbers and formula, A1: 0.87006, B1: 0.870060, C1: =A1-B1+0
> C1 would return non-zero value.)
> however, i have not seen that date number without time (i.e. an integer)
> has had rounding error.
>
>
> in passing 'Date' type value from vba to worksheet functions,
> date number seems to be converted to a text, and then VLookup
> (most other functions also) uses that text.
> perhaps, VLookup in vba would find '7/6/2005 put in a cell as a text,
> and ignore all date numbers.
>
> i guess, in passing range object type value, it would be converted to
> a cell-range reference. (perhaps R1C1 format text is used)
> and VLookup would take the cell's value as a simple number.
> so, no problem occurs.
>
> --
> HTH,
>
> okaizawa