Excel VBA - Convert date to display as text in the form MMM YYYY-CodePudding

I would like to convert Column A or Range A from date format to display as text format MMM YYYY.

Here is the worksheet layout:

However, no changes occur as I run the code:

Sub CONVERT_DATE()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open ("MyWorkbook.xlsx")
Workbooks("MyWorkbook.xlsx").Activate

Dim ws As Worksheet
Set ws = Workbooks("MyWorkbook.xlsx").Sheets("MyWorkSheet")

Dim wsLastRow As Long
wsLastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

'Convert date to display as text in the format mmm yyyy

ws.Range("A2:A" & wsLastRow).Text = Format(ws.Range("A2:A" & wsLastRow).Text, "mmm yyyy")

Workbooks("MyWorkbook.xlsx").Close SaveChanges:=True

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Can anyone spot the issue(s)?

Many thanks!

CodePudding user response：

Use NumberFormat instead of trying to set the Text

Sub CONVERT_DATE()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open ("MyWorkbook.xlsx")
Workbooks("MyWorkbook.xlsx").Activate

Dim ws As Worksheet
Set ws = Workbooks("MyWorkbook.xlsx").Sheets("MyWorkSheet")

Dim wsLastRow As Long
wsLastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

'Convert date to display as text in the format mmm yyyy

' change this line
ws.Range("A2:A" & wsLastRow).NumberFormat = "mmm yyyy"

Workbooks("MyWorkbook.xlsx").Close SaveChanges:=True

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

CodePudding user response：

Instead of this line

ws.Range("A2:A" & wsLastRow).Text = Format(ws.Range("A2:A" & wsLastRow).Text, "mmm yyyy")

Use NumberFormat to change the format of your range

ws.Range("A2:A" & wsLastRow).NumberFormat = "mmm yyyy"