I have a spreadsheet which produces a pretty chart based on a bunch of data thrown at it from Access.
The number of rows of data the chart needs to show varies, according to the selected output from Access. At the moment, with a fixed data range, the legend shows symbols for a full dozen rows, which is needed occasionally, but the number varies with every report.
I have a couple of macros that need to be run over the data to set it up to graph, one of which already uses a number of Activechart options to customise the look of the graph. I have found a little bit of info on ActiveChart.SetSourceData Source:=, but I'm having trouble getting the syntax right.
- Cell C6 counts the number of records involved in this particular report.
- The fixed data range is currently ='DIAMETER REPORT'!$A$42:$M$193. Data is arranged in columns, so it's the column range I need to adjust – the row range stays constant.
So I think I need something like:
ActiveChart.SetSourceData Source:=Sheets("DIAMETER REPORT").Range("A42:" & Offset("A42", 152, "c6", 0, 0))
The macro is throwing up a syntax error at the offset though.
I'm not familiar with VBA and am piecing this together through Google searches and deconstructing a couple of macros a co-worker built years ago, so I'd really appreciate any help you can offer to get this going.