[RESOLVED] Date in Excel

Hi

I tried to use code below, but when the data array is date , no work fine

Code:

---

Dim lMyArray(2, 1) As Longl
MyArray(0, 0) = format(now,"dd/mm/yyyy")
MyArray(0, 1) = format(now +1,"dd/mm/yyyy")
MyArray(1, 0) = format(now +2,"dd/mm/yyyy")
MyArray(1, 1) = format(now +3"dd/mm/yyyy")
MyArray(2, 0) = format(now +4,"dd/mm/yyyy")
MyArray(2, 1) = format(now +5,"dd/mm/yyyy")


XLSheet.Range("D7:E9").Value = lMyArray

---

In Excel date show all column as "0/1/1900"
Why ?

I see code in http://www.vbforums.com/showthread.php?t=391665 for other data
work fine :(

Try this

vb Code:

Sub Sample()
    Dim MyArray(2, 1) As Date
    MyArray(0, 0) = Format(Now, "dd/mm/yyyy")
    MyArray(0, 1) = Format(Now + 1, "dd/mm/yyyy")
    MyArray(1, 0) = Format(Now + 2, "dd/mm/yyyy")
    MyArray(1, 1) = Format(Now + 3, "dd/mm/yyyy")
    MyArray(2, 0) = Format(Now + 4, "dd/mm/yyyy")
    MyArray(2, 1) = Format(Now + 5, "dd/mm/yyyy")
    XLSheet.Range("D7:E9").Value = MyArray
    XLSheet.Range("D7:E9").NumberFormat = "dd/mm/yyyy"
End Sub

formatted dates are strings not longs
myarray is not lmyarray
you may need to format the workshee cells for that range

i tested this appeared correct

vb Code:

Dim lMyArray(2, 1) As Variant
lMyArray(0, 0) = Format(Now, "dd/mm/yyyy")
lMyArray(0, 1) = Format(Now + 1, "dd/mm/yyyy")
lMyArray(1, 0) = Format(Now + 2, "dd/mm/yyyy")
lMyArray(1, 1) = Format(Now + 3, "dd/mm/yyyy")
lMyArray(2, 0) = Format(Now + 4, "dd/mm/yyyy")
lMyArray(2, 1) = Format(Now + 5, "dd/mm/yyyy")
Range("D7:E9").Value = lMyArray

sorry, in my code are Date

Code:

---

Dim lMyArray(2, 1) As date

---

:D

So if your query is solved then do remember to mark the thread resolved;)

Check the link in my signature on how to resolve it :)

Thank, but the problem is not resolved

:(

If they are supposed to be Dates then put dates into them, not Strings (which is what Format returns). eg:

Code:

---

Dim lMyArray(2, 1) As Date
lMyArray(0, 0) = now
lMyArray(0, 1) = now +1
lMyArray(1, 0) = now +2
lMyArray(1, 1) = now +3
lMyArray(2, 0) = now +4
lMyArray(2, 1) = now +5

XLSheet.Range("D7:E9").Value = lMyArray

---

This will ensure that the correct values are passed, but they might not display properly - if that is the case, change the format of the cells in Excel (and record a macro so that you can see the code to do it).

si copied the original code

turn option explicit on, it may resolve some of your problems

oops, fixed. :blush:


Here's an FAQ article on what westconn1 mentioned: What is Option Explicit, and why should I use it?

Thank

Already with

vb Code:

Option Explicit

Quote:

---

Originally Posted by si_the_geek

If they are supposed to be Dates then put dates into them, not Strings (which is what Format returns). eg:

Code:

---

Dim lMyArray(2, 1) As Date
lMyArray(0, 0) = now
lMyArray(0, 1) = now +1
lMyArray(1, 0) = now +2
lMyArray(1, 1) = now +3
lMyArray(2, 0) = now +4
lMyArray(2, 1) = now +5

XLSheet.Range("D7:E9").Value = lMyArray

---

This will ensure that the correct values are passed, but they might not display properly - if that is the case, change the format of the cells in Excel (and record a macro so that you can see the code to do it).

---

Thank , but I tried from VB and inside Excel the code below, and no work

Code:

---

Sub insere_datas()
  Dim Dt_Expedicao()  As Date
  Dim i As Integer
    ReDim Dt_Expedicao(0)
  Do While i < 9
    ReDim Preserve Dt_Expedicao(UBound(Dt_Expedicao) + 1)
    Dt_Expedicao(UBound(Dt_Expedicao)) = Now()    'CStr(rs!data_expedicao)
    i = i + 1
  Loop
  '  Range("AF4:AF12").Select
    Range("AF4:AF12").NumberFormat = "@"
    Range("AF4:AF12").Value = Dt_Expedicao
    Range("AF4:AF12").NumberFormat = "dd/mm/yyyy"
    Range("AF4:AF12").HorizontalAlignment = xlCenter
 
 
 
End Sub

---

Show me as 00/01/1900

:(:(

That is because:

1) You are using a one-dimensional array (which goes across the sheet, and gets copied downwards if apt) rather than a two-dimensional array (which goes across and down).

2) You do not set a value for the first array element, so it is the default value.

Sorry, I did not understand

Thank , eg when I tried code below It put only same date

vb Code:

Dim lMyArray(2) As Date
lMyArray(0) = Now
lMyArray(1) = Now + 1
lMyArray(2) = Now + 2
Plan1.Range("D7:D9").Value = lMyArray

You are using a one dimensional array:

Code:

---

Dim lMyArray(2) As Date

---

..it needs to be a two-dimensional array (as it was in post #7 and earlier):

Code:

---

Dim lMyArray(2, 1) As Date

---

Thank You

But I must put only a column , in Left column and right column already data and can not to changed


tia:(

So?

There is nothing to stop you using a 2-dimensional array with just one column (eg: Dim lMyArray(2, 0) As Date )

you can use a 1 dimensional array like

vb Code:

Dim lMyArray(2) As Date
      lMyArray(0) = Now
      lMyArray(1) = Now + 1
      lMyArray(2) = Now + 2
      Range("D7:D9").Value = Application.WorksheetFunction.Transpose(lMyArray)
      Range("d7:d9").NumberFormat = "dd/mm/yy"

the transpose function swaps row and columns

I tried to use your code , but put values (dates ) inconsistent

Code:

---

Dim lMyArray(3) As Variant

'ReDim Preserve lMyArray(0, 2) As Variant

lMyArray(0) = Now
lMyArray(1) = Now + 1
lMyArray(2) = Now + 2
Plan1.Range("D7:D9").Value = Application.WorksheetFunction.Transpose(Dt_Expedicao)
Plan1.Range("d7:d9").NumberFormat = "dd/mm/yy"

---

Show me

04/08/09
00/01/00
00/01/00

what is dt_expedicao
it is suppossed to be the array (lMyarray) with the dates that is transposed

i tested and works correctly

what is your local date format?
you could try changing to
Plan1.Range("d7:d9").NumberFormatLocal = "dd/mm/yy"

Quote:

---

Originally Posted by si_the_geek

So?

There is nothing to stop you using a 2-dimensional array with just one column (eg: Dim lMyArray(2, 0) As Date )

---

Thank

Your Idea work fine , But I am using a loop with recordset and need resize array for each record

Code:

---

  Do While Not rs.EOF
     
        'Acrescenta uma linha no array
     
        Dt_Expedicao(UBound(Dt_Expedicao)) = Now()    'CStr(rs!data_expedicao)]
        ReDim Preserve Dt_Expedicao(UBound(Dt_Expedicao) + 1) As Date

  loop

---

If I need put in two dimension I can to use Redim Preserve in second dimension
;)

As it is coming from a recordset (assuming data_expedicao is the only field in it), there is no need to use an array or any loops - you can simply use CopyFromRecordset instead:

Code:

---

Plan1.Range("D7").CopyFromRecordset rs

---

Sorry, variable error, Your solution woked fine !!!!

How can I to post like resolved ?

You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).