VBA to read full dates not just the day
Hi,
I have a macro which searches for the latest date down a column and across particular rows. Extract of the code is shown below:
The problem is that it keeps returning the highest day not dates, for example say 16/06/09 and 03/07/09 are both in the same results the value which is displaying is 16/06/09.
Please Login or Register to view this content.
I believe this is beacuse 16 is higher than 3, and the code is ignoring the fact that it is a date.
Any ideas?
Thanks again all!
Last edited by pmarsella; 08-05-2009 at 03:56 AM.
Val is declared as a String, "16/06/09" is a bigger string than "03/07/09"
Last edited by DonkeyOte; 08-05-2009 at 04:09 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
How would I change this to store val as a date? Or, at least work out the latest date using strings?
Thank you for revising your post and adding tags.
As stated your Val variable is declared as a String... thus the comparison is String based rather than Number based and "16..." is > "03..."
Posts are crossing... as discussed on the other thread I would still opt for an Evaluation rather than Iteration, eg:
Please Login or Register to view this content.
What other way could I store the Val as rather than a string?
The code is useless unless it can pick up the latest date.
If you want to use the original code just Declare Val as Long rather than String
This is much better code, however, can I make it ignore dates which are 00/00/0000 or cells which are blank. It is returning 00/00/0000 for both.Originally Posted by DonkeyOte
Posts are crossing... as discussed on the other thread I would still opt for an Evaluation rather than Iteration, eg:
Please Login or Register to view this content.
Thanks.
Not entirely sure I follow, you should only get 0 returned (in date format 00/00/0000) if there are no dates at all associated with the "Sentence Process" values ... can you post an example ?
I have found the problem it is to do with the way the dates have been formatted in their cells. I should be able to try and fix this. If not I will be back!
Thanks for your time!
ok,
I can't find the solution. The problem is that when I change the date format to 16-jun-09 some of them stay in the format 16/06/2009 which the macro does not read and wont include in the results.
To overcome this i have to manually double-click each date which never changed and it will then go to the correct format 16-jun-09.
Is there anything I can do to make this happen automatically for all the cells which never formatted correctly?
I have no idea why some dont format in the first place.
Thanks.
The column is seemingly formatted as Text, you could run Text to Columns first off, eg:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks