Issue when copying cells (defaults to mm/dd/yyyy when im wanting dd/mm/yyyy)

[markpem]

Hello All,

I have some code below that looks for any changes and finds the match on the other sheet and updates any changes, however the dates when transferred over go from the UK formatiing of dd/mm/yyyy to the US style date of mm/dd/yyyy making all the dates in the sheet incorrect.

Could someone help and advise how I make it stay the same way? Thanks!"

Dim s1rw As Long, s2rw As Long, col As Long, endcol As Long

    Sheets("Sheet1").Select
    With Sheets("Sheet2")
        s2rw = 2 ' Adjust to first data row #
        endcol = .Cells(s2rw - 1, 1).End(xlToRight).Column
        Do Until .Cells(s2rw, 1).Value = "" ' Loop through case #s
            s1rw = 0
            On Error Resume Next
            s1rw = Cells.Find(What:=.Cells(s2rw, 1).Value, LookIn:=xlFormulas, LookAt:=xlWhole).Row
            On Error GoTo 0
            If s1rw > 0 Then ' Found case #
                For col = 1 To endcol ' Loop through columns
                    If Cells(s1rw, col).Value <> "" Then
                        .Cells(s2rw, col).Value = Cells(s1rw, col).Value
                    End If
                  Next
            End If
          Rows(s2rw).Delete
            s2rw = s2rw + 1
        Loop
        .Select
    End With

[xladept]

Maybe:

If Cells(s1rw, Col).Value <> "" Then
    If IsDate(Cells(s1rw, Col).Value) Then
.Cells(s2rw, Col).Value = Format(Cells(s1rw, Col).Value, "dd/mm/yyyy")
Else
.Cells(s2rw, Col).Value = Cells(s1rw, Col).Value
End If: End If

**Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[markpem]

Hello

First of all XLadept I want to thank you for spending the time regading my issue but it doesnt look like it's worked.

I did a search and came up with: - http://stackoverflow.com/questions/9...-automatically

There is some info on there that might help on my code, would you be able to look?

Thanks

MarkPem

[cytop]

Also posted: http://www.ozgrid.com/forum/showthread.php?t=192352

[markpem]

Also posted: http://www.ozgrid.com/forum/showthread.php?t=192352

Hello, Yes your right. I did crosspost. I was blissfully unware that it was against forum rules and I have been shown the error of my ways.

[xladept]

Maybe if you format the post:

Format(.Cells(s2rw, Col), "dd/mm/yyyy") = Cells(s1rw, Col)