Solved: how can i get labelname in graphic

[Ger]

I'm using Excell 2003.
I have a range of data (sheet data). With these data i create a pivottable.
These table i use to make a graphic.
As labelname of the graphicpoints i like to use the name in label (column C)
Now i manualy click on each value and then i point too the range in column c
I think this may be easier using vba.
There can be 1 problem because the number of rows can change depending on the values in the sheet data.
I also looking for a solution for the pivottable. I now have a "hard" row reference to the cells a2:c30 but this can change to. How can i make the row range flexible (depending on the rows in the sheet data)

please see the attached file.
Thx for the help.

Ger

[Ger]

I succeeded to get the lablename in the graphic using vba. The problem i have is that the number of rows in the sheet "data" can change (now i "programmed" till row 30). how can i make this flexible. The same problem is the range I use for the graphic (now 8 points).
And how can i provide the message when i delete the "old" sheet draaitabel (pls see attachement)
Please help,

Ger

[Bob Phillips]

[vba]

Sub Macro3()
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row

With .ChartObjects(1).Chart

For i = 2 To Lastrow

.SeriesCollection(i - 1).Points(1).DataLabel.Text = "=grafiek!R" & i - 1 & "C3"
Next i
End With
End With

ActiveWindow.Visible = False
Windows("vlootschouw.xls").Activate
Range("E12").Select
End Sub
[/vba]

[Ger]

I had to change a few things but this is working fine:

[VBA]Sub Macro4()
Sheets("grafiek").Select
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row

With .ChartObjects("grafiek 1").Chart

For i = 2 To Lastrow
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.SeriesCollection(1).Points(i - 1).DataLabel.Select
Selection.Text = "=grafiek!R" & i - 1 & "C3"
Next i
End With
End With

ActiveWindow.Visible = False
Windows("vlootschouw.xls").Activate
Range("E12").Select
End Sub[/VBA]

Thanks for the help.
Can i use the same for a fexible range in the pivottable?

Ger

[Bob Phillips]

Why did you need to do that selecting, it worked perfectly well without in my tests?

What do you mean by do the same for pivots, they are dynamic by nature.

[Ger]

i had an error in the line:
.SeriesCollection(i - 1).Points(1).DataLabel.Text = "=grafiek!R" & i - 1 & "C3"
so i changed it to
ActiveSheet.ChartObjects("Grafiek 1").Activate
ActiveChart.SeriesCollection(1).Points(i - 1).DataLabel.Select
Selection.Text = "=grafiek!R" & i - 1 & "C3"

the pivottable gets it data from the sheet data. I make the pivottable with the range a2:c30. But this range (row 30) can change.


Ger

[Bob Phillips]

For the pivot, createa a defined name of say MyData with RefersTo of Offset(data!$A$2,0,0,COUNTA(data!$A:$A),3) and set the PT data to MyData.

[Ger]

xld, thanks a lot for your help again. It works fine now.
I have 1 question: is it necessary to use the "i" in "Next i" (see macro4)

Ger

[Bob Phillips]

Not necessary, but it is a good programming practice (multiple nested loops are better identified, not all languages are so loose).

[Ger]

Thanks.

Ger