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

Latisha Antony

New Member
Joined
May 26, 2015
Messages
2
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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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


:confused::confused::confused:
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top