HELP WITH CHART SETSOURCE DATA!

[jay dean]

I am trying to update the source of data of many charts on sheet
"Charts" with the code below. It seems to work fine EXCEPT THAT all the
charts below the first one take their source of data from the first one
instead of data from themselves because each chart has its own data. I
guess "rng" below does not go through all the other charts as I expect.
Any help would be appreciated.

Dim ocht As ChartObject
Dim cht As Chart
Dim dir As Variant
Dim rng As Range

For Each ocht In ActiveSheet.ChartObjects
For Each dir In Range("A:A")

If dir.Interior.ColorIndex = 55 Then
Set rng = Range(Cells(dir.Row, 1), Cells(dir.Offset(3).Row, 31))

Set cht = ocht.Chart
ActiveSheet.ChartObjects(ocht.Name).Activate

ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("Charts").Range(rng.Address),
PlotBy:=xlRows

ActiveWindow.Visible = False


Exit For
End If
Next
Next

End Sub

Thanks.
Jay Dean

 

[Debra Dalgleish]

Your 'For each dir' loop starts at the top of column A for each chart
object. You could add a variable to store the last dir.row, so the next
loop would start one row down, and find the next coloured cell:

Sub UpdateCharts()
Dim ocht As ChartObject
Dim cht As Chart
Dim dir As Range
Dim rng As Range
Dim myRow As Long
myRow = 1
For Each ocht In ActiveSheet.ChartObjects
For Each dir In Range(Cells(myRow, 1), Cells(Rows.Count, 1))
If dir.Interior.ColorIndex = 55 Then
Set rng = Range(Cells(dir.Row, 1), _
Cells(dir.Offset(3).Row, 31))
ocht.Chart.SetSourceData _
Source:=Sheets("Charts") _
.Range(rng.Address), PlotBy:=xlRows
myRow = dir.Row + 1
Exit For
End If
Next
Next
End Sub

 

[jay dean]

Debra -
Your approach sounds pretty intuitive! I can now see what why my
"rng" variable was not looping.However, I can only test your code at
work on Monday. I have a question though:
Why does your Long variable "myRow" initialize from 1 and not
dir.Row+1?

Thanks.

Jay Dean

 

[Debra Dalgleish]

You can remove the line: myRow = 1

I think it's a remnant of an earlier approach to the problem, and isn't
required now.

 

[jay dean]

Debra-

For some reason, it keeps linking the wrong data. I have struggled to
code this in a different all day to see if even an alternate method will
work but I can't seem to get around the .....Range(rng.Address)...
Any additional help would be appreciated.Thanks.

jay