Problem with dates when writing a VBA array to Excell

[jjhenry]

I writing a VBA varinat array to Excel, I have a date column in the array formatted UK date. When I write it to Excel it is in US format. I do the same thing on a diferent sheet and it works fine. Even if I change the format of the cells after writing in the array the format does not change. The VBA code is

.Activate
.Range(Cells(pprow + 1, 1), Cells(pprow + UBound(DayData, 2), 10)) = Application.Transpose(DayData)

Any ideas
Thanks
Jason

 

[RoryA]

Welcome to the forum.

I suspect that the source cells for DayData actually contain dates stored as text. If they were true date values, you shouldn't have that issue. I suggest you convert the source cells.

 

[jjhenry]

Welcome to the forum.

I suspect that the source cells for DayData actually contain dates stored as text. If they were true date values, you shouldn't have that issue. I suggest you convert the source cells.

Thanks RoryA

I thought I was doing that, see below

For k% = 1 To 10
If k% = 2 Then
DayData(k%, Drowcnt%) = Format(orderdata(j%, k%), "dd/mm/yyyy")
Else
DayData(k%, Drowcnt%) = orderdata(j%, k%)
End If
Next k%

 

[RoryA]

Nope - Format is actually returning text. You shouldn't need to use Format at all. If you do use it, you should format as mm/dd/yyyy so your later code will work properly since VBA will convert the strings back to dates using US locale settings.

 

[jjhenry]

Hi Rory
Thanks, I have got it working now.
Jason

 

[RoryA]

Glad to help!

 

[Shamen]

Hello,

I encountered the same problem as described above.
I pick up a two-dimensional table into array, and one of the input columns in Excel is in a non-American date (not text) format.
After I take out one row from array into an excel sheet a date becomes an American date format.

Dim i As Integer, j As Integer
Dim DataArr As Variant

DataArr = [InputSRow].Offset(1, 0).Resize([DataCounter], 5)

For i = 1 To [DataCounter]

    [CalcSRow].Offset(1, 0).Value = Application.WorksheetFunction.Index(DataArr, i, 0)

Is there a problem here?


And another question:
After a certain calculation I want to insert a whole set of values from the sheet into a two-dimensional array, something like:

Dim DataArr(1to 1000, 1 to 5)  As Variant
OutputArr(i, ) = Range("C5:G5").Value

But it's not working. Is there a way to do this without putting values one by one in a loop?


I would appreciate help

 

[RoryA]

Re your first question, Index is unnecessary there, and probably causing the problem. Use:

[CalcSRow].Offset(1, 0).Value = DataArr(I, 1)

 

[Shamen]

Thank you very match for the response.

I did not explain myself to the end.
The formula is a range of 5 cells and in order to save time I try to write several values (a row from an array) into a sheet in one step and not one by one in Loop (realated to my second question).
This is the way I found using an index and may be this causing the problem ...
By now i solved it by formatting the dates column as number before transferring and then formatting back as dates - seems to work.

Thanks again

 

[RoryA]

You shouldn't need to change the formatting if you just use the value2 property of the range:

DataArr = [InputSRow].Offset(1, 0).Resize([DataCounter], 5).Value2