|
Transferring a Date array to a range
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! |
Transferring a Date array to a range
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! |
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! |
Transferring a Date array to a range
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 |
Transferring a Date array to a range
Hi Dave, it works now! However I dont 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 |
Transferring a Date array to a range
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 dont 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 |
Transferring a Date array to a range
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 |
| All times are GMT +1. The time now is 01:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com