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

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?

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

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

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

Hi James

Send the workbook to

info at microsofttraining.net

Attention Carlos

and my collegues will forward it to me

Regards

Carlos