Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Macros for making charts

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

    Default

    I need to make a macros that populates 124 separate charts with different data. On sheet one, the data is there with six columns and three rows. The charts have to be bar charts and they are reading different rows each time. Please help.

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    My experience on this board tells me that you will find it dificult to get someone to solve this problem in it's entirety. I suggest that you be prepared for a reasonably slow process. We'll help you to learn how to do it, but finding someone with the time to do the whole thing is tricky.

    Here's some questions to help you put more detail into your request:

    Can you do any of this yourself? i.e. What is your VBA experience? Do you record macros and use them or do you frequently create code from scratch? Or none of the above?

    Do you know how to create a chart in VBA?

    Will the charts be embedded in a sheet or will they be separate chartsheets?

    Is all of the data going to be on one sheet or do you have several sheets with a separate page for each data set?

    What ranges are used to store the data and why is the range different each time?

    That's all I can think of right now. I hope it's a good start.

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

    Default

    I usually record the macros. I can do some of it myself, but yes, I'm very new to this. The charts have separate chart sheets and all of the charts are reading from the same sheet. The reason why the range is different each time is because the data has the same units, but different questions that each chart has to represent. I actually have a spreadsheet with an example, but I can't post it on here. Thanks.

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-20 11:56, Lokicue wrote:
    The reason why the range is different each time is because the data has the same units, but different questions that each chart has to represent. I actually have a spreadsheet with an example, but I can't post it on here. Thanks.
    OK, what you're asking is simple enough, except for the bit about the data. I'm not quite following you. If the info is not confidential, email a copy of the workbook without macros to me at j_b68@hotmail.com

    Also, create a chart in the workbook that is representative of the format you want.

    An explanation of the data and exactly what you would probably be good as well. (Since I'll be looking at the data with the explanation.)

    If I can help, I'll post the VBA code here, so that others can see it.

    Cheers


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

    Default

    K. I sent it to you. Thank you so much for taking a look at it.

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, this turned into an hellacious mini-project, the like of which I'll never do again, but it was educational so I've got no hard feelings.

    I'm using XL2000 on a 800MHz PC running NT4 SP6 (I think).

    The workbook I was given contained a data sheet and a sample chart that was embedded on a separate sheet.

    My intention was to copy the sample chart and simply change the data series each time to create a new chart.

    This worked great until I got to chart 70. I was then getting "1004" errors. A quick look in the MSKB produced this little gem:

    http://support.microsoft.com/search/...;en-us;Q210684

    What this meant was that I had to move my code into a different workbook so that I could save and close the data workbook after a certain number of charts had been created. (this is the only workaround MS offered to get rid of the "1004" error.

    The code then worked great, until I hit chart 100 and got an "Automation Error". It turned out I'd hit the fonts limit as described by this joyous article:

    http://support.microsoft.com/default...;en-us;Q168650

    So I got rid of Autoscale and made all the fonts on the chart the same. (it's strange that I got this error since I'm using XL2000 and this is apparently an XL97 only bug)

    Finally after many opening and closings of Excel I got the code to create 124 charts on 124 worksheets. Writing this explanation has now taken me into the 3 hour mark for solving the problem. Yes I am an idiot, yes I should get fired, but this looked like work.

    Anyway, here is the code that I finally came up with, I'm posting it here for posterity:


    Option Explicit

    Public Sub CreateCharts()

    'Declare Variables
    Dim oBook As Workbook
    Dim sFullPath As String
    Dim DataSht As Worksheet
    Dim TemplateSht As Worksheet
    Dim sht As Worksheet
    Dim cht As Chart
    Dim sQuestion As String
    Dim sNameFirstPart As String
    Dim sNameSecondPart As String
    Dim sSeries1Values As String
    Dim sSeries2Values As String
    Dim iFirstRow As Integer
    Dim iLastRow As Integer
    Dim iFirstCol As Integer
    Dim iLastCol As Integer
    Dim iRow As Integer
    Dim iCol As Integer
    Dim iCount As Integer

    'Turn off screen updates to speed up code
    Application.ScreenUpdating = False

    ' Initialise Variables

    sFullPath = "C:TempCrazySpreadsheet.xls" 'This is where we will save our workbook.
    Set oBook = Workbooks("CrazySpreadsheet.xls") 'Set the workbook to the data workbook.
    Set DataSht = oBook.Sheets("Inpt.Data") 'The sheet with the data
    Set TemplateSht = oBook.Sheets("TEMPLATE") 'The template sheet we're copying
    iFirstCol = 2 'First column with data
    iLastCol = 8 'Last column with data
    iFirstRow = 4 'First row with data
    iLastRow = DataSht.Range("A65536").End(xlUp).Row 'Last row with data. This is picked up automatically

    For iCol = iFirstCol To iLastCol
    sNameFirstPart = DataSht.Cells(iFirstRow - 2, iCol).Value 'First part of the sheet name, i.e. the Unit name

    For iRow = iFirstRow To iLastRow Step 4
    sQuestion = DataSht.Cells((iRow - 1), 2).Value 'The question for the next sheet
    sNameSecondPart = "(" & Left(sQuestion, (InStr(1, sQuestion, ":") - 1)) & ")" 'Lastpart of the sheetname i.e. the question number
    'Copy Template
    TemplateSht.Copy After:=oBook.Sheets.Item(oBook.Sheets.Count)
    Set sht = Sheets(Sheets.Count)
    Set cht = sht.ChartObjects(1).Chart

    'Increment counter
    iCount = iCount + 1

    'Rename sheet
    sht.Name = sNameFirstPart & sNameSecondPart

    'Add unit name and question to the sheet with chart
    sht.Range("C3").Value = sNameFirstPart
    sht.Range("B5").Value = sht.Range("B5").Value & " " & sQuestion

    'Set new data ranges
    sSeries1Values = "=(Inpt.Data!R" & iRow & "C" & iCol & ",Inpt.Data!R" & iRow & "C9:R" & iRow & "C10)"
    sSeries2Values = "=(Inpt.Data!R" & (iRow + 1) & "C" & iCol & ",Inpt.Data!R" & (iRow + 1) & "C9:R" & (iRow + 1) & "C10)"

    'Change old data ranges
    With cht
    .SeriesCollection(1).Values = sSeries1Values
    .SeriesCollection(2).Values = sSeries2Values
    End With
    'Save and close book because of Excel limitation with copying sheets
    If iCount Mod 20 = 0 Then
    oBook.Close SaveChanges:=True
    Set oBook = Nothing
    Set oBook = Application.Workbooks.Open(sFullPath)
    Set DataSht = oBook.Sheets("Inpt.Data") 'The sheet with the data
    Set TemplateSht = oBook.Sheets("TEMPLATE") 'The template sheet we're copying
    End If


    Next
    Next

    End Sub


    That's all I have to say about that.

    Thank you.

    Good night.

    (Disclaimer, this is probably not the best way to solve this problem, if I had more time I'd have created the charts programmatically.)

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

    Default

    Thank you so much Sorry about it taking so long.

  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No problem, have you got it working? One thing I forgot to tell you was that I changed your data spread sheet slightly. the first two questions, questions 15A and questions 1A (I think) were in a different format to the rest of the questions on the sheet. These two cells had "Question: 15A etc etc." in one cell whereas all of the other cells had "Question" in one cell and "15A - etc etc" in another. So I broke 15A and 1A into separate cells. The reason I did this was so that I could rename the sheets using the "15A" and "1A" parts more easily and to make the sheet uniform.

    I hope this makes sense.

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
  •