VBA Charting issue

ny156uk

New Member
Joined
Oct 6, 2006
Messages
2
Hi,

I have about 10 graphs, each of which are powered by data held on a specified 'graphdata' page. The data is regularly changing length (i have a max of 12 rows, but sometimes there may only be 6). The graph currently is set default to plot all 12 rows - resulting (sometimes) in there being several blank rows of data on the graph.

So far I have tried something like this:

--Code starts--
Dim GraphSelect, ChartName, GraphName As String
Dim GraphArea As Range

Sheets("ExcelData").Select

GraphArea = Range("A19:F31")
ChartName = "VolRec"

Sheets(GraphSelect).Select
ActiveSheet.ChartObjects(ChartName).Select
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("Exceldata").Range(GraphArea), PlotBy:=xlColumns
--code ends--

The range I specified (a19:f31) is something I want the code to manually work out. It will always start A19 and end F something, but it needs to know how 'deep' to plot the graph.

The error I receive is "object variable or With block variable not set".

In the end I want to be able to 'loop' through the process and have this move onto the next chart and the next data-series.

Any help would be greatly appreciated - i'm the only person in the office today with any of this sort of knowledge and it's rather frustrating.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Zwahlav

New Member
Joined
Jun 16, 2006
Messages
17
Hello,
for this purpose I would use something like this:
GraphArea = Range("A19:F" & Range("A19").End(xlDown).Row)

I´ve written this just by hearth, but I think it should work.

Also:
You can try to start the macro recorder, then select the cell "A19" then press Shift+Ctrl+<down arrow> simultaneously, then stop the macro recorder and have a look on the recorded piece of code.
 

Zwahlav

New Member
Joined
Jun 16, 2006
Messages
17
Also:
You can try to start the macro recorder, then select the cell "A19" then press Shift+Ctrl+(down arrow) simultaneously, then stop the macro recorder and have a look on the recorded piece of code.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
You may want to go the non-VBA route. See
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

Hi,

I have about 10 graphs, each of which are powered by data held on a specified 'graphdata' page. The data is regularly changing length (i have a max of 12 rows, but sometimes there may only be 6). The graph currently is set default to plot all 12 rows - resulting (sometimes) in there being several blank rows of data on the graph.
{snip}
 

Forum statistics

Threads
1,136,991
Messages
5,678,996
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top