searching for data labels with specific text

[Kathl]

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?

 

[Joel]

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]

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.

 

[Sandy Mann]

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

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Joel]

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.Name) = "INTERIM" THEN

 

[Kathl]

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

 

[Kathl]

Now it shows the fault

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

 

[Kathl]

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