title: Time series values not matching the horizontal axis on my Excel chart
body: <p><strong>SOLVED BY USING X-Y PLOT INSTEAD OF LINE CHART</strong></p>
  
  <p>I want to create a chart in Excel that contains multiple series that are linked in terms of date (i.e. one series ends on 2018/12/31 the next begins 2018/12/31). The raw data is taken from a sheet with two columns; the date column and the value column.</p>
  
  <p>The chart in question here is called 'Beta' and contains 3 time series.
  This is my attempt: </p>
  
  <pre><code>Sub UpdateGraph
  Dim ws             As Worksheet
  Dim rng_NF3        As Range
  Dim rng_Barra      As Range
  Dim rng_NF3_Date   As Range
  Dim rng_Barra_Date As Range
  Dim rng_Total_Date As Range
  Dim rng_Total_Val  As Range
  Dim cht_Name       As String
  Dim ArrDate        As Variant
  Dim arrValues      As Variant
  
  
          cht_Name = "Beta"  ' Example
          Set ws = Activesheet
          Set cht = ws.ChartObjects(cht_Name)
          Set rng_NF3 = ws.Range(ws.Cells(2, 4), ws.Cells(200, 4)) ' Set range of values from NF3 (GEM3)
          Set rng_Barra = ws.Range(ws.Cells(201, 4), ws.Cells(500, 4)) ' Set range of values Barra
          Set rng_NF3_Date = rng_NF3.Offset(0, -1) ' Set range of date for NF3 observations
          Set rng_Barra_Date = rng_Barra.Offset(0, -1) ' Set range of date for Barra observations
          Set rng_Total_Date = Union(rng_NF3_Date, rng_Barra_Date)
          Set rng_Total_Val = Union(rng_NF3, rng_Barra)
          ArrDate = rng_Total_Date.Value
          arrValues = rng_Total_Val.Value
          With cht.Chart
              .FullSeriesCollection(1).Format.Line.ForeColor.RGB = ws.Cells(1, 20).Interior.Color
              .FullSeriesCollection(2).Format.Line.ForeColor.RGB = ws.Cells(2, 20).Interior.Color
              .FullSeriesCollection(1).Values = rng_NF3 ' Value series for NF3
              .FullSeriesCollection(2).Values = rng_Barra ' Value series for Barra
              .FullSeriesCollection(1).XValues = rng_NF3_Date
              .FullSeriesCollection(2).XValues = rng_Barra_Date
               If cht_Name = "Beta" Then ' Defining Beta = 1
               .FullSeriesCollection(3).Format.Line.ForeColor.RGB = ws.Cells(1, 21).Interior.Color '  Color
               .FullSeriesCollection(3).Values = 1 ' Should be a range with the same number in all elements, not working but another issue.
               .FullSeriesCollection(3).XValues = rng_Total_Date
               End If
               ' X-axis
               With cht.Chart.Axes(xlCategory)
                  .CategoryType = xlTimeScale
                  '.MajorUnitScale = xlMonths
                  .MinimumScale = ArrDate(LBound(ArrDate, 1), 1)
                  .MaximumScale = ArrDate(UBound(ArrDate, 1), 1)
                  .MajorUnit = 3 ' Manually set x-axis unit scale
                  '.MajorUnitIsAuto = True
               End With
              ' Y-axis
              With cht.Chart.Axes(xlValue)
                  .MinimumScaleIsAuto = True
                  .MaximumScaleIsAuto = True
                  .MajorUnitIsAuto = True
              End With
          End With
  
  End sub
  </code></pre>
  
  <p>I wanted the series to continue after each other but instead they both begin at the first date of the horizontal axis. I suspect Excel does not recognize my attempt to match the date of my time series to the date of the x-axis. </p>
  
  <p>Here is how my figure looks. I am using a line chart.
  <a href="https://i.stack.imgur.com/iZkDX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iZkDX.png" alt="enter image description here"></a></p>