PDA

View Full Version : Solved: macro doesn't work anymore



Ger
04-02-2012, 05:32 AM
Hi,

I used a macro to put text in a graph. It worked fine with excel97. In excel 2010 the macro doesn't work anymore. I need some help to rewrite it.
The text in column "C" is the text which must be displayed in the graph.

Sub Macro3()
'
' Macro3 Macro
' De macro zorgt ervoor dat de labels uit kolom c in de grafiek worden gezet
'
'
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 & "C3"
Next i
End With
End With

ActiveWindow.Visible = False
End Sub


Ger

mancubus
04-02-2012, 06:00 AM
hi.
try this.


Sub Macro4()

Dim ws As Worksheet
Dim cht As Chart
Dim Lastrow As Long, i As Long

Set ws = Sheets("grafiek")

With ws
Lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
Set cht = .ChartObjects("Grafiek 1").Chart
For i = 2 To Lastrow
On Error Resume Next
cht.SeriesCollection(1).Points(i - 1).HasDataLabel = True
cht.SeriesCollection(1).Points(i - 1).DataLabel.Text = .Cells(i, 3).Value
On Error GoTo 0
Next i
End With

End Sub

Ger
04-02-2012, 06:38 AM
Thx,

this works after i modified the range of the table with data for the graph(a2:B17). Is it possible to let vba calculate and modify the range?

Ger

mancubus
04-02-2012, 07:34 AM
wellcome.
i did not change the chart data range.
it worked.

but if you want;
insert red line into macro after blue line

Set cht = .ChartObjects("Grafiek 1").Chart
cht.SetSourceData Source:=Range("A2:B17")

Ger
04-02-2012, 11:31 PM
The first code worked but only the values from range A2:B8 was displayed in the graph. After i changed the input range of the graph to a2:B17 all values where displayed in the graph.
So i need a code that changes the input range of the graph depending on the range of values in a2 to b.....
I think it must be something like
cht.SetSourceData Source:=Range("A2:B2&lastrow")

Ger

mancubus
04-03-2012, 02:06 AM
Range("A2:B" & LastRow)

Ger
04-03-2012, 02:20 AM
Thx a lot,

this works fine.

:thumb

Ger

mancubus
04-03-2012, 03:10 PM
you're wellcome.