"Angela" wrote:
I would appreciate if someone can have a look at this again.
Sorry, I did not read your previous posting closely, and I did not see your
request for follow-up.
Angela wrote previously:
I have tested 25 senarios and would like to share
with you. I'm so far successful with 24. Only stuck
with 1.
Your implementation has a number of deficiencies. See below.
Did you try the implemenation I offered?
Angela wrote previously:
Please let me know how to share my findings in an excel
sheet with you. I would like to share a table with all
25 senarios that I'm looking into.
You could send the Excel file to me directly. Send it joeu2004 "at"
hotmail.com.
Alternativly, upload the Excel file to a file-sharing website and post the
URL (link;
http://...) in a response here. Be sure that the uploaded file
is marked shared or sharable on the file-sharing website. The following is
a list of some free file-sharing websites. I use box.net/files.
Windows Live Skydrive:
http://skydrive.live.com
MediaFi
http://www.mediafire.com
FileFactory:
http://www.filefactory.com
FileSavr:
http://www.filesavr.com
FileDropper:
http://www.filedropper.com
RapidSha
http://www.rapidshare.com
Box.Net:
http://www.box.net/files
Some comments on the code you posted....
Angela wrote previously:
TargetDateG = Format(TargetDate, "Short Date")
There is no need to format TargetDate in some date form. The date format
does not make any difference.
If you are trying to remove a non-zero time component, it is sufficient to
do:
TargetDate = Int(TargetDate)
Angela wrote previously:
AgeLmt = Array(0, 7.51, 14.51, 21.51, 30.51, 45.51, 60.51)
As I explained before, there is no need for the decimal fractions 0.51,
especially if you use Int(TargetDate) as I suggested previously and above.
Angela wrote previously:
If TargetDate = TargetDateG Or TargetDate < 1 Then
AgeBucket = "Invalid date or out of range"
The test "< 1" catches only "dates" that are time-only. I suspect that you
also would like to treat numbers like 1234 as "out of range". After all,
that is the date 5/18/1903.
Previously, I suggested that you hardcode a reasonable constant least-likely
age. I chose 365 (one year) arbitrarily.
Alternatively, you could have a constant least-likely date. For example:
Const earliestDate = #1/1/2010#
Moreover, your implementation will result in a "type mismatch" VBA error if
TargetDate is a non-numeric string.
I wonder if that is 25th scenario that your implementation fails with.
Previously, I provided a reasonable implementation that would avoid this.
Did you try my implemenation?
Angela wrote previously:
If CDate(TargetDate) Date Then
The only reason to use CDate is if TargetDate might be a __string__ whose
content is interpreted to be a date, not an Excel date, which is numeric.
Is that the case?
Well, to answer that, I really do need to see the Excel file. The English
description is fraught with potential misunderstanding and
misinterpretation.