[RESOLVED] Date in Excel

[mutley]

Hi

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

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

[Siddharth Rout]

Try this

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

[westconn1]

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

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

[mutley]

sorry, in my code are Date

Dim lMyArray(2, 1) As date

[Siddharth Rout]

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

[mutley]

Thank, but the problem is not resolved

[si_the_geek]

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

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).

[westconn1]

si copied the original code

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

[si_the_geek]

oops, fixed.


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

[mutley]

Thank

Already with

Option Explicit

[mutley]

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

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

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

[si_the_geek]

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.

[mutley]

Sorry, I did not understand

[mutley]

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

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

[si_the_geek]

You are using a one dimensional array:

Dim lMyArray(2) As Date

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

Dim lMyArray(2, 1) As Date

[mutley]

Thank You

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


tia

[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 )

[westconn1]

you can use a 1 dimensional array like

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

[mutley]

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

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

[westconn1]

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"

[mutley]

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

  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

[si_the_geek]

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:

Plan1.Range("D7").CopyFromRecordset rs

[mutley]

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

How can I to post like resolved ?

[si_the_geek]

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).