Having issue comparing/pasting/using dates from South-Africa and I am in USA..

[welchs101]

Hi, i am not sure exactly what question to ask or what to show you.

When i run my macro using input files generated in South-Africa.........my program works fine.

When someone in South-Africa runs my macro.............they have the following situation arrise:
1) Date such as January 23 2011 will show up in a file that i created in the macro as 23/01/11............the problem is this 23/01/11 shows up as not a date format and causes a type mismatch error in the macro. This happens to the person running this in south africa...........In the macro i i try to format this date to the following format "d-mmm-yyyy" but nothing happens.


any suggestions / ideas ?

anything idea or things to consider at all would be appreciated..........i am at my wits end on this one. no ideas how to proceed.

[welchs101]

I have been having a lot of problems lately trying to convert/manipulate dates from one region of the world to another.

I am definitely not an expert but i did figure out my problem.

Here it is:
I would run my macro here in the US and it would work fine. I would send it to a friend in South Africa(ZA) to run and they would get a runtime error "type mismatch" because what was supposed to be a date was not and so it failed when it tried to compare two dates.

So here is my blunder. I was reading in data from one file. This file had many different data types.......text, numbers, dates................

I read all the data into a single string array (which had many columns in the array). I then pasted this array into another worksheet in the format and order i wanted.

Upon putting the data from the array into the worksheet what would happen is in ZA dates can be input as 23/12/2011....dd-mm-yy....format

Well, the string "23/12/2011" when pasted into excel worksheet was not immediately recognized as a date even in ZA. Even if i tried to convert 23/12/2011 into say a diff. date format like mm-dd-yy........using the format cells date option it would not work..........i actually posted a thread on this the other day as well.

To make a long story short..........what i did to correct the problem was not read data into string array. I did it the old fashioned and probably not the best way..........copy and pastespecial valueandnumberformats. This seemed to work just fine. No issues with the dates at all.

[romperstomper]

If you are going to move data around in arrays, I recommend you use the Value2 property to read from or write to arrays, rather than the Value. Value2 reads dates and currencies as doubles which tends to avoid the issue.

[welchs101]

thanks romperstomper,

can you give me an example of how you would move it around..........you showed me the ".value2" before when i was trying to do the filldown but i am not sure how to apply this with arrays.

[romperstomper]

Please Login or Register  to view this content.

for example.

[welchs101]

thanks romperstomper,

i was doing some reading on this and found a good article........says the same thing you did........USE .VALUE2

http://fastexcel.wordpress.com/2011/...avoid-itthanks


thanks for the info did not know this.

I still probably would have had the issue i did because of the "array was a string" though.....right?

[welchs101]

for some reason the link did not work......will try and post it again.

http://fastexcel.wordpress.com/2011/...w-to-avoid-it/