hi,

i wrote a macro constructing a chart, where all data series and data labels
of the series were shown and formatted in a certain way. this part was
working perfectly; the next step is to search all series, which have a
certain data label name (in this case "Interim" and to reformat them. The
problem is that I don't know how to put the query into vba, so that it works
correctly.

My first approach was to make a loop stepping through each series and to
insert an if-statement searching for the certain name:

IF ActiveChart.SeriesCollection(xxx).DataLabels.Name = "Interim" THEN

etc.

Seems that VBA cannot work with an if-statement like that. Do you have any
proposals?

You need a for loop like this

Series_Num = 0
for i = 1 to LastSeries
If ActiveChart.SeriesCollection(i).DataLabels.Name = "Interim" THEN
Series_Num = i
exit for
end if
next i
if Series_Num < 0 then
'add your code here

exit

"Kathl" wrote:

hi,

i wrote a macro constructing a chart, where all data series and data labels
of the series were shown and formatted in a certain way. this part was
working perfectly; the next step is to search all series, which have a
certain data label name (in this case "Interim" and to reformat them. The
problem is that I don't know how to put the query into vba, so that it works
correctly.

My first approach was to make a loop stepping through each series and to
insert an if-statement searching for the certain name:

IF ActiveChart.SeriesCollection(xxx).DataLabels.Name = "Interim" THEN

etc.

Seems that VBA cannot work with an if-statement like that. Do you have any
proposals?

Don't you read what's written here? Did not include the loop in this text
because this is not the problem and if I write, that there is a loop you can
be sure it is really there.
The problem is with the if-statement, because the program loops through all
the if returns always false.

"Joel" wrote:

You need a for loop like this

Series_Num = 0
for i = 1 to LastSeries
If ActiveChart.SeriesCollection(i).DataLabels.Name = "Interim" THEN
Series_Num = i
exit for
end if
next i
if Series_Num < 0 then
'add your code here

exit

"Kathl" wrote:

hi,

i wrote a macro constructing a chart, where all data series and data labels
of the series were shown and formatted in a certain way. this part was
working perfectly; the next step is to search all series, which have a
certain data label name (in this case "Interim" and to reformat them. The
problem is that I don't know how to put the query into vba, so that it works
correctly.

My first approach was to make a loop stepping through each series and to
insert an if-statement searching for the certain name:

IF ActiveChart.SeriesCollection(xxx).DataLabels.Name = "Interim" THEN

etc.

Seems that VBA cannot work with an if-statement like that. Do you have any
proposals?

Unless I am misunderstanding you:

The problem is with the if-statement, because the program loops through
all
the if returns always false.

is not correct, the

exit for

stops the For/Next loop when the first TRUE answer to the If is encountered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Kathl" wrote in message
...
Don't you read what's written here? Did not include the loop in this text
because this is not the problem and if I write, that there is a loop you
can
be sure it is really there.
The problem is with the if-statement, because the program loops through
all
the if returns always false.

"Joel" wrote:

You need a for loop like this

Series_Num = 0
for i = 1 to LastSeries
If ActiveChart.SeriesCollection(i).DataLabels.Name = "Interim" THEN
Series_Num = i
exit for
end if
next i
if Series_Num < 0 then
'add your code here

exit

"Kathl" wrote:

hi,

i wrote a macro constructing a chart, where all data series and data
labels
of the series were shown and formatted in a certain way. this part was
working perfectly; the next step is to search all series, which have a
certain data label name (in this case "Interim" and to reformat them.
The
problem is that I don't know how to put the query into vba, so that it
works
correctly.

My first approach was to make a loop stepping through each series and
to
insert an if-statement searching for the certain name:

IF ActiveChart.SeriesCollection(xxx).DataLabels.Name = "Interim" THEN

etc.

Seems that VBA cannot work with an if-statement like that. Do you have
any
proposals?

Make sure what the IF is checking for is capitalized the same as what your
are testing for. a good solution is to force the text to upper case

If ucase(ActiveChart.SeriesCollection(i).DataLabels.N ame) = "INTERIM" THEN


"Kathl" wrote:

Don't you read what's written here? Did not include the loop in this text
because this is not the problem and if I write, that there is a loop you can
be sure it is really there.
The problem is with the if-statement, because the program loops through all
the if returns always false.

"Joel" wrote:

You need a for loop like this

Series_Num = 0
for i = 1 to LastSeries
If ActiveChart.SeriesCollection(i).DataLabels.Name = "Interim" THEN
Series_Num = i
exit for
end if
next i
if Series_Num < 0 then
'add your code here

exit

"Kathl" wrote:

hi,

i wrote a macro constructing a chart, where all data series and data labels
of the series were shown and formatted in a certain way. this part was
working perfectly; the next step is to search all series, which have a
certain data label name (in this case "Interim" and to reformat them. The
problem is that I don't know how to put the query into vba, so that it works
correctly.

My first approach was to make a loop stepping through each series and to
insert an if-statement searching for the certain name:

IF ActiveChart.SeriesCollection(xxx).DataLabels.Name = "Interim" THEN

etc.

Seems that VBA cannot work with an if-statement like that. Do you have any
proposals?

ok 'm posting all of my code so that you don't misunderstand any replies of
others for the code i have implemented:

For num = 2 To 36
If ActiveChart.SeriesCollection(num).DataLabels.Name = "Interim" Then
ActiveChart.SeriesCollection(num).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
Selection.InvertIfNegative = False
Selection.Interior.ColorIndex = xlNone
End If
Next num

So repeating the problem is that the if-statement never becomes true because
somehow the program doesn't find the Series in the chart that are labelled as
"Interim".

Cheers

"Sandy Mann" wrote:

Unless I am misunderstanding you:

The problem is with the if-statement, because the program loops through
all
the if returns always false.

is not correct, the

exit for

stops the For/Next loop when the first TRUE answer to the If is encountered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

Now it shows the fault

RunTime Error 1004
Unable to get the name property of the DataLabels class


"Joel" wrote:

Make sure what the IF is checking for is capitalized the same as what your
are testing for. a good solution is to force the text to upper case

If ucase(ActiveChart.SeriesCollection(i).DataLabels.N ame) = "INTERIM" THEN


"Kathl" wrote:

Don't you read what's written here? Did not include the loop in this text
because this is not the problem and if I write, that there is a loop you can
be sure it is really there.
The problem is with the if-statement, because the program loops through all
the if returns always false.

"Joel" wrote:

You need a for loop like this

Series_Num = 0
for i = 1 to LastSeries
If ActiveChart.SeriesCollection(i).DataLabels.Name = "Interim" THEN
Series_Num = i
exit for
end if
next i
if Series_Num < 0 then
'add your code here

exit

"Kathl" wrote:

hi,

i wrote a macro constructing a chart, where all data series and data labels
of the series were shown and formatted in a certain way. this part was
working perfectly; the next step is to search all series, which have a
certain data label name (in this case "Interim" and to reformat them. The
problem is that I don't know how to put the query into vba, so that it works
correctly.

My first approach was to make a loop stepping through each series and to
insert an if-statement searching for the certain name:

IF ActiveChart.SeriesCollection(xxx).DataLabels.Name = "Interim" THEN

etc.

Seems that VBA cannot work with an if-statement like that. Do you have any
proposals?

hey guys, thanx for trying to answer.

luckily i found the solution myself. as i always suspected there was sth
wrong with the class and member.
I searched the class library once more and found another member called Name
in the object Series. That was the right object for finding the data series
labels and implemented like that:

If ActiveChart.SeriesCollection(num).name = "Interim" Then

"Kathl" wrote:


Now it shows the fault

RunTime Error 1004
Unable to get the name property of the DataLabels class


"Joel" wrote:

Make sure what the IF is checking for is capitalized the same as what your
are testing for. a good solution is to force the text to upper case

If ucase(ActiveChart.SeriesCollection(i).DataLabels.N ame) = "INTERIM" THEN


"Kathl" wrote:

Don't you read what's written here? Did not include the loop in this text
because this is not the problem and if I write, that there is a loop you can
be sure it is really there.
The problem is with the if-statement, because the program loops through all
the if returns always false.

"Joel" wrote:

You need a for loop like this

Series_Num = 0
for i = 1 to LastSeries
If ActiveChart.SeriesCollection(i).DataLabels.Name = "Interim" THEN
Series_Num = i
exit for
end if
next i
if Series_Num < 0 then
'add your code here

exit

"Kathl" wrote:

hi,

i wrote a macro constructing a chart, where all data series and data labels
of the series were shown and formatted in a certain way. this part was
working perfectly; the next step is to search all series, which have a
certain data label name (in this case "Interim" and to reformat them. The
problem is that I don't know how to put the query into vba, so that it works
correctly.

My first approach was to make a loop stepping through each series and to
insert an if-statement searching for the certain name:

IF ActiveChart.SeriesCollection(xxx).DataLabels.Name = "Interim" THEN

etc.

Seems that VBA cannot work with an if-statement like that. Do you have any
proposals?