James has attended:
Excel VBA Intro Intermediate course
Recognising Dates
Hi Carlos,
I am currently writing a macro that, in one section of it, needs to search through a list of dates (row by row assigning each date to a variable and comparing using SelectCase). Unfortunately the list is an export from a piece of software that creates date cells in the format
" 12/31/2007"
The problem occurs when the cell shows
" 01/02/2008" to " 01/12/2008"
excel seems to treat this as dd/mm/yyyy and then back to mm/dd/yyyy when the middle value is more than 12.
I then use the line:
DateValue = ActiveCell.Value
to assign the data in this cell to the variable "DateValue".
Is there anything I can add to this line to assign the variable in a uniform manner?
Regardss,
James
RE: Recognising Dates
I have just found one solution for this - changing my computers regional setting to US!
Is this the only fix or is there an easier one?
RE: Recognising Dates
Hi James
Received the e-mail but there was just an image of the regional options dialog box attached.
Could you send the workbook?
I'm busy training the next few days but as soon as I get some time I'll look at it.
Carlos
RE: Recognising Dates
James
First Don't use DateValue as a variable name. This is a Date Function in Excel
I created 2 variables :
1. MyDate - This holds the date being compared to
2. ImportDate - This will hold each of the imported dates to compare
Now compare the two dates eg:
MyRow = 1
ImportDate = Cells(MyRow,1).Value
If MyDate = ImportDate then
<Do code>
MyRow = MyRow + 1
End If
This can loop through all the dates
By doing this the computer saves the imported date as UK.
Hope this helps. If not please send me the Weorkbook with your code and I'll see what I can do
Regards
Carlos
RE: Recognising Dates
Carlos,
I have a similar code setup for the loop (just with a couple more variables - firstdate, seconddate etc) with a select case instead of an IF.
I have tried changing DateValue to ImportDate but this doesn't work.
Can you send me your email address so that I can attach the workbook? I can't seem to attach files to this reply...
Regards,
James
RE: Recognising Dates
Hi James
Send the workbook to
info at stl-training.co.uk
Attention Carlos
and my collegues will forward it to me
Regards
Carlos
