Jon,

Thanks a lot for your great solution. I almost got the
final result by running your macro. (Maybe I missed one or
two steps somewhere???)

So far, I got HLC chart, but without OpenLine. I think
this was due to my chart was not in a CategoryType. How do
you add these lines inside your macro?

With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale


Thanks for your advice,


-----Original Message-----
Shi -

This example assumes
- Data in columns, L-R, of Date, Open, High, Low, Close
- Dates are real numerical dates

The only error checking is whether a range of cells is
selected.

'' START CODE --------------------------------------------
----
Sub StockHLC_OpenLine()
Dim rData As Range
Dim rHLC As Range
Dim rOpen As Range
Dim rXVals As Range
Dim cHLCO As Chart

If TypeName(Selection) < "Range" Then
MsgBox "Select a range of cells containing
data," & vbCrLf _
& "in order: Date, Open, High. Low, Close",
vbExclamation, _
"Invalid selection"
Exit Sub
End If

Set rData = Selection
If rData.Columns.Count < 5 Then Set rData =
rData.CurrentRegion

Set rXVals = rData.Columns(1)
Set rHLC = Union(rXVals, rData.Columns(3).Resize(,
3))
Set rOpen = rData.Columns(2)

Set cHLCO = ActiveSheet.ChartObjects.Add(100, 100,
375, 225).Chart
With cHLCO
.SetSourceData Source:=rHLC, PlotBy:=xlColumns
.ChartType = xlStockHLC
With .SeriesCollection.NewSeries
.Values = rOpen
.ChartType = xlXYScatterLinesNoMarkers
.XValues = rXVals
.Name = "Open"
End With
End With
End Sub
'' END CODE ----------------------------------------------
----------

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon,

Thanks a lot for your time. All what I want to do is to
plot exactly the same HLC chart as you sampled on your
web
site, but with a macro.

The only difference is I use the "Open" price line as
my
Index line on HLC chart.

I can do this easily by following your suggested steps
manually, but I can't do it with a macro.

Can you help, please?
Thank you,




-----Original Message-----
Shi -

1. Do you need to select the plot area? Probably not.

2. It's dangerous to be setting the XValues to "". What

purpose does

this line serve? The effect is to remove reference to
the

X Values range

in the series formula, which means the series will just

use 1, 2, 3,

etc. Interestingly, if you do this to the Y values, it

does not clear

that piece of the formula. Instead, it converts from a

range containing

values, to an array of these values {1,2,3,4}. Still, I

imagine it's a

way to create problems.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:


Jon,

The true problem is with these two lines:
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""

If I run both macros without these above two lines,
everthing seems fine.

If I add these two lines inside my macro, this macro

just

doesn't know how to select "SeriesCollection(4)".

Would you please give me an advice?

Thanks for your help,




-----Original Message-----
Jon,

You are the Master of Excel Charting. Thank you very

much

for your help here.

I just tried your solution, I still couldn't get
my "OpenLine" or your "Index line" working with a
macro.

Can you offer me more help? Thanks.





-----Original Message-----
Shi -

Sometimes the macro recorder gets confused. This
will

work if you put


Chart Type after Set Source Data:

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.ChartType = xlStockHLC
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="Chart1"
[etc.]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:



Jon's technique helps me a lot to plot a stock's
HLC

chart


manually. But if I record it as a macro based on
his
recommended steps, it doesn't work anymore. I have
a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close"

data

located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine,
but "OpenLine
macro" doesn't. Can anyone find out what's the

problem

with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location
Whe=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory,

xlPrimary).CategoryType =


xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste

Rowcol:=xlColumns,


SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

.


.


.


.