Stubborn date format and range copy

[Guest]

Hi, I would like to recreate this formula (see below) to a whole column but I
am incapable to do so. The problem come from the date format which I cannot
change with usual format. They only way I found out was to select the cell,
then copy the formula and paste it using the text importation wizard (that I
can see when I paste the date). Any other way to go throught this would be
accepted.

Thanks!

Range("M4").Select
ActiveCell.FormulaR1C1 = "09/27/2006"
Range("W4").Select
ActiveSheet.PasteSpecial Format:="Texte Unicode", Link:=False, _
DisplayAsIcon:=False

 

[Gary Keramidas]

you can see if this gives you any ideas

Sub test2()
Range("M4").Value = "09/27/2006"

With Range("W4")
Range("M4").Copy
..PasteSpecial xlPasteAll
End With
End Sub

 

[Guest]

Hi,
It work to some extent It change the date to it's good value wich is good.
However, I want the macro to read every cell beside "M4". Each of these cell
will have a different date and I want to copy it to another cell to keep what
I was sent. Also, I am not able to find a command that will repeat the same
line (ex.; select M4, copy to N4, Select M5, copy to N5......)