2

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.
Improve this question
21
  • 2
    Make sure both columns are both formatted as dates and actually contain dates. Commented Oct 4, 2018 at 20:57
  • format the column to the date format you want. Commented Oct 4, 2018 at 20:57
  • 3
    What issue are you having? This seems like a formatting issue rather than a code issue? Commented Oct 4, 2018 at 20:58
  • 4
    What do you mean 'showing dd/mm/yyyy format' ? If they aren't showing 41501, 41529 and 41550 then they were never dates to begin with, just text that looks like dates. Commented Oct 4, 2018 at 21:22
  • 1
    Check this out: stackoverflow.com/questions/36043415/… so possible store it as a string and convert to a date once transposed?... Commented Oct 10, 2018 at 19:35

5 Answers 5

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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)
Improve this answer

4 Comments

its odd , ill do a custom format for dd/mm/yyyy , but when I go to see the number formatting again its just at the date section. Is this normal?
Maybe your sheet is protected in some way. Is it yours or did you get it from somebody?
got it from someone. See my updated question for some more details.
I think the problem could not be simply brushed aside by saying format issue. In my workplace we are living with similar type of problem for around last 12/13 years. However we worked around with some silly crude workaround approach suitable only in context to actual real time data we process, but I must acknowledge @Jeeped answer to post "Conflict between innertext VBA and excel" and "Excel VBA - Convert Text to Date"? Are also a great workaround. But the fundamental problem is still very much intact. .I am posting a new question giving the actual problem we faced
1

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?

Improve this answer

3 Comments

I don't declare any of my variables as strings though. They are all variant, long or boolean. My pastearr variable is variant.
@excelguy Variant isn't a datatype though, it just means the array can hold any datatype (and the compiler will do its best to find the correct datatype if the source is ambiguous). So it doesn't matter if the array is variant - if the content it holds is cast to String, you will get the trouble mentioned here.
@Vegard ... exactly. Even with an array declared as a Variant ... assign a date to it and you can see that the underlying type is Date ... and it through (for example) Application.Transpose and you'll see that the underlying type is changed to String.
1

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!

Improve this answer

Comments

1

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.