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

"Dave Peterson" wrote:

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 wrote:

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" wrote:

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 wrote:

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!

--

Dave Peterson


--

Dave Peterson