Thread: Date values not appearing correclty in the x-axis

Thanks for the reply Pope. I will check and get back.

But i went through an MS Office link for creating XY (Scatter) and Line charts . http://office.microsoft.com/en-us/ex...001054840.aspx
As per the link :

Axis type and scaling differences

Scatter charts and Line charts also differ in the way they use axis types and scaling:

The x-axis of a Scatter chart can only be a value axis. This means that only numeric data is displayed on this axis. To display this numeric data with greater flexibility, you can change the scaling options on this axis.
The x-axis of a Line chart can be either a category or a time axis, both of which can display non-numeric data. Depending on which data is used, the scaling options are limited compared to the scaling options of the Scatter chart's x (value) axis.

Is it is so that exact date value can't be plotted on the x-axis. And also is there any way that i can plot data labels on the graph because i have a doubt about this also as it picks the value from the x-axis as well.

If it is so then Is there any other workaround available to achieve the same result.

Originally Posted by Andy Pope

Firstly the data in column A is just text and not a true date value.
Secondly only 1 series in the chart is even using column A as the X values.

Thanks for your input. Can you please suggest how can i get the exact values of the date displayed on my x-axis and display the series accordingly. I tried using xlLine in place of xlXYscatter but it takes the date values for only the first series.
Is there any way i can modify my code to achieve the desired result.

hi

i have modifed my code a bit.
Now the problem i am facing is that still my x-axis is not picking exact date values.
In case i use xlLine as chart type, the x-axis shows the date values but only for the first series. the other series do not come as required.

My requirement is to plot different series based on vendors..my x-axis should reflect exact date and y-axis is okay as auto-scaled.

sub draw_graph()
Dim lngRow As Long
Dim lngStartRow As Long
Dim objChart As Chart
Dim objSeries As Series
Set objChart = ActiveSheet.ChartObjects.Add( 200, 50, 500, 325).Chart
objChart.ChartType = xlXYScatterLines
lngStartRow = 4
ngRow = 4
With ActiveSheet
lngStartRow = 4
lngRow = 4
Do While Len(.Cells(lngRow, 1).Value) > 0
If .Cells(lngStartRow, 3) <> .Cells(lngRow, 3) Then
Set objSeries = objChart.SeriesCollection.NewSeries
objSeries.Name = .Cells(lngStartRow, 3).Value
objSeries.XValues = Range("A" & lngStartRow, "A" & lngRow - 1)
objSeries.Values = Range("B" & lngStartRow, "B" & lngRow - 1)
objSeries.HasDataLabels =True
objSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue)
lngStartRow = lngRow
End If
lngRow = lngRow + 1
Loop
Set objSeries = objChart.SeriesCollection.NewSeries
objSeries.Name = .Cells(lngStartRow, 3).Value
objSeries.XValues = Range("A" & lngStartRow, "A" & lngRow - 1)
objSeries.Values = Range("B" & lngStartRow, "B" & lngRow - 1)
objSeries.HasDataLabels =True
objSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue)
lngStartRow = lngRow
End With
objChart.hasTitle = True 
objChart.ChartTitle.Characters.Text = " Commodity Price Graph " 
OBJCHART.AXES(XLVALUE, XLPRIMARY).HASTITLE = TRUE
OBJCHART.AXES(XLVALUE, XLPRIMARY).AXISTITLE.CHARACTERS.TEXT = "Price(INR)"
objChart.Axes(xlCategory, xlPrimary).HasTitle = True
objChart.axes(xlCategory, xlprimary).AXISTITLE.CHARACTERS.TEXT = "DATE"'.
END SUB

Hi

i have written code for a formula. but at run time it is giving me object defined error.

The formula is for adding 0 before the value if value is less than 10.

Range("D4").Select
ActiveCell.FormulaR1C1 = _
"= VALUE(IF((RC[-2]/10)<1,CONCATENATE(0,RC[-2]),RC[-2])&""/""&RC[-3]&""/""&RC[-1])"

Any help will be appreciatwed.

Anyways, this is the final code which is running in the excel. Thanks for all your help.

Sub draw_graph()

Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    
    Range("D4").Select
    ActiveCell.FormulaR1C1 = _
        "=VALUE(IF((RC[-2]/10)<1,CONCATENATE(0,RC[-2]),RC[-2])&""/""&RC[-3]&""/""&RC[-1])"
    Range("D4").Select
    Selection.AutoFill Destination:=Range("D4:D9") 'Need to pass the range dynamically
    Columns("D:D").Select
    Selection.NumberFormat = "m/d/yyyy"
    Selection.ColumnWidth = 10.6
    ActiveCell.FormulaR1C1 = ""
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:C").Select
    Range("C1").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Date"

Dim ThisSeries As String

Dim lngRow As Long
Dim lngStartRow As Long
Dim objChart As Chart
Dim objSeries As Series
Set objChart = ActiveSheet.ChartObjects.Add( _
200, 50, 700, 325).Chart
objChart.ChartType = xlXYScatterLines
With ActiveSheet
lngStartRow = 4
lngRow = 4

ThisSeries = .Cells(lngStartRow + 1, 3).Value

Do While Len(.Cells(lngRow, 1).Value) > 0

ThisSeries = .Cells(lngStartRow + 1, 3).Value

If Not .Cells(lngRow + 1, 3).Value = ThisSeries Then

Set objSeries = objChart.SeriesCollection.NewSeries
objSeries.Name = .Cells(lngStartRow + 1, 3).Value
objSeries.XValues = .Range("A" & lngStartRow + 1, "A" & lngRow)

objSeries.Values = _
Range("B" & lngStartRow + 1, "B" & lngRow)
lngStartRow = lngRow
End If
lngRow = lngRow + 1

Loop
End With
objChart.HasTitle = True
objChart.ChartTitle.Characters.Text = " Commodity Price Graph "
objChart.Axes(xlValue, xlPrimary).HasTitle = True
objChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price(INR)"
objChart.Axes(xlCategory, xlPrimary).HasTitle = True
objChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "DATE"

End Sub