Date Serial VBA

[VbaHell]

Hello all, I m using Excel 2007

Can anyone help me please on this issue

Column E:E is populated with Dates IE 12/06/12
In Column AP:AP I need to show as June 12 as a TEXT using the data from column E:E.

IE
12/06/12 June 12
14/12/12 Dec 12
09/02/11 Feb 12

Etc

This needs to be changed using VBA code and I think it can be done using Date Serial but I am unsure how to complete this.

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Range("AP" & i).Value = Format(Range("E" & i).Value, "mmmm yy")
Next i
End Sub

 

[Domski]

Or:

With Range("AP1:AP" & Range("E" & Rows.Count).End(xlUp).Row) 
       .FormulaR1C1 = "=TEXT(RC[-37],""mmm yy"")" 
    .NumberFormat = "@"   
     .Value = .Value
End With

Dom

 

[Domski]

Cheers Vog, I'm pretty sure I added them and they vanished. I also then couldn't edit the post for some reason.

Dom

 

[VbaHell]

Thank you both for your reply, Domski your code works a treat the first time but unless I reformat the column as a date format before re running the code again it just brings over the TEXT Calculation

=TEXT(RC[-37],"mmm yy")

<tbody>
</tbody><colgroup><col></colgroup>

 

[Domski]

Fair point:

With Range("AP1:AP" & Range("E" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "General"
    .FormulaR1C1 = "=TEXT(RC[-37],""mmm yy"")"
    .NumberFormat = "@"
    .Value = .Value
End With

Dom

 

[VbaHell]

That's how I solved it also, many thanks again for your help, I see you have posted loads on this site, 6,953 since 2005, That's dedication