Transferring a Date array to a range

[jonigr]

I am having trouble transferring a “Date array†to a range.

In my XL sheet cells A1-A3 are pre-formatted as mm/dd/yyyy dates.

MyDateArray is a two dimensional (3, 1) array containing dates, say all
three are 05/11/2007.

When I try

Range("A1").Resize(1, 1) = MyDateArray(1, 1)

this works perfect and puts

05/11/2007

in A1, but

Range("A1").Resize(3, 1) = MyDateArray

gives me three times

01/00/1900
01/00/1900
01/00/1900

in A1-A3.

This is actually a much longer array and I prefer not to loop through all
the elements. Is there a way to transfer it to the sheet at once without
disturbing the dates? Thanks very much!

 

[paul.robinson]

Hi
looks like year zero. is there anything in MyDateArray?
Is MyDateArray a Variant?
i.e
myDateArray = Range("A1:A3").Value

for your example?

regards
Paul

 

[jonigr]

Hi Paul,

I toned down the code to the bare minimum. This results in a senseless piece
but does illustrate the problem.

Please pre-format Range A1:C3 (9 cells) as date mm/dd/yyyy format and put
03/25/2006 in cells A1:A3 (3 cells).

Thanks so much for taking the time to help!

-joni

Function ChangeDate(OldDate As Variant) As Date
If OldDate = #3/25/2006# Then ChangeDate = #5/11/2007#
End Function

Sub DateArrayTest()

Dim NewDateArray() As Date
ReDim NewDateArray(1 To 3, 1)
Dim OldDateArray As Variant
OldDateArray = Range("A1").Resize(3, 1)

For j = 1 To 3
NewDateArray(j, 1) = ChangeDate(OldDateArray(j, 1))
Next j

Range("B1").Resize(1, 1) = NewDateArray(1, 1) 'this works
Range("C1").Resize(3, 1) = NewDateArray 'but this doesn't!

End Sub

 

[Dave Peterson]

It's not the assignment back to the worksheet--it's your function.

If the old date isn't March 25, 2006, then the ChangeDate will be 0.

Maybe you wanted:

Option Explicit
Function ChangeDate(OldDate As Variant) As Date
If OldDate = #3/25/2006# Then
ChangeDate = #5/11/2007#
Else
ChangeDate = OldDate
End If
End Function

Sub DateArrayTest()
Dim j As Long

Dim NewDateArray() As Date
ReDim NewDateArray(1 To 3, 1 To 1)
Dim OldDateArray As Variant
OldDateArray = Range("A1").Resize(3, 1)

For j = 1 To 3
NewDateArray(j, 1) = ChangeDate(OldDateArray(j, 1))
Next j

Range("B1").Resize(1, 1) = NewDateArray(1, 1) 'this works
Range("C1").Resize(3, 1) = NewDateArray 'but this doesn't!

End Sub

 

[jonigr]

Hi Dave, it works now! However I don’t think it was the function, it was
because you (unconsciously?) changed my incorrect

ReDim NewDateArray(1 To 3, 1)

to the correct

ReDim NewDateArray(1 To 3, 1 To 1)

Thanks!

-joni

 

[Dave Peterson]

I made that change on purpose.

But upon further review, I'd say you have two problems--well, only one if you
really wanted 0's returned.

 

[jonigr]

Ah well, point is that that single change in my original code is sufficient
to copy the full array to the range as wanted (resulting in new dates instead
of zeros).

What the result is when the input is not provided for in the function
procedure is a slightly different matter. In any case we got this solved,
thanks again for your time and expertise.

-joni