Home » MS OfficeRSS

Excel Chart - Dynamic named range skips data

Hello,

 

I have a dashboard in Excel which includes some charts. For one the number of rows of data changes regularly so I wanted to use a dynamic named range to refresh the data when a user opens the particular sheet.

 

This has worked, and the chart picks up the category names and all of the data except for the first column. It picks up the series name and shows this in the legend but does not show any data. Does anyone know why this is?

 

The VBA script is;

 

Sub Activate_Custody()

Sheets("Custody").Unprotect
Sheets("Custody").Activate

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Values = CustodyData

Sheets("Custody").Protect

End Sub

 

The named range CustodyData is set using the following formula;

 

=OFFSET(qtabHistoricProceedingsCustodyB!$J$1,0,0,COUNTA(qtabHistoricProceedingsCustodyB!$J:$J),6)

This highlights the correct area of the worksheet, picks up the category names in the first column, and the correct number of rows in the data table.

 

I don't get any error messages, and all the data updates except the first series.

 

7 Answers Found

 

Answer 1

Unless you have defined a variable to reference the named range then your current variable, CustodyData, is an empty variant.


Try this,

ActiveChart.SeriesCollection(1).Values = "='" & thisworkbook.name & "'!CustodyData"

 

Answer 2

Hi Andy,

 

Thanks for the reply, unfortunately your suggestion generates an error.

 

Runtime error 1004

Unable to set the values property of the series class.

 

Using just the Name the chart is picking up the category labels, series names and data from 4 out of 5 series, so it appears to be able to read the variable.

 

Its just missing the first column of data, which seems bizarre as this is between the column with the category names in and the 4 columns of data it does pick up. As its picking up the series name, for the blank series, correctly I have to assume that it is picking up the correct range but for some reason is failing to read the data in that range?

 

Answer 3

You do not show in your code where or how you assign the variable CustodyData to the named range.

Do the cells covered by the named range contain error values or are empty?

 

 

Answer 4

Maybe you have to explicitly refer to the named range (and I interpreted the OFFSET function as the named range CustodyData) as in what Andy wrote, except maybe

ActiveChart.SeriesCollection(1).Values = "='" & thisworkbook.names("CustodyData") - and do we need to convert thisworkbook.names("CustodyData") to a string?

Otherwise, I'm wondering if the chart is picking up the first column as the x axis values...

hope this helps,

Eric

 

 

Answer 5

I tried a number of different ways to assign the variable (e.g. using ActiveWorkbook.Names("CustodyData") etc) but everything I tried generated error messages. When I left it just as the Name, it worked except for skipping the first column of data.

 

None of the cells contain error values, and categories with no data are displayed as zeros.

 

To be honest I'm starting to think that the problem is actually with the chart or the data itself. As I tried refreshing the data by manually setting the series in the Source Data window and it still dropped the first column of data?

 

Thanks for the help with this, but I think I'm going to start from scratch with this one. I'll let you know if I ever find out what the problem was.

 

Answer 6

If you end up back at the same problem it my help if you can post an example workbook on a free download site, such as skydrive.

 

 

Answer 7

The problem seemed to be with the SeriesCollection code, it appears putting SeriesCollection(1) excluded the first series for some reason. I confirmed this by trying SeriesCollection(2) and SeriesCollection(3), these skipped the first 2 series and first 3 series respectively. Not sure how/why that happened, I must have missed something.

 

I changed the code completely after that, and set the range within VBA itself now. I've used this instead, and its working every time :)

 

Sub Activate_Custody()

Dim Data As Range
Dim RealUsedRange As Variant
Dim FirstRow        As Long
Dim LastRow         As Long
Dim FirstColumn     As Integer
Dim LastColumn      As Integer
    
Sheets("qtabHistoricProceedingsCustodyB").Activate

FirstRow = "1"
    
FirstColumn = "1"
    
LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
LastColumn = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn))

Set Data = RealUsedRange

Sheets("Custody").Unprotect
Sheets("Custody").Activate

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Data

Sheets("Custody").Protect

End Sub

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter