Select A26, use D26:BX26 as Chart series data RRS feed

  • Question

  • Hi All......

    I want to be able to select a cell in column A, say A26, and need VBA code to then select the range D26:BX26 to use as the series data on my chart.   Then if I select A27, use range D27:BX27, etc etc.   I have everything I need except this line of code.

    This one does not work.......

    ActiveChart.SeriesCollection(1).Values = selection.Offset(0,3):selection.Offset(0,78)

    Any help  would be much appreciated.

    Vaya con Dios,

    Chuck, CABGx3+AAA

    Chuck, CABGx3

    Thursday, May 10, 2012 3:11 PM

All replies

  • Try

    ActiveChart.SeriesCollection(1).Values = ActiveCell.Offset(0,3).Resize(1, 73)

    Regards, Hans Vogelaar

    Thursday, May 10, 2012 3:32 PM
  • Hi Hans.....

    Thanks for your response but I got an error message my Excel 2000 which reads "Object variable or With block variable not set"

    I put your code in place of this line of mine which worked, but requires a RangeName (thisName) be created for each range, which is what I was trying to work around.......

    ActiveChart.SeriesCollection(1).Values = "=BloodTests!" & thisName

    Incidently, your code

    "ActiveChart.SeriesCollection(1).Values = ActiveCell.Offset(0,3).Resize(1, 73)"

    did not appear in this forum, but did appear in my email notification that you had responded.


    Chuck, CABGx3+AAA

    Chuck, CABGx3

    Thursday, May 10, 2012 4:11 PM
  • Ah, wait - a cell and a chart can't be selected simultaneously.


    ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values = ActiveCell.Offset(0,3).Resize(1, 73)


    ActiveChart.SeriesCollection(1).Values = ActiveWindow.RangeSelection.Offset(0,3).Resize(1, 73)

    Regards, Hans Vogelaar

    Thursday, May 10, 2012 4:17 PM
  • Hi Hans......

    Got the same error on the first one, and got the following error on the second one....

    "Applicaton-defined or object-defined error"

    Could I somehow create a RangeName on the fly based on the selected cell  before trying to do the chart, and then using that RangeName for the chart range.....or something like that?


    Chuck, CABGx3+AAA

    Chuck, CABGx3

    Thursday, May 10, 2012 4:35 PM
  • Is your chart embedded in a worksheet or is it a separate chart sheet?

    Regards, Hans Vogelaar

    Thursday, May 10, 2012 7:34 PM
  • Hi Hans........

    I was just about to post back that I've got a working solution.  I was able to create my desired range before I do the chart, so it works ok now.

    To answer your question, the chart just pops up on the sheet right now, not a separate sheet......but this is a work in progress, so that may change later.  Right now I'm just using column "A" to list my Blood Test Names and the rest of the columns to contain the accumulated readings with a date at the top of each column........but I may put a user-friendly front end on this thing and not present the whole database to the user, just the opportunity to select which chart they want to see and then the chart itself......also will eventually want a way to be able to add a second selected series to the chart for comparisons of two tests, or maybe two or three charts to pop up for one printout.

    Anyway, thanks for the help, you gave me some good info.


    Chuck, CABGx3+AAA

    Chuck, CABGx3

    Thursday, May 10, 2012 7:44 PM