ChartObjects.Count is incorrect

[Guest]

Hello all,

I've run into a stupid issue, and I'm not sure how to get around it.

I'm trying to automatically change the data source of a chart. Basically
every month I need to move the data source ahead by a row (advance the chart
window by a month).

I'm trying to loop through the chart objects of the worksheet and I guess
there were numerous charts added then deleted. So my ChartObjects.Count
returns 13, but there are only 6 charts.

How can I either:
a) Reset the counter of ChartObjects?
b) Edit the chart names to be (for example) Chart 1 - Chart 6? Right now,
it's not consistent what charts were left. There's no continuous run of
values I can loop through.
c) Only look at valid charts? The chart has a name, so I can't screen out
using that variable. Maybe another is (consistently) missing from
non-viewable charts? I don't know.

Any ideas are appreciated.

Thanks,
Jay

 

[Guest]

Avoid using the name:

Sub Tester1()
For Each ocht In ActiveSheet.ChartObjects
MsgBox ocht.Name & " - " & ocht.Chart.Name
Next

End Sub

but you can change the name manually. Make the drawing bar visible and
select the northwest arrow. Now click on the chart object and change the
name in the name bar. Reselect it and make sure it took. Turn on the macro
recorder while you do it and get the code to do it as well.

 

[Toyin.Butler]

Jay,

If you are changing the data source of a chart, it would be easier to
use a named range - the chart always looks at the named range, but the
named range changes.

Is the data a time series and are the columns you are switching
between in the same workbook? Or have I misinterpreted your underlying
issue?

Toy.

 

[Peter T]

Hi Jay,

Maybe someone assumed that deleting rows or columns containing charts also
deletes the charts, rather than making them zero width or height and
effectively invisible.

Sub FindNonDeletedCharts()
Dim chtobj As ChartObject
For Each chtobj In ActiveSheet.ChartObjects
If chtobj.Width < 20 Then
chtobj.Width = 200
chtobj.Chart.ChartArea.Interior.ColorIndex = 3
End If
If chtobj.Height < 20 Then
chtobj.Height = 100
chtobj.Chart.ChartArea.Interior.ColorIndex = 3
End If
Next

Regards,
Peter T

 

[Guest]

That solved my 'ghost' chart issues, thanks!
--
Disregard, this is so I can find my post later.
***postedbyJay***

Hi Jay,

Maybe someone assumed that deleting rows or columns containing charts also
deletes the charts, rather than making them zero width or height and
effectively invisible.

Sub FindNonDeletedCharts()
Dim chtobj As ChartObject
For Each chtobj In ActiveSheet.ChartObjects
If chtobj.Width < 20 Then
chtobj.Width = 200
chtobj.Chart.ChartArea.Interior.ColorIndex = 3
End If
If chtobj.Height < 20 Then
chtobj.Height = 100
chtobj.Chart.ChartArea.Interior.ColorIndex = 3
End If
Next

Regards,
Peter T

 

[Guest]

That solved my chart naming issues, thanks!

 

[Guest]

Toy,

I hadn't thought of using a named range.

I've run into all sorts of issues trying to change the XValues and Values of
a SeriesCollection. I can set the value, but I can't retrieve the value.

I'm going to try the named range route. I can't find a way to retrieve the
XValues/Values for me to parse and increment.

Thanks!

 

[Toyin.Butler]

One thing to note when using named ranges for chart series, ensure you
name your range for the worksheet only and not the workbook.

i.e. Sheet1!NamedRange

Then when refering to the name range you will need to put Sheet1!
NamedRange in the chart series.

Regards,

Toyin.

ps - I hope you can find your post later!

 

[Guest]

I found a small problem with this named range solution.

I have 30+ charts I need to create named ranges for. With 3+ series in each
chart.

I think I might be farther ahead if I can find a way to modify the XValues
and Values instead. That way it won't be dependant on the named ranges, and
then I won't have to create them all.

Any ideas?

 

[Guest]

http://j-walk.com/ss/excel/tips/tip83.htm

that should help with the xValues, etc

 

[Guest]

Thanks for the link! It gave me a couple ideas, but I went in another
direction. I wanted something more simple than what Module could accomplish.

Here's my solution. It could be cleaned up a bit, but it seems to work. At
the moment, there's no validation of movement. You could go outside the
bounds of data, but that's an exercise for another day.

I'm incrementing every chart by 1 row, because that's the structure of my
data/charts. You could change the movement of the chart window as required.

I'm open to any feedback. Thanks guys, you all helped a lot.

Public Sub AdvanceChartData()
Dim cht As Chart
Dim sr As Series
Dim i As Long, j As Long
Dim arrFormula As Variant
Dim curXValues As String, newXValues As String
Dim curValues As String, newValues As String

For i = 1 To ActiveSheet.ChartObjects.Count
Set cht = ActiveSheet.ChartObjects(i).Chart
For Each sr In cht.SeriesCollection
arrFormula = Split(sr.Formula, ",")
curXValues = arrFormula(1)
curValues = arrFormula(2)
newXValues = MoveDataRange(curXValues, 1)
newValues = MoveDataRange(curValues, 1)

sr.Formula = arrFormula(0) & "," & newXValues & "," & newValues &
"," & arrFormula(3)

Next
Next
End Sub

Public Function MoveDataRange(strRange As String, lngMoveBy As Long)
Dim loc As Long
Dim locStartDS As Long
Dim locColon As Long
Dim locEndDS As Long
Dim lngFromRow As Long
Dim lngToRow As Long

locStartDS = InStr(1, strRange, "$")
locStartDS = InStr(locStartDS + 1, strRange, "$")
locColon = InStr(locStartDS + 1, strRange, ":")
lngFromRow = CLng(Mid(strRange, locStartDS + 1, locColon - (locStartDS +
1)))

locEndDS = InStrRev(strRange, "$")
lngToRow = CLng(Mid(strRange, locEndDS))

MoveDataRange = Mid(strRange, 1, locStartDS) & (lngFromRow + lngMoveBy) &
Mid(strRange, locColon, (locEndDS + 1) - locColon) & (lngToRow + lngMoveBy)

End Function