How chart time scale x-axis with another date serie

[Guest]

Hello all. I have this problem, I will need to create this chart that I don't
know how to.

Serie 1 is below which needs to be time scaled
Date Count
1/1/2006 12
1/2/2006 3
1/3/2006 5
2/1/2006 18
2/3/2006 4
2/18/2006 9
4/1/2006 18
7/4/2006 38

Serie 2 - needs to plot the three dates in serie 1's time scaled X-Axis with
symbols.

1/31/2006
2/18/2006
4/2/2006

I will look something like you have a line of count on a time-scaled chart,
with 3 dates point in the x-axis. Please give me some advice on how to do
it. Thank you all.

 

[Jon Peltier]

Make the chart with the first series as a line chart with a time scale axis.

Add the data for the second series. Convert this series to an XY series
(select the series, Chart menu > Chart Type). Put the XY series onto the
primary axis (double click, Axis tab).

- Jon

 

[Guest]

Jon:

Thank you s much for your reply. You answered my last time's similar
question too.

Is it possible to generate this kind of chart from VBA code? Thanks again.

 

[Jon Peltier]

If you can do it manually, you can do it in VBA.

I have a bunch of examples and hints on programming Excel charts:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

Use the macro recorder (one of my two favorite programming tools, Google's
the other) to get some rough code, and use the page above to refine it.
Basically, record a macro while making a line chart and then changing one
series to an XY style.

The macro first looks something like this:

Sub Macro1()
' Macro recorded 11/10/2006 by Jon Peltier
Range("B6:C13").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B6:C13")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveWindow.Visible = False
Windows("Book3").Activate
Range("E6:F11").Select
Selection.Copy
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
ActiveChart.SeriesCollection(2).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(2).ChartType = xlXYScatterLines
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).AxisGroup = 1
End Sub

Fix it up a little:

Sub Macro1()
' Macro fixed up 11/10/2006 by Jon Peltier
Dim cht As Chart
Dim wks As Worksheet
Dim srs As Series

Set wks = ActiveSheet
Set cht = wks.ChartObjects.Add(250, 150, 450, 250).Chart
With cht
.SetSourceData Source:=wks.Range("B6:C13")
.ChartType = xlLineMarkers
Set srs = .SeriesCollection.NewSeries
With srs
.Values = wks.Range("F7:F11")
.XValues = wks.Range("E7:E11")
.Name = wks.Range("F6")
.ChartType = xlXYScatterLines
.AxisGroup = 1
End With
End With
End Sub

- Jon

 

[Guest]

Thank you so very much Jon. I do appreciate your kindness to help.