Changing date from day to Month

[Klartigue]

In column E of a spreadsheet, I have dates appearing in the short date form: 7/12/2011, 5/10/2011, etc... In my macro, I would like transform these dates to say July and May respectively. So I would like the macro to identify the date in column E and be able to recognize what month that is and place the month in column F.

Any ideas??

Thanks for the help!

[JKwan]

try this:

Sub ConvertMonth()
Dim LastRow As Long
Dim lRow As Long
LastRow = FindLastRow(Worksheets("Sheet1"), "E")
For lRow = 1 To LastRow
Worksheets("Sheet1").Cells(lRow, "F") = Format(Worksheets("Sheet1").Cells(lRow, "E"), "mmmm")
Next lRow
End Sub

Public Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
FindLastRow = WS.Range(ColumnLetter & "65536").End(xlUp).Row
End Function

[Klartigue]

I am working with something like this but it wont work..do you see something wrong??


Sub Month()
Dim LastRow As Long
Dim i As Long
Windows("Volume.xlsx").Activate
With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = 2 To LastRow
Worksheets("Sheet1").Cells(lRow, "F") = Format(Worksheets("Sheet1").Cells(lRow, "E"), "mmmm")
Next i
End With
End Sub

[JKwan]

Well, are you getting errors, if so, what is the error?
I only have Excel 2003.

[JKwan]

silly me, what am I thinking...... the would be the same 2007 and 2003. You forgot to change lRow to i, your line should be:

Worksheets("Sheet1").Cells(i, "F") = Format(Worksheets("Sheet1").Cells(i, "E"), "mmmm")

[Simon Lloyd]

Why not make it so that if you change a data the month will display even after the macro has run?

Sub Month_To_Cell()
Dim rng As Range, MyCell As Range
Set rng = Sheets("Sheet1").Range("E2:E" & Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row)
For Each MyCell In rng
MyCell.Offset(0, 1).Formula = "=Text(" & MyCell.Address & "," & Chr(34) & "mmmm" & Chr(34) & ")"
Next MyCell
End Sub

[Klartigue]

Simon, i tried this..


Sub Month_To_Cell()
Dim rng As Range, MyCell As Range
Set rng = Sheets("Sheet1").Range("E2:E" & Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row)
For Each MyCell In rng
MyCell.Offset(0, 1).Formula = "=Text(" & MyCell.Address & "," & Chr(34) & "mmmm" & Chr(34) & ")"
Next MyCell
End Sub

but for this line the error is subscript out of range.

Set rng = Sheets("Sheet1").Range("E2:E" & Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row)

If it helps, the sheet i am working with is Volume.xlsx

[Klartigue]

I am just going to do it this way..


Sub Macro2()
' Macro2 Macro
Dim LastRow As Long
Dim i As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 2 To LastRow
.Cells(i, "D").Select
Selection.Copy
Range("E2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.NumberFormat = "[$-409]mmmm-yy;@"
Next i
End With
End Sub


But this formula does not copy from last row to D2 into column E. it only copies the first one. How do i get this to copy all occupied cells in column D and paste them into column E. I thought i had the lastrow function correct

[Simon Lloyd]

Simon, i tried this..


Sub Month_To_Cell()
Dim rng As Range, MyCell As Range
Set rng = Sheets("Sheet1").Range("E2:E" & Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row)
For Each MyCell In rng
MyCell.Offset(0, 1).Formula = "=Text(" & MyCell.Address & "," & Chr(34) & "mmmm" & Chr(34) & ")"
Next MyCell
End Sub

but for this line the error is subscript out of range.

Set rng = Sheets("Sheet1").Range("E2:E" & Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row)

If it helps, the sheet i am working with is Volume.xlsx

Firstly did you change Sheet1 for your actual sheet?, you say you are working on Volume.xlsx thats a 2007 to 2011 workbook that does NOT have macro's enabled. I don't see anything in your original question that states you are trying to manipulate a second workbook from the active one, can you clarify what you are attempting to do?

Anyway, if your code is displaying what you really want to do then just use this

Sub Copy_n_Convert()
Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row).Value = Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row).Value
Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).NumberFormat = "[$-409]mmmm-yy;@"
End Sub

[Klartigue]

thats works perfectly, thank you!