VBA to change x-axis category labels to Text or Timescale

[Latisha Antony]

Hi,

I am extremely new with VBA and have been trying for several hours on on how to change my x-axis label which now reads "1 to 12" to a string/text /timescale "Jan to Dec".

my data structure looks similar to the below

Dept Jan Feb March
w 20 30 20
x 40 34 54
y 34 20 41
z 51 40 30

The code I have written thus far looks someting like this

Sub main()
'variable declaration
Dim i As Long
Dim LastRow As Long
Dim LastColumn As Long
Dim chrt As Chart
Dim xrng As Range

'Find the last used row
LastRow = Sheets("Sheet1").Range("A600").End(xlUp).Row
'Find the last used column
LastColumn = Sheets("Sheet1").Range("A1").End(xlToRight).Column
'Looping from second row till last row which has the data
For i = 2 To LastRow

'Sheet 2 is selected bcoz charts will be inserted here
Sheets("Sheet2").Select
'Adds chart to the sheet
Set chrt = Sheets("Sheet2").Shapes.AddChart.Chart

'sets the chart type
chrt.ChartType = xlLine
'X axis name
chrt.Axes(xlCategory, xlPrimary).HasTitle = True
chrt.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "MONTHS"

chrt.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "CWT"

'now the line chart is added...setting its data source here
With Sheets("Sheet1")
chrt.SetSourceData Source:=.Range(.Cells(i, 1), .Cells(i, LastColumn))
End With

Next


---------------------------------------------------------------------------------------------------------------------------------


I really hope someone can help with this. I'm desperate!

 

[Latisha Antony]

I have cleaned up the code a lil but the problem still remains..

Sub main()
'variable declaration
Dim i As Long
Dim LastRow As Long
Dim LastColumn As Long
Dim chrt As Chart
'Find the last used row
LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
'Find the last used column
LastColumn = Sheets("Sheet1").Range("A1").End(xlToRight).Column
'Looping from second row till last row which has the data
For i = 2 To LastRow
'Sheet 2 is selected bcoz charts will be inserted here
Sheets("Sheet2").Select
'Adds chart to the sheet
Set chrt = Sheets("Sheet2").Shapes.AddChart.Chart
'sets the chart type
chrt.ChartType = xlLine
chrt.Axes(xlCategory, xlPrimary).HasTitle = True
chrt.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
chrt.Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
chrt.Axes(xlCategory, xlPrimary).BaseUnit = xlMonths
chrt.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "mmm"

'now the line chart is added...setting its data source here
With Sheets("Sheet1")
chrt.SetSourceData Source:=.Range(.Cells(i, 1), .Cells(i, LastColumn))
End With

'Left & top are used to adjust the position of chart on sheet
chrt.ChartArea.Left = 1
chrt.ChartArea.Top = (i - 2) * chrt.ChartArea.Height
Next
End Sub


==============================================================

My x-axis still shows


_______________________________________ instead of
Jan Jan Jan Jan Jan Jan Jan Jan Jan Jan Jan Jan


_________________________________________
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec