Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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 correspondin prices (numbers). The second worksheet (cleanPrices) has a set of date (overlapping partially with the dates in the Prices spreadsheet. I wan to match up prices for each of the dates in the cleanPrices spreadshee 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 am always getting errors. Thanks -- eager_beave ----------------------------------------------------------------------- eager_beaver's Profile: http://www.excelforum.com/member.php...fo&userid=2551 View this thread: http://www.excelforum.com/showthread.php?threadid=38956 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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" wrote in message news:eager_beaver.1smaea_1122113110.0127@excelforu m-nospam.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
.Range("A" & x), r1, 2, False) see: no .value in the function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
Can you explain that one?
-- HTH RP (remove nothere from the email address if mailing direct) "abcd" wrote in message ... .Range("A" & x), r1, 2, False) see: no .value in the function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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 ' 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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" wrote in message ... 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 ' 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
Bob Phillips a écrit : 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* because he knows these are dates without the .value but the "date 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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... |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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" wrote in message ... 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... |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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 ' 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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
I think because this does not give the same transtypage
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
I don't understand: transtypage
Can you try again? abcd wrote: I think because this does not give the same transtypage -- Dave Peterson |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling Dates in VLookup
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup with Dates | Excel Worksheet Functions | |||
Error handling VLOOKUP. #N/A - can it be 0 to allow sumation. | Excel Worksheet Functions | |||
dates and vlookup | Excel Worksheet Functions | |||
Error handling with a handling routine | Excel Programming | |||
Vlookup Error handling | Excel Programming |