Why is the first element of my array formatted differently than the rest when reading them into cell-CodePudding

I was hoping you could help me with this issue: I have an array of strings that I'm using it to iteratively populate a column of cells with each of its values respectively. It parts from the current Date retrieved as a system's variable and tracks backwards until a condition is met.

The problem I'm having is that the 1^st element of my array is being formatted differently than the rest in its output cell despite being in the same loop and destination column.

The wrong output is:

13/01/2023
01/12/2023
01/11/2023
01/10/2023
01/09/2023
01/08/2023
01/07/2023

When what I was expecting instead is:

01/13/2023
01/12/2023
01/11/2023
01/10/2023
01/09/2023
01/08/2023
01/07/2023

My code is as follows:

Dim element() As Variant
Dim Tday, counter, adjustment As Integer

Tday = 7
i = Tday

'Here i use "adjustment" to offset days (e.g. when its monday but i need to start Friday and backwards.)
'For simplicity's sake I'm hardcoding it at 3 for this question.
adjustment = 3


While i <> 0

        counter = Tday - i
        'I'm Dynamically Allocate array's size as needed
        ReDim Preserve element(counter)
        element(counter) = Format(Date - (counter   adjustment), "mm/dd/yyyy")
        i = i - 1

Wend

'SO Far the array is correctly formatted  but...

'HERE IS THE PROBLEM: Reading the values of the array and placing them into cells returns an unexpected format in its first iteration:

For i = LBound(element) To UBound(element)
    ThisWorkbook.Sheets(1).Range("A" & 1   i) = Format(element(i), "mm/dd/yyyy")
Next i

As you'll notice I'm implementing the format function since I ultimately need the cells to be text and in the indicated date format mm/dd/yyyy for these will be the criteria for an advanced filter which retrieves the records with such dates in my source data table.

I kindly thank your input in advance.

I've already attempted to change the type of the elements array into Date, however this causes the unexpected behavior (dd/mm/yyyy) directly in the array itself instead of further down the code.
The format function appears a second time because it helped me maintain the desired format when assigning cells the values contained in the array. With the sole exception of the first value which won't assume the indicated date formatting.

CodePudding user response：

Format the cells as text if that's what you want.

Option Explicit
Sub macro1()

    Dim arDates() As String, i As Long
    Dim NoOfDays As Long, startday As Long
    
    NoOfDays = 7
    startday = 3 ' start wednesday
    
    ReDim arDates(1 To NoOfDays, 1 To 1)
    For i = 1 To NoOfDays
        arDates(i, 1) = Format(Date   1 - startday - i, "mm/dd/yyyy")
    Next
    
    With ThisWorkbook.Sheets(1).Range("A1").Resize(NoOfDays)
       .NumberFormat = "@"
       .Value2 = arDates
    End With
    
End Sub

CodePudding user response：

Your program change the format of the array, not the cell so you need to change the format of the cell:

To have cells as text


For i = LBound(element) To UBound(element)
        ThisWorkbook.Sheets(1).Range("A" & 1   i).NumberFormat = "@"

    ThisWorkbook.Sheets(1).Range("A" & 1   i).value = Format(element(i), "mm/dd/yyyy")
Next i