Transferring a Date array to a range
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
" wrote:
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
On Feb 25, 3:58 am, 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!
|