If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dynamic Chart Generation
I am trying to use VBA to dynamically produce a number of charts. The number of charts produced depends on the amount of data that appears in a seperate spreadsheet... I have taken the code produced when I recorded a macro to produce one such chart and adapted it to meet my needs, however I am getting error 1004, "Unable to set the XValues property of the Series class" when I try to assign the x-axis values using a range I have dynamically created. I'm sure the problem is somewhere with my syntax, but however I try to phrase it I cannot seem to get it to work. Please find a copy of my code below - any help would be much appreciated! Jon In the following code the variable Count determines how many charts will need to be produced: code Dim i As Integer Dim Count As Integer Dim intGraphDisplayStartRow As Integer Dim rngTempRange1 As Range Dim rngTempRange2 As Range Dim rngTempRange3 As Range If Count 0 Then For i = 1 To Count intGraphDisplayStartRow = (i * 13) + 2 ThisWorkbook.Worksheets("Page 2").Select Charts.Add Set rngTempRange1 = ThisWorkbook.Worksheets("WorkspaceTemp") _ .Range("A" & Trim(CStr(intGraphDisplayStartRow _ & ":A" & intGraphDisplayStartRow + 11))) Set rngTempRange2 = ThisWorkbook.Worksheets("WorkspaceTemp") _ .Range("C" & Trim(CStr(intGraphDisplayStartRow)) & ":C" _ & Trim(CStr(intGraphDisplayStartRow + 11))) Set rngTempRange3 = ThisWorkbook.Worksheets("WorkspaceTemp") _ .Range("C" & Trim(CStr(intGraphDisplayStartRow - 1))) ActiveChart.ChartType = xlLine ActiveChart.SeriesCollection(1).XValues = rngTempRange1 ActiveChart.SeriesCollection(1).Values = rngTempRange2 ActiveChart.SeriesCollection(1).Name = rngTempRange3 ActiveChart.Location Whe=xlLocationAsObject, Name:="Page 2" Next End If /code -- jonwatts ------------------------------------------------------------------------ jonwatts's Profile: http://www.excelforum.com/member.php...o&userid=28038 View this thread: http://www.excelforum.com/showthread...hreadid=475451 |
#2
|
|||
|
|||
Hi jonwatts,
The only thing wrong with your code is that when you create a new chart Series(1) does not exist, so you need to add a new series. Try this slightly modified version. Dim i As Integer Dim Count As Integer Dim iStartRow As Integer Dim iEndRow As Integer Dim rngTempRange1 As Range Dim rngTempRange2 As Range Dim rngTempRange3 As Range Dim wks As Worksheet ' Just for neatness, and so I could test on my own ' worksheet, we set a variable for the worksheet Count = 2 Set wks = ThisWorkbook.Worksheets("WorkspaceTemp") If Count 0 Then For i = 1 To Count iStartRow = (i * 13) + 2 iEndRow = iStartRow + 11 ' iEndRow calculated here, makes the range calcs shorter ThisWorkbook.Worksheets("Page 2").Select Set rngTempRange1 = wks.Range("A" & Trim(CStr(iStartRow & ":A" & iEndRow))) Set rngTempRange2 = wks.Range("C" & Trim(CStr(iStartRow)) & ":C" & Trim(CStr(iEndRow))) Set rngTempRange3 = wks.Range("C" & Trim(CStr(iStartRow - 1))) Charts.Add With ActiveChart .ChartType = xlLine .SeriesCollection.Add rngTempRange2 ' the only change you really need .SeriesCollection(1).XValues = rngTempRange1 .SeriesCollection(1).Name = rngTempRange3 .Location Whe=xlLocationAsObject, Name:="Page 2" End With ActiveChart.Parent.Top = iStartRow * 5 ActiveChart.Parent.Left = iStartRow * 5 Next End If Ed Ferrero http://edferrero.m6.net/ I am trying to use VBA to dynamically produce a number of charts. The number of charts produced depends on the amount of data that appears in a seperate spreadsheet... I have taken the code produced when I recorded a macro to produce one such chart and adapted it to meet my needs, however I am getting error 1004, "Unable to set the XValues property of the Series class" when I try to assign the x-axis values using a range I have dynamically created. I'm sure the problem is somewhere with my syntax, but however I try to phrase it I cannot seem to get it to work. Please find a copy of my code below - any help would be much appreciated! Jon In the following code the variable Count determines how many charts will need to be produced: code Dim i As Integer Dim Count As Integer Dim intGraphDisplayStartRow As Integer Dim rngTempRange1 As Range Dim rngTempRange2 As Range Dim rngTempRange3 As Range If Count 0 Then For i = 1 To Count intGraphDisplayStartRow = (i * 13) + 2 ThisWorkbook.Worksheets("Page 2").Select Charts.Add Set rngTempRange1 = ThisWorkbook.Worksheets("WorkspaceTemp") _ Range("A" & Trim(CStr(intGraphDisplayStartRow _ & ":A" & intGraphDisplayStartRow + 11))) Set rngTempRange2 = ThisWorkbook.Worksheets("WorkspaceTemp") _ Range("C" & Trim(CStr(intGraphDisplayStartRow)) & ":C" _ & Trim(CStr(intGraphDisplayStartRow + 11))) Set rngTempRange3 = ThisWorkbook.Worksheets("WorkspaceTemp") _ Range("C" & Trim(CStr(intGraphDisplayStartRow - 1))) ActiveChart.ChartType = xlLine ActiveChart.SeriesCollection(1).XValues = rngTempRange1 ActiveChart.SeriesCollection(1).Values = rngTempRange2 ActiveChart.SeriesCollection(1).Name = rngTempRange3 ActiveChart.Location Whe=xlLocationAsObject, Name:="Page 2" Next End If /code -- jonwatts ------------------------------------------------------------------------ jonwatts's Profile: http://www.excelforum.com/member.php...o&userid=28038 View this thread: http://www.excelforum.com/showthread...hreadid=475451 |
#3
|
|||
|
|||
Dynamic Chart Generation
A further refinement is to use
Worksheets("Page 2").ChartObjects.Add(dimensions) rather than Charts.Add ... ActiveChart.Location Whe=xlLocationAsObject, Name:="Page 2" Work your position parameters ActiveChart.Parent.Top = iStartRow * 5 ActiveChart.Parent.Left = iStartRow * 5 into the left, top, width, and height dimensions in the ChartObjects.Add statement. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ed Ferrero wrote: Hi jonwatts, The only thing wrong with your code is that when you create a new chart Series(1) does not exist, so you need to add a new series. Try this slightly modified version. Dim i As Integer Dim Count As Integer Dim iStartRow As Integer Dim iEndRow As Integer Dim rngTempRange1 As Range Dim rngTempRange2 As Range Dim rngTempRange3 As Range Dim wks As Worksheet ' Just for neatness, and so I could test on my own ' worksheet, we set a variable for the worksheet Count = 2 Set wks = ThisWorkbook.Worksheets("WorkspaceTemp") If Count 0 Then For i = 1 To Count iStartRow = (i * 13) + 2 iEndRow = iStartRow + 11 ' iEndRow calculated here, makes the range calcs shorter ThisWorkbook.Worksheets("Page 2").Select Set rngTempRange1 = wks.Range("A" & Trim(CStr(iStartRow & ":A" & iEndRow))) Set rngTempRange2 = wks.Range("C" & Trim(CStr(iStartRow)) & ":C" & Trim(CStr(iEndRow))) Set rngTempRange3 = wks.Range("C" & Trim(CStr(iStartRow - 1))) Charts.Add With ActiveChart .ChartType = xlLine .SeriesCollection.Add rngTempRange2 ' the only change you really need .SeriesCollection(1).XValues = rngTempRange1 .SeriesCollection(1).Name = rngTempRange3 .Location Whe=xlLocationAsObject, Name:="Page 2" End With ActiveChart.Parent.Top = iStartRow * 5 ActiveChart.Parent.Left = iStartRow * 5 Next End If Ed Ferrero http://edferrero.m6.net/ I am trying to use VBA to dynamically produce a number of charts. The number of charts produced depends on the amount of data that appears in a seperate spreadsheet... I have taken the code produced when I recorded a macro to produce one such chart and adapted it to meet my needs, however I am getting error 1004, "Unable to set the XValues property of the Series class" when I try to assign the x-axis values using a range I have dynamically created. I'm sure the problem is somewhere with my syntax, but however I try to phrase it I cannot seem to get it to work. Please find a copy of my code below - any help would be much appreciated! Jon In the following code the variable Count determines how many charts will need to be produced: code Dim i As Integer Dim Count As Integer Dim intGraphDisplayStartRow As Integer Dim rngTempRange1 As Range Dim rngTempRange2 As Range Dim rngTempRange3 As Range If Count 0 Then For i = 1 To Count intGraphDisplayStartRow = (i * 13) + 2 ThisWorkbook.Worksheets("Page 2").Select Charts.Add Set rngTempRange1 = ThisWorkbook.Worksheets("WorkspaceTemp") _ Range("A" & Trim(CStr(intGraphDisplayStartRow _ & ":A" & intGraphDisplayStartRow + 11))) Set rngTempRange2 = ThisWorkbook.Worksheets("WorkspaceTemp") _ Range("C" & Trim(CStr(intGraphDisplayStartRow)) & ":C" _ & Trim(CStr(intGraphDisplayStartRow + 11))) Set rngTempRange3 = ThisWorkbook.Worksheets("WorkspaceTemp") _ Range("C" & Trim(CStr(intGraphDisplayStartRow - 1))) ActiveChart.ChartType = xlLine ActiveChart.SeriesCollection(1).XValues = rngTempRange1 ActiveChart.SeriesCollection(1).Values = rngTempRange2 ActiveChart.SeriesCollection(1).Name = rngTempRange3 ActiveChart.Location Whe=xlLocationAsObject, Name:="Page 2" Next End If /code -- jonwatts ------------------------------------------------------------------------ jonwatts's Profile: http://www.excelforum.com/member.php...o&userid=28038 View this thread: http://www.excelforum.com/showthread...hreadid=475451 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating a dynamic chart for an engineering spreadsheet | Cal@tech-etch | Charts and Charting | 1 | November 3rd, 2004 07:50 PM |
Selecting Charts in a Macro | Herman Merman | Charts and Charting | 1 | August 18th, 2004 12:25 AM |
Static Range, Dynamic Chart Data | Jon Peltier | Charts and Charting | 1 | February 6th, 2004 08:42 PM |
'Closing' chart window *and* removing chart using VBA | Marc R. Bertrand | Charts and Charting | 7 | December 17th, 2003 04:30 PM |