Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP WITH CHART SETSOURCE DATA!
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP WITH CHART SETSOURCE DATA!
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 wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP WITH CHART SETSOURCE DATA!
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP WITH CHART SETSOURCE DATA!
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 wrote: 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? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP WITH CHART SETSOURCE DATA!
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart empty with new data in the dynamic range chart. | Excel Discussion (Misc queries) | |||
chart from pivot data does not update x-axis bar chart values - bug | Excel Discussion (Misc queries) | |||
How do you link chart source data when you copy the chart? | Charts and Charting in Excel | |||
Resizing chart because of refreshing Chart Data through a query | Charts and Charting in Excel | |||
Chart data file lost - need to re-create from chart? | Charts and Charting in Excel |