CODEWITHSUNDEEP
vbaexcel
excelguy
excelguy

Reputation: 1624

VBA, Date formatting issue

I have a code that

1) compares dates from Col X to Col Y.

2)paste dates to col Y if there is no match between columns.

Column X my format looks like 

08/15/2013
09/12/2013
10/03/2013

But when it pastes to column Y it goes,

15/08/2013
12/09/2013
03/10/2013

How can I format my paste to go to dd/mm/yyyy.

Added more code to show array:

   ReDim PasteArr(1 To 1, 1 To 6)
    subcount = 1

    For Cell1 = 1 To UBound(DataArr(), 1)
        For Each Cell2 In BusDates()
            If DataArr(Cell1, 1) Like Cell2 Then
                Matched = True
                Exit For                                      'if it matches it will exit
            ElseIf Cell2 Like BusDates(UBound(BusDates), 1) Then 'if it gets to the end, it's truly unique and needs to be added

                For index = 1 To 6
                    PasteArr(subcount, index) = DataArr(Cell1, index)
                Next index

                subcount = subcount + 1

                PasteArr = Application.Transpose(PasteArr)
                ReDim Preserve PasteArr(1 To 6, 1 To subcount)
                PasteArr = Application.Transpose(PasteArr)

                Matched = False

            End If
        Next Cell2

        If Matched = False Then
            BusDates = Application.Transpose(BusDates)
            ReDim Preserve BusDates(1 To UBound(BusDates) + 1)
            BusDates = Application.Transpose(BusDates)
            BusDates(UBound(BusDates), 1) = DataArr(Cell1, 1)
        End If

    Next Cell1
    Worksheets("stacks").Range("M" & LastRow + 1 & ":" & Cells(LastRow + UBound(PasteArr, 1) - 1, 18).Address).Value = PasteArr

What i've tried: Changing the format of cells

enter image description here

15/08/2013
12/09/2013
03/10/2013

which is now the correct format for column X.

But this is pasting into column Y as:

enter image description here

which is 

15/08/2013 - correct
09/12/2013 - incorrect
10/03/2013 - incorrect.

Upvotes: 2

Views: 2259

Answers (5)

steve biko
steve biko

Reputation: 171

The trick is to assign values from Column X to column Y using then Range.value property. This will ensure that the data is transferred in the same format it exist in X column (whether date, number, string... etc). If you set the same display on two columns then you will see same thing on both columns.

I find your code convoluted so I have rewritten the logic to search first column 'X' and put unique occurrences on second column 'Y'

Public Sub findOrAdd()
    Const COLUMN_SOURCE = "B"
    Const COLUMN_DEST = "D"
    Const ROW_STARTDATA = 2

    Dim x As Long, y As Long
    Dim foundMatch As Boolean

    Dim sht As Worksheet
    Set sht = Sheet1

    x = ROW_STARTDATA
    Do Until sht.Range(COLUMN_SOURCE & x).Value = "" 'X -variable loop walks through all cells in source column
        Debug.Print "Doing row " & x & " =" & sht.Range(COLUMN_SOURCE & x).Value
        foundMatch = False
        'search for value of current cell in destcells
        y = ROW_STARTDATA
        Do Until sht.Range(COLUMN_DEST & y).Value = "" 'Y -variable loop walks through all cells in dest column - checking if it exists
            If sht.Range(COLUMN_SOURCE & x).Value = sht.Range(COLUMN_DEST & y).Value Then
                'match found stop searching and do nothing
                foundMatch = True
                Exit Do
            End If
            y = y + 1
        Loop

        If foundMatch = False Then
            'Y loop completed and match was not found.
            'Append content as end of destination cells
            sht.Range(COLUMN_DEST & y).Value = sht.Range(COLUMN_SOURCE & x).Value

            '** NOTE value is added by assigned cell.value, which is not pasting.
            '** If the formats of the source and destination address are done the same then they will display the same thing in excel
        End If
        x = x + 1
    Loop
End Sub

Note: Blank rows will cause loops to exit

Upvotes: 1

Pspl
Pspl

Reputation: 1474

I live in Portugal and sometimes I have issues of the same nature regarding the date formatting options. Usually, what I do (and normally it works), is using and abusing of the DateSerial function. For instance, if I wanted to populate your PasteArr array I would do:

PasteArr(subcount, index) = DateSerial(Year(DataArr(Cell1, index)), Month(DataArr(Cell1, index)), Day(DataArr(Cell1, index)))

To write a date on a cell I do the following:

Worksheets("stacks").cells("M" & LastRow + 1).formulaR1C1 = DateSerial(Year(PasteArr(subcount, index)), Month(PasteArr(subcount, index)), Day(PasteArr(subcount, index)))

Honesty the previous procedure seems a little bit silly. Really, it does! However it solves the problem with the date formatting dd/mm/yyyy vs mm/dd/yyyy problem. If you ask me why, I don't know exactly how it works! But it works every time!

Upvotes: 1

TechnoDabbler
TechnoDabbler

Reputation: 1265

Per my comment above, given you're using arrays and not copying ranges/cells directly. If your arrays are declared as string arrays, you will get the issue of transposed days/months. For example:

enter image description here

Could that be the issue?

Upvotes: 1

user11909
user11909

Reputation: 1265

Use dd/MM/yyyy as cell format. Lowercase m stands for minutes, uppercase M for months.

Upvotes: 2

Dominique
Dominique

Reputation: 17493

Check your cell format. It should be:

Number
  Custom
    dd/mm/yyyy (depending on your locale, in my case (Dutch) it's dd/mm/jjjj)

Upvotes: 1

Related Questions