Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Create a chart with a macro

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to create a bar chart by recording a macro. My source data is sent from an Access table to a sheet. The number of rows can vary widely. In recording, after I choose the type of chart I want, It asks me for the source data. I am using "CTRL-A" to choose all data. When I finish my chart, and run the macro, all goes according to plan. HOWEVER, when I use NEW data sent over from access, and it has MORE rows than the previous chart had, the macro will only choose the same RANGE OF DATA that I had previously used. When I go in to edit the macro, it show (for example):
    ActiveChart.SetSourceData Source:=Sheets("NewChart").Range("A1:C21"), PlotBy _
    :=xlColumns
    My new data has 36 rows, but the macro has HARDCODED the previous old data rows. What am I doing wrong??? Thanks!!!!

    [ This Message was edited by: jamesgw on 2002-05-03 12:02 ]

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When you record a macro, it is always hardcoded. To get around this you must dynamically define your range. Here is how I would do it.

    dim iRow as Integer

    iRow = Sheets("NewChart").Range("A1").CurrentRegion.Rows.Count

    ActiveChart.SetSourceData Source:=Sheets("NewChart").Range(cells(1,1),cells(iRow,3), PlotBy _ :=xlColumns

    This assumes that your data will always be in a sheet called "NewChart" and that you have three columns with data starting in cell A1. To make it dynamic for new columns change to:

    dim iRow as Integer
    dim iCol as Integer

    iRow = Sheets("NewChart").Range("A1").CurrentRegion.Rows.Count

    iCol = Sheets("NewChart").Range("A1").CurrentRegion.Columns.Count

    ActiveChart.SetSourceData Source:=Sheets("NewChart").Range(cells(1,1),cells(iRow,iCol), PlotBy :=xlColumns

    Good luck,

    K

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks K, that seems to be a neat solution.


    BUT,

    When I tried it, I got a totally new error. This time it says:

    Run-time error '1004';
    Method 'cells' of object '_global' failed.

    It stops on the "ActiveChart.SETSOURCE DATA Source" line.

    Here is the first part of my macro.

    Sub graph()
    Dim iRow As Integer

    Sheets("graph").Select
    ActiveWindow.SelectedSheets.Delete 'delete old graph
    Range("B1").Select
    Selection.ClearContents 'remove title from 2nd column
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Length of Study" 'change 3rd column title
    Cells.Select
    Range("D2").Activate
    iRow = Worksheets("NewChart").Range("A1").CurrentRegion.Rows.Count
    Charts.Add 'add chart
    ActiveChart.ChartType = xlBarStacked 'create stacked bar graph
    ActiveChart.SetSourceData Source:=Sheets("NewChart").Range(Cells(1, 1), Cells(iRow, 3)), PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="GRAPH" ' change sheet name
    With ActiveChart
    .HasTitle = True

    Could anyone tell me WHAT to look for here?
    Thanks!!




    [ This Message was edited by: jamesgw on 2002-05-06 09:53 ]

  4. #4
    New Member
    Join Date
    May 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  5. #5
    New Member
    Join Date
    Jun 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am having the same problem trying to implement this code. I have another solution to selecting a dynamic area for a chart macro:
    Range("A1:F1").Select
    Range(Selection, Selection.End(xlDown)).Select

    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ...

    So I get the first 6 columns, as many rows are are present. Works great, except that occasionally it farts and plots data by row instead of by column. So I want to implement the PlotBy:=column, which is (cleverly!) embedded in the ActiveChart.SetSourceData portion of the code, and cannot be seperated. So I am trying to jump through these hoops to specify PlotBy:=Columns. I tried the iRow solution posted by kkknie and am getting the same Method 'cells' of object '_Global' failed error. No idea what it's talking about. Anyone else ever get a solution to this problem?

  6. #6
    New Member
    Join Date
    Aug 2005
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was having the same problem until I saw your post and had the idea to try this

    Range(Cells(StartRow, StartColPtr), Cells(EndRow, LastColPtr)).Select
    Charts.Add
    ActiveChart.ChartType = xlLine
    ActiveChart.PlotBy = xlRows

    Note that the ActiveChart object takes the PlotBy so that it will force plot method you want.

  7. #7
    New Member
    Join Date
    May 2007
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Try the following

    Set r1 = Range(Cells(1, 1), Cells(RowLimit, columnLimit))
    Sheets("CHRT1 - Av Against Due by Date").Select
    ActiveChart.PlotArea.Select
    ActiveChart.SetSourceData Source:=r1, PlotBy:=xlColumns

    I have just posted snippet of my code but you should be able to figure out how to use it in your code

    ritesh.narain@gmail.com

    Ritesh

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •