an afternoon with a dot and a commer...

[olivier]

Hello everybody,

First of all, I am running under excel 2002 and windows 2000 pro. In
Excel I use the system number format : the decimal seperator is a
commer (",")

In order to draw an horizontal line between the points (1,1) and
(100,1) on a chart I am setting up the values of a two-values series by
building a character string as follows :

ActiveChart.SeriesCollection(1).XValues = "={1,100}"
Activechart.SeriesCollection(1).Values = "={1,1}"

Which gives me a serie whose formula is :

=SERIE(;{1.100};{1.1};1)

Now for the second step : I want this line to be at y=1.5 (ie a decimal
number)

the code in VBA will be :

ActiveChart.SeriesCollection(1).XValues = "={1,100}"
ActiveChart.SeriesCollection(1).Values = "={1.5,1.5}"

Which gives me a serie whose formula is :

=SERIE(;{1.100};{1,5.1,5};1)

(Note that there is an inversion between the commers and the dots)

The real issue is when I want to define the position of this line by
the value of a cell :

Y = Sheets("Sheet1").Cells("A1")
ActiveChart.SeriesCollection(2).XValues = "={1,100}"
ActiveChart.SeriesCollection(2).Values = "={" & Y & " , " & Y &
"}"

Which, assuming A1 contained the value 1.5 (displayed as "1,5" of
course!) gives a serie whose formula is :

=SERIE(;{1.100};{1.5.1.5};1)

The only solution I found is the folowing code :

Y = Sheets("Sheet1").Cells("A1")
Y_with_dot = Replace(Y, ",", ".")
Ystring = "={" & Y_with_dot & " ; " & Y_with_dot & "}"
ActiveChart.SeriesCollection(1).XValues = "={1,100}"
ActiveChart.SeriesCollection(1).Values = Ystring

Which, assuming A1 contained the value 1.5 gives a serie whose formula
is :

=SERIE(;{1.100};{1,5.1,5};1)

And thanks to a f.....g commer I've lost my afternoon...

Any comments? Does anyone know of a more simple way to do what I want
to do?

Thanks for numerous answers!

Olivier