+ Reply to Thread
Results 1 to 1 of 1

Dates Of Time-Chart Treated As Categories Rather Than Dates

  1. #1
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2016
    Posts
    87

    Dates Of Time-Chart Treated As Categories Rather Than Dates

    Hi Everyone,

    I've got a problem very similar to this: http://www.excelforum.com/excel-char...ingle-day.html

    Only difference is that I seek the opposite result. I want the Excel to treat the dates as dates rather than categories.

    I'm using the following code to create a chart:


    Sub CreateChart()
    Dim ser As Series
    Dim WString As String
    Dim ArraySize As Long

    ArraySize = UBound(AM)

    Dim XValues() As Date
    Dim YValues2() As Double
    Dim Backup() As Long
    Dim Positions() As Long

    For i = 1 To UBound(AM) + 1

    ReDim Preserve Positions(i - 1)

    Positions(i - 1) = WorksheetFunction.Match(WorksheetFunction.Min(AL), AL, 0) - 1
    ReDim Preserve Backup(i - 1)
    ReDim Preserve YValues2(i - 1)

    Backup(i - 1) = CLng(AL(Positions(i - 1)))
    YValues2(i - 1) = AM(Positions(i - 1))

    AL(Positions(i - 1)) = Empty

    Next i

    Worksheets("Product Search 2").Shapes.AddChart.Select
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Text = "Time plot of price (£/kg)"
    ActiveChart.ChartTitle.Font.Size = 25

    For i = ActiveChart.SeriesCollection.Count To 1 Step -1
    ActiveChart.SeriesCollection(i).Delete
    Next i


    Set ser = ActiveChart.SeriesCollection.NewSeries
    With ser
    .XValues = Backup
    .Values = YValues2

    End With

    With ActiveChart

    With .Axes(xlCategory, xlPrimary)
    .CategoryType = xlTimeScale
    .TickLabels.NumberFormat = "dd/mmm/yyyy"
    .TickLabels.Font.Size = 22
    .HasTitle = True
    .AxisTitle.Characters.Text = "Date"
    .AxisTitle.Font.Size = 25
    '.MajorUnitScale = xlAutomatic
    '.TickLabelSpacingIsAuto = True
    MajorUnit = 4

    End With

    With .Axes(xlValue, xlPrimary)

    .TickLabels.Font.Size = 22
    .HasTitle = True
    .AxisTitle.Characters.Text = "Price (£/kg)"
    .AxisTitle.Font.Size = 25

    End With

    End With


    The plot initially appears as shown in the attached file "1st time plot.png". Strangely, if I cut and paste the the chart it appears exactly how I want it to as in the attached file; "time plot after cut and paste (how I want it).png". Also, if I save the Excel file, close and open it, it appears exactly how I want it to.

    Please can someone tell me how I can recode, so that the chart always treats the dates as dates rather than categories?

    Many thanks in advance,

    Rowan
    Attached Images Attached Images

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1