Issue with Charts.Add when I only want one row

[CharlesLester]

BTW, I have Excel 2010
I've tried looking for (and trying various methods of) a solution, and have been unsuccessful so far. I have a large worksheet, with a header row (Row 1) with the date for each row (eg, Cell B1 1/1/16, Cell C1 1/2/16, ..., Cell NC1 12/31/16), event ids in the respective row in Column A (e.g. Cell A2 "Event 1", Cell A3 "Event 2", etc), and the data for each day for each event in the respective cell (eg, cell C3 has the number of occurrences of Event 2 on 1/2/16).
There are more than 256 events (rows).
I want to chart the yearly data for any particular event with a macro, by placing my cursor on the row desired and launching the macro, generating a new chart page with the title of the name of the event in column A (eg Event 2), the dates from columns B-NC as the X-axis labels, and the series label of the event name (eg, Event 2). This works fine if I ctrl-click in the row I want, but if I merely click in the line, I get the run-time error '1004': The maximum number of data series per chart is 255 on the Charts.Add statement.

Please Login or Register  to view this content.

It appears (from the flash of a screen if i reduce the number of rows) that the Charts.Add is, the the absence of a ctrl-click selection, assuming I want to chart *ALL* the rows.
I've tried placing variations of all of the following before the Charts.Add to undo that assumption, to no avail:

Please Login or Register  to view this content.

myRow and myCol are the row and column of my active cell. myName is the name of the worksheet with the data.
Any help is appreciated. Thanks in advance!

[dflak]

This sounds very doable and possibly without VBA, using named dynamic ranges and the OFFSET command. I really can't get a picture of what you are trying to do looking at the code.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[CharlesLester]

Hmm. The sanitized version works without the error :-(, probably because column 1 text has been replaced mostly by formulas. The chart is what was created when CreateChartFullPeriod was executed with the cursor on row 2.

[dflak]

My suspicions were confirmed. This can be done without VBA.

I added an extra sheet so as not to interfere with the data you already have.

The following two articles explain how to use the offset command to make a named dynamic range and then how to use named dynamic ranges in charts.

http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
http://www.utteraccess.com/wiki/inde...namic_Charting

I’ll elaborate the specifics here.

I created a named range with the event numbers for the sake of data validation in Cell B2 on Sheet 2. Event_number =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1).

OFFSET has 5 parameters if you are defining it in terms of a single cell:
-Start Cell
-Rows to go down
-Columns to go right
-Rows to return
-Columns to return

So, on sheet1, we start in Cell A2, go down zero rows, go right zero columns and return a range COUNTA(A:A)-1 rows deep and 1 column wide. The reason for the -1 is that we don’t want to count the header.

So this gives us the dropdown list for the data validation in cell B2.

Cell B3 contains the formula: =MATCH(B2,Sheet1!A:A,0) – this finds the row on Sheet 1 where the selected value is found.

I then created a named range for the dates: Plot_X =Sheet1!$B$1:$NC$1. This is actually a static range.

Then I created a named dynamic range for the Y values to plot: Plot_Y =OFFSET(Plot_X,Sheet2!$B$3-1,0).

If you use offset with a range, you only need three parameters, the command already knows how many rows and columns to return: the same number as the start range.
-Start Range
-Rows to go down
-Columns to go right

In this case we start with Plot_X (the dates in row 1) and go down the number of rows in cell B3 on sheet 2 minus a row and we go right zero columns.

The reason for the -1 is because MATCH starts counting at 1 and OFFSET starts with zero.

The I right clicked on the existing chart and selected Select Data and I edited the X and Y series putting Plot_Y and Plot_X in place of your fixed range.

The final tweak was to select the title, go up to the formula bar, type an equal sign and select the dropdown box on sheet 2. Titles can read the value of a cell.

[CharlesLester]

Sweet chart! Thanks for the example and the explanation.