I tried to apply what I have found online, but I wasn't able to apply it on my code. The axis x give me some issue, I can't choose the existing point on axis x on my chart (see line highlighted). The way I do it create a new x-axis with 2 values.
' Create an empty Chart
Sheet1.Shapes.AddChart2.Select
Sheet1.ChartObjects(1).Name = "Workforce"
' Chart Parent (ChartObject)
ActiveChart.Parent.Left = 160
ActiveChart.Parent.Top = 160
ActiveChart.Parent.Width = 1080
ActiveChart.Parent.Height = 360
ActiveChart.ChartArea.RoundedCorners = True
ActiveChart.ChartArea.Border.ColorIndex = 1
' Chart Type
ActiveChart.ChartType = xlLineMarkers
' Chart Source
ActiveChart.SetSourceData Source:=Range("Project!$N$1:$BN$3")
' Chart Title
ActiveChart.ChartTitle.Text = "Workforce in Department"
' Chart Legend
ActiveChart.HasLegend = True
ActiveChart.Legend.Position = xlLegendPositionTop
' Chart Axis X
ActiveChart.Axes(xlCategory).HasTitle = True
ActiveChart.Axes(xlCategory).AxisTitle.Text = "53 Weeks in Year " & Right(Sheet3.Cells(1, 5).Value, 4)
' Chart Axis Y
ActiveChart.Axes(xlValue).HasTitle = True
ActiveChart.Axes(xlValue).AxisTitle.Text = "Number of Scientist"
' Chart Series
' Serie Theory Data
ActiveChart.SeriesCollection(1).Select
Selection.Border.ColorIndex = 5
Selection.Border.Weight = xlThin
Selection.Border.LineStyle = xlDot
Selection.MarkerBackgroundColor = RGB(0, 0, 0)
Selection.MarkerForegroundColor = RGB(0, 0, 0)
Selection.Name = "Theory"
' Serie Reality Data
ActiveChart.SeriesCollection(2).Select
Selection.Border.ColorIndex = 5
Selection.Border.Weight = xlThin
Selection.Border.LineStyle = xlContinuous
Selection.MarkerBackgroundColor = RGB(0, 0, 255)
Selection.MarkerForegroundColor = RGB(0, 0, 255)
Selection.Name = "Reality"
' Serie Wanted Data
ActiveChart.SeriesCollection(3).Select
Selection.Border.ColorIndex = 3
Selection.Border.Weight = xlThin
Selection.Border.LineStyle = xlDot
Selection.MarkerBackgroundColor = RGB(255, 0, 0)
Selection.MarkerForegroundColor = RGB(255, 0, 0)
Selection.Name = "Wanted"
' Y axis adjusted
Dim n As Integer
n = Application.WorksheetFunction.Sum(Sheet3.Range("N1:BN1")) / 53
Sheet1.ChartObjects(1).Select
ActiveChart.Axes(xlValue).MinimumScale = n - 15
Sheet1.ChartObjects(1).Select
ActiveChart.Axes(xlValue).MaximumScale = n + 5
' Vertical line
Dim ns As Series
Sheet1.ChartObjects(1).Select
ActiveChart.SeriesCollection.NewSeries.Name = "=""Current week"""
Set ns = ActiveChart.SeriesCollection("Current week")
ns.XValues = Array(11, 11) '<======= PROBLEM! ==================================
ns.Values = Array(n - 15, n + 5)
I would like to put a variable instead of the number 11, but first I have to make it work with this number(Week 11). As usual, any ideas are welcome. Thanks
