Array changes dates stored as dates into text.

**KNevland** · 07-02-2014, 08:26 AM

I've made a macro that pulls date-values out from one Array (SearchBondArray) if a certain condition is met. The loop then adds the date from one array to TempDateArray, while making sure that the TempDateArray is expanding as more dates are added.

When the entire array is searched through, and no more dates are to be added. The Array is then pasted into a range in an Excel Sheet.

The issue is that when the dates are pasted into the excel sheet, the format is no longer dates but Text. And thus the sorting formula in the end can't sort the data by most recent date.

Any idea on how to make sure that the values posted are still formatted as dates?

Please Login or Register  to view this content.

**romperstomper** · 07-02-2014, 08:35 AM

Try using:

Please Login or Register  to view this content.

**KNevland** · 07-02-2014, 08:44 AM

Thank you! That worked great!

Mind telling me what adding the "2" after .value does? As opposed to just .value.

**romperstomper** · 07-02-2014, 08:56 AM

.Value2 returns Date values as their Double equivalent (which is how Excel internally works with dates) so rather than getting 02/07/2014 from a cell with today's date in as you would with .Value, .Value2 will return 41822. This value can't be misinterpreted, unlike a Date string, which VBA will interpret as a date using a US format if it can, or return Text if it can't.

When you use Application.Transpose, the data in the array becomes Strings rather than Dates, so when it is put into the range, VBA will interpret 02/07/2014 as being 7th Feb 2014 and return that as a date, but a date like 23/02/2014 can't be interpreted as a US format date, so you get a "23/02/2014" as text.