Hi Paul,
The dates all do change to numbers for both the value to lookup and in the table. Everything is already sorted Newest at the Top.
I have to do something now, but I will try and put together a file to send later.
Thanks,
David
From: Paul Schreiner <schreiner_paul@att.net>
To: ExcelVBA@yahoogroups.com
Sent: Friday, September 16, 2011 8:51 AM
Subject: Re: [ExcelVBA] Vlookup(Date, range, 1, true)
we still need to validate some things...
in your date column, change the formatting to "general".
do they all change to numbers?
what about if you change the cell that you're using as the lookup value? ("Date"
in vlookup(Date,range,1,true)
also.. try sorting your data by the Date column.
the Vlookup when using "true" is erratic.
Sometimes, it selects the first value GREATER than the tested value.
BTW.. what version of Excel are you using? (2007 and 2010 do a little better job
of it)
Can you send me an Excel file with just a few rows of values so I can look at
it?
I COULD try to duplicate it, but when I insert dates, they're exactly what I
expect them to be and they work perfectly...
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: David Lanman <who1012000@yahoo.com>
To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Sent: Fri, September 16, 2011 10:52:41 AM
Subject: Re: [ExcelVBA] Vlookup(Date, range, 1, true)
Hello,
I am using all of the parameters, the lookup value is another cell, the table is
fairly large, I am returning the first column (just to make sure it is working)
and I use True, because the date will not always be there, but the closest is
fine. If I use False, it works, unless the date is not there, then I get #N/A,
which is expected. If I use True, it fails, even if the date is there.
How might I do the Vlookup if I assume that the dates are all Default Midnight
and that the date will not likely be exact even for the day, let alone the time.
In the case that I am looking at the lookup dates are all 12/31/yyyy, going back
10 years. The table dates are daily and go about about 10 years.
What is returned is always 1/3/2000.
Thanks,
David
From: Paul Schreiner <schreiner_paul@att.net>
To: ExcelVBA@yahoogroups.com
Sent: Friday, September 16, 2011 7:30 AM
Subject: Re: [ExcelVBA] Vlookup(Date, range, 1, true)
There are several, several and... several... possibilities.
without seening your data and function, it's really difficult to guess.
However... because you said "it always returns the same date".
In your function, are you using all parameters?
the syntax is:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
even though they SAY that [range_lookup] is optional, it's really not.
it's only optional if you're willing to accept a value that's "close" to what
you're looking for.
This value should always be "false"
secondly.. dates are funny things.
Based on the source of the data, even though you may SEE a date, it actually
contains time information.
(try changing the format of the cells and see if it shows you 12:00 as a time
for all values.
If it does, then the default time is midnight)
This is important, because these two values:
9/16/2011 10:23:14
9/16/2011 10:23:15
will each display as 9/16/2011, but in reality, they're stored as:
40802.4327972222
40802.4328125000
and if you're looking for 9/16/2011, you're really comparing the values of these
numbers to 40802.00000000
which you won't find, unless one of the values happens to be at midnight.
So... we need more info to move forward with helping you with this one.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: David <who1012000@yahoo.com>
To: ExcelVBA@yahoogroups.com
Sent: Fri, September 16, 2011 10:15:43 AM
Subject: [ExcelVBA] Vlookup(Date, range, 1, true)
Hello All,
I am doing a Vlookup on a date, but it fails. Is there something about dates
that fails? It always returns the same date, which is always incorrect.
Thanks,
David
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar