help of setting the distance between 2 markers - urgent

[Ye]

Hi,

In the chart of excel, there is one type called line with
markers displayed at each data value. Because my data
values are lots, in my chart, markers are close to each
other even overlapped. I don't want this. So I want to
know if Excel has the function that can set the position
of markers. For example, I want to put markers at 100th
data value, 200th data value, 300th data value and so on.


Ye

 

[LeninVMS]

This code sets a marker at every 100th point of your first series:

Sub SetMarkerDistance()

Dim mychart As Chart

Set mychart = Application.Worksheets("Sheet1").ChartObjects(1).Chart

mychart.SeriesCollection(1).MarkerStyle = xlMarkerStyleNone
n = mychart.SeriesCollection(1).Points.Count

For i = 1 To n Step 100

With mychart.SeriesCollection(1).Points(i)
.MarkerStyle = xlMarkerStyleAutomatic
.MarkerSize = 5
End With
Next i

End Sub


- Leni

 

[ye]

Is this VB?

 

[LeninVMS]

Yes.

Alt-f11 would take you to the editor, in which u can insert a module
paste this code and run this Macro. Edit code to suit your specifi
sheetnames etc.

- Leni

 

[Tushar Mehta]

See the Excel/Tutorials/Select Markers page of my web site for non-VBA
solutions.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[ye]

Hi,

thanks. I looked at your web and decide to use "use an
extra column". But there is still one problem because I
want to combine two serials("-----" if it express line
without marker and " + " if it express marker only) into
one serial ("---+----"). What can I do?


Ye

-----Original Message-----
See the Excel/Tutorials/Select Markers page of my web site for non-VBA
solutions.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

I don't believe that is possible. However, it is not clear why
combining the two series is important. After all, you do get the
desired visual effect, don't you?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hi,

thanks. I looked at your web and decide to use "use an
extra column". But there is still one problem because I
want to combine two serials("-----" if it express line
without marker and " + " if it express marker only) into
one serial ("---+----"). What can I do?


Ye

 

[ye]

I want to publish paper with the chart where there are 8
lines. Now I know how to distinquish these 8 lines with
the different markers by using 8 extra columns. This
method is to use 2 columns to express one line(column A
is for the line "------" and column B is for marker "+"
only). So 8 lines need 16 columns. Each column
corresponds to one serial automatically.


Because I need to tell the readers what each line
expresses, the implementation of this has to be done
through serials. But for each line, I only need one serial
("---+---"). Now there are two serials corresponding to
one line. I tried to reduce one serial but the
corresponding information in the chart vanish too.

So I want to know if Excel has the function of reducing
one serial without losing information in the chart.

-----Original Message-----
I don't believe that is possible. However, it is not clear why
combining the two series is important. After all, you do get the
desired visual effect, don't you?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

I'm sure you know what you are doing, but I don't understand your
problem. Yes, you need 16 columns for 8 series. So, what? The
readers don't have to know how you created the effect. Just as long as
someone who wants can duplicate your work.

If your problem is that you have a legend on the chart and it shows 16
entries, just delete the legend entry corresponding to the dummy series
that creates the marker. Click the legend. Pause and click the
individual legend you want to delete. Press the 'Delete' key.

If the problem is that the color of the marker is different from the
line, double-click the marker series and from the Patterns tab set the
color scheme to match the line.

If you still insist that you want one column for one series, create the
series and write a macro that will show the marker only for each n-th
point. Lenin has already shared a solution using this technique.

If you don't want to use VBA and don't want to use 2 columns, you could
always set the marker for the series to 'None.' Then, with the series
still selected, double-click each point for which you want a marker.
In the resulting 'Format Data Point' dialog box, from the Patterns tab,
set the marker as desired.

If none of the above appeal to you, XL is the wrong program for you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Tushar -

He's talking about the legend, if I understand it. In the legend you get
one entry with the line style, and another with the marker style, in the
technique you've shared with him. He wants to combine them, but I also
don't think it can be done as stated. But I have a technique, awkward as
it is.

Add eight more series, just with small dummy data ranges, with the
appropriate combined line and marker styles. After formatting the
series, clear the cells with this data, so the series no longer appear
in the chart. Remove all the marker only and line only legend entries in
the legend (two single clicks to select the legend entry, meaning the
text, not the little symbol, then press Delete).

Sure, it's a long way round to get what you need. That's what makes
Excel so much FUN!!

- Jon

 

[ye]

It is my fault. I didn't explain it clearly.

I mean that I want 16 columns(data) and 8 series(in the
legend). But Now I have 16 columns and 16 series. Also I
want to show the readers that "---+----" expresses level
0, "----^----" expresses level 1, .... , "----*----"
expresses level 7. (All these levels will help readers'
understanding my article.) But now I have "------"
and "+" to express level 0, "-------" and "^" to express
level 1, ... , "-------" and "*" to express level 7.

If I delete the series I made for the markers, I will
also lose the markers in the chart. Actually, It seems
that I want to make my own series that are not related to
the chart.

Is it possible?

-----Original Message-----
I'm sure you know what you are doing, but I don't understand your
problem. Yes, you need 16 columns for 8 series. So, what? The
readers don't have to know how you created the effect. Just as long as
someone who wants can duplicate your work.

If your problem is that you have a legend on the chart and it shows 16
entries, just delete the legend entry corresponding to the dummy series
that creates the marker. Click the legend. Pause and click the
individual legend you want to delete. Press the 'Delete' key.

If the problem is that the color of the marker is different from the
line, double-click the marker series and from the Patterns tab set the
color scheme to match the line.

If you still insist that you want one column for one series, create the
series and write a macro that will show the marker only for each n-th
point. Lenin has already shared a solution using this technique.

If you don't want to use VBA and don't want to use 2 columns, you could
always set the marker for the series to 'None.' Then, with the series
still selected, double-click each point for which you want a marker.
In the resulting 'Format Data Point' dialog box, from the Patterns tab,
set the marker as desired.

If none of the above appeal to you, XL is the wrong program for you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I want to publish paper with the chart where there are 8
lines. Now I know how to distinquish these 8 lines with
the different markers by using 8 extra columns. This
method is to use 2 columns to express one line(column A
is for the line "------" and column B is for marker "+"
only). So 8 lines need 16 columns. Each column
corresponds to one serial automatically.


Because I need to tell the readers what each line
expresses, the implementation of this has to be done
through serials. But for each line, I only need one serial
("---+---"). Now there are two serials corresponding to
one line. I tried to reduce one serial but the
corresponding information in the chart vanish too.

So I want to know if Excel has the function of reducing
one serial without losing information in the chart.

-----Original Message-----
I don't believe that is possible. However, it is not clear why
combining the two series is important. After all,

you
do get the

desired visual effect, don't you?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hi,

thanks. I looked at your web and decide to use "use an
extra column". But there is still one problem

because
I

want to combine two serials("-----" if it express line
without marker and " + " if it express marker only) into
one serial ("---+----"). What can I do?


Ye


-----Original Message-----
See the Excel/Tutorials/Select Markers page of my web
site for non-VBA
solutions.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Ye]

Thank you. I think you understand what I mean. I will try
the method you told me and tell you if it can work.

 

[Jon Peltier]

The legend can show what's in the chart, although you can delete
unwanted legend entries. You can't add a legend entry for something not
in the chart, and this is why I suggested a dummy series for each marker
and line combination you want to show in the legend. The dummy series
need not have any visible points, and the legend entries for the "real"
series can be deleted.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

It is my fault. I didn't explain it clearly.

I mean that I want 16 columns(data) and 8 series(in the
legend). But Now I have 16 columns and 16 series. Also I
want to show the readers that "---+----" expresses level
0, "----^----" expresses level 1, .... , "----*----"
expresses level 7. (All these levels will help readers'
understanding my article.) But now I have "------"
and "+" to express level 0, "-------" and "^" to express
level 1, ... , "-------" and "*" to express level 7.

If I delete the series I made for the markers, I will
also lose the markers in the chart. Actually, It seems
that I want to make my own series that are not related to
the chart.

Is it possible?

-----Original Message-----
I'm sure you know what you are doing, but I don't

understand your

problem. Yes, you need 16 columns for 8 series. So,

what? The

readers don't have to know how you created the effect.

Just as long as

someone who wants can duplicate your work.

If your problem is that you have a legend on the chart

and it shows 16

entries, just delete the legend entry corresponding to

the dummy series

that creates the marker. Click the legend. Pause and

click the

individual legend you want to delete. Press

the 'Delete' key.

If the problem is that the color of the marker is

different from the

line, double-click the marker series and from the

Patterns tab set the

color scheme to match the line.

If you still insist that you want one column for one

series, create the

series and write a macro that will show the marker only

for each n-th

point. Lenin has already shared a solution using this
technique.

If you don't want to use VBA and don't want to use 2

columns, you could

always set the marker for the series to 'None.' Then,

with the series

still selected, double-click each point for which you

want a marker.

In the resulting 'Format Data Point' dialog box, from

the Patterns tab,

set the marker as desired.

If none of the above appeal to you, XL is the wrong

program for you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions


8

with

A

marker "+"

 

[Hackett]

This code sets a marker at every 100th point of your first series:

Sub SetMarkerDistance()

Dim mychart As Chart

Set mychart = Application.Worksheets("Sheet1").ChartObjects(1).Chart

mychart.SeriesCollection(1).MarkerStyle = xlMarkerStyleNone
n = mychart.SeriesCollection(1).Points.Count

For i = 1 To n Step 100

With mychart.SeriesCollection(1).Points(i)
.MarkerStyle = xlMarkerStyleAutomatic
.MarkerSize = 5
End With
Next i

End Sub


- Leni

--
Message posted from http://www.ExcelForum.com

Can you adjust the Macro to change all the Series in a selected chart and have it give the option for you to enter the number of points you want it to skip (say change it from every 100 points to every 50 points).

Thanks
Jim