Chart labels VBA

[Noz2k]

So I have a workbook setup where the user inputs a date range, clicks a button and it looks through sheets for the specified range and calculates the sum or average where appropriate and puts the calculated values into a table on a summary sheet.

The sheets that are searched through are all labelled Week1 through to Week52, using X variable to determine the number.

On the summary sheet I have a chart which is used to show the trend over the time period. However the problem I have is that I want the X-axis (the category axis) to begin with the number of the first week in the range, at the moment it begins with number 1.

If the range is Week7 to Week10 I would want the x-axis to read 7, 8, 9, 10. Rather than 1, 2, 3, 4.

Anyway here is my code for the chart at the moment

Sheets("Sheet2").Shapes.AddChart.Select
    r = 2
    C = 2
    With ActiveChart
    .Parent.Name = ws2.Range("A1").Offset(Y - 1, 0)
    .Parent.Width = 800
    .Parent.Height = 400
    .ChartType = xlLine
    .SetSourceData Source:=wsm.Range("C1:C" & X)
    .SeriesCollection(1).Name = ws2.Range("C3")
    .HasTitle = True
    .ChartTitle.Text = ws2.Range("A1").Offset(Y - 1, 0)
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Text = (DF - 6) & " to " & DT
    .Axes(xlCategory).AxisBetweenCategories = False
    .Axes(xlValue).MajorGridlines.Delete
    If ws2.Range("W3").EntireColumn.Hidden = True Then
Do Until r = 20
    .SeriesCollection.NewSeries
    .SeriesCollection(C).Values = wsm.Range("C1:C" & X).Offset(0, r)
    .SeriesCollection(C).Name = ws2.Range("C3").Offset(0, r)
    r = r + 2
    C = C + 1
    Loop
ElseIf ws2.Range("Y3").EntireColumn.Hidden = True Then
    Do Until r = 22
    .SeriesCollection.NewSeries
    .SeriesCollection(C).Values = wsm.Range("C1:C" & X).Offset(0, r)
    .SeriesCollection(C).Name = ws2.Range("C3").Offset(0, r)
    r = r + 2
    C = C + 1
    Loop
ElseIf ws2.Range("AA3").EntireColumn.Hidden = True Then
    Do Until r = 24
    .SeriesCollection.NewSeries
    .SeriesCollection(C).Values = wsm.Range("C1:C" & X).Offset(0, r)
    .SeriesCollection(C).Name = ws2.Range("C3").Offset(0, r)
    r = r + 2
    C = C + 1
    Loop
ElseIf ws2.Range("AC3").EntireColumn.Hidden = True Then
    Do Until r = 26
    .SeriesCollection.NewSeries
    .SeriesCollection(C).Values = wsm.Range("C1:C" & X).Offset(0, r)
    .SeriesCollection(C).Name = ws2.Range("C3").Offset(0, r)
    r = r + 2
    C = C + 1
    Loop
End If
End With

Any help would be greatly appreciated, have searched a lot on google but can't seem to find an answer as to how to set the first value of the x-axis

 

[Noz2k]

Anyone? If I need to explain myself more clearly, then please let me know. Cannot find a solution to this anywhere and it's really frustrating.

It seems like it should be something which is really simple to do

 

[Noz2k]

Pretty sure I need to add something along the lines of this

.SeriesCollection(1).XValues = WeekSNum:WeekSNum + X

Where WeekSNum is the first value and WeekSNum + X is the last value.

But the way I've done it there doesn't work, and I don't know how to increment by 1.

 

[Jon Peltier]

Do you have your week numbers in a range of cells? Then you can use

.SeriesCollection(i).XValues = {some range}

Or you could try something like

.SeriesCollection(i).XValues = Array(7, 8, 9, 10)

And shouldn't you be using an XY chart instead of a line chart? The XY chart treats the X values as numbers, while the line chart treats them as non-numeric labels.

 

[Noz2k]

Do you have your week numbers in a range of cells? Then you can use

.SeriesCollection(i).XValues = {some range}

Or you could try something like

.SeriesCollection(i).XValues = Array(7, 8, 9, 10)

And shouldn't you be using an XY chart instead of a line chart? The XY chart treats the X values as numbers, while the line chart treats them as non-numeric labels.

I think it's fine as a line chart, the labels could just as well be named Week1, Week2, Week3, Week4, etc.

How do I build the array? (I'm not very familiar with using arrays). At the moment I have 3 variables which determine the week numbers. 'WeekSNum' which gives me the starting week, 'Numloops' which tells me how many times to loop through until I reach the last week in the specified range, and 'X' which increments by 1 until equals 'NumLoops'.

I'm guessing that at the start of each loop, I would have to put in some code which posted the sheet number into the array, but I don't know how this is done?

 

[Jon Peltier]

I built the array for you:

.SeriesCollection(i).XValues = Array(7, 8, 9, 10)

Since you have a particular starting week which I'll call iWeek, use

.SeriesCollection(i).XValues = Array(iWeek, iWeek + 1, iWeek + 2, iWeek + 3)

 

[Noz2k]

ok, but do I not need something in there to specify the amount of values to add to the array?

That would work for the example of week 7 - 10, but what if the range is week 5 - 50?

 

[Jon Peltier]

Okay, so you need to declare an array and populate it.

If the starting value is 5 and the ending value is 50:

Dim MyArray(5 To 50) As Double
Dim iCounter As Long
For iCounter = 5 To 50
    MyArray(iCounter) = iCounter
Next

If the starting and ending values are calculated:

Dim MyArray(5 To 50) As Double
Dim iStart As Long
Dim iEnd As Long
Dim iCounter As Long

' calculate iStart and iEnd here using whatever algorithm

For iCounter = iStart To iEnd
    MyArray(iCounter) = iCounter
Next

 

[Noz2k]

Thanks for all your help Jon.

Have finally got it working, I decided to go with the range option that you suggested rather than an array, but it is still extremely handy to have this as a reference point to go back to for the next time I encounter a similar problem.

So it is now just set up to post the week number to an unused cell on a hidden sheet, and then uses your code to reference this range with X as a variable for the amount of values in the range, so the 2 lines of code I added to each of my chart modules was

wsm.Range("cz1").Offset(X,0) = p

This posts the Week Number (P) to the relevant cell and I already have code in the loop which increments X, and in turn P after this.

and later in the add chart code

.seriescollection(1).XValues = wsm.Range("CZ1:CZ" & X)

Once again, thanks for all the help.