Consulting

Results 1 to 13 of 13

Thread: Chart data for a selected range

  1. #1
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location

    Chart data for a selected range

    In the Workbook attached, I am currently plotting recorded Min & Max temperatures on a chart. This is working, however I'd like to extend the capabilities of the charting tool to be able to define the period of data to be charted. Options being selected with the data validation value in cell G34. Can someone please show me how this is possible? Please note that there is currently data in columns D & E ( written in white font to enable clarity when reading the physical temperature data - it defines the safe temperature range for the use of a particular chemical that may be applied to bees, as indicated by the light brownish colour in the chart).

    The second objective will be in another thread.
    Attached Files Attached Files
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    @Paul, I amended your code slightly so that it just adjusts the chart as per the value in cell G4 (was cell G34) and this seems to run

    Sub UpdateChart()
        Dim iNumDays As Long, iTemp As Long, i As Long, o As Long
        Dim dateStart As Date, dateEnd As Date
        Dim rTemps As Range
        Dim rowStart As Long, rowEnd As Long
        With ActiveSheet
            If .ChartObjects.Count <> 1 Then Exit Sub
            Application.EnableEvents = False
            Set rTemps = .Range("A1").CurrentRegion
            iNumDays = .Range("G4").Value
            With rTemps
                If iNumDays > .Rows.Count Then iNumDays = .Rows.Count - 1
                rowStart = .Cells(.Rows.Count - iNumDays + 1, 1).Row
                rowEnd = .Cells(.Rows.Count, 1).Row
                dateStart = .Cells(rowStart, 1).Value
                dateEnd = .Cells(rowEnd, 1).Value
            End With
            With .ChartObjects(1).Chart
                .Axes(xlCategory).MinimumScale = CDbl(dateStart)
                .Axes(xlCategory).MaximumScale = CLng(dateEnd)
                .ChartTitle.Caption = "Temperature " & dateStart & " - " & dateEnd & Format(iNumDays, " (## days)")
            End With
            Application.EnableEvents = True
        End With
    End Sub
    Attached Files Attached Files
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    I encountered the same issue as well. We are looking forward to receiving numerous suggestions and assistance from everyone.
    Last edited by georgiboy; 03-26-2024 at 01:59 AM. Reason: Removed spam

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    @AussieBear

    I was playing around earlier trying to see if I could use a Pivot Chart, but it got too complicated

    I'll throw the 2 versions I ended up with over the fence if you want to take a look
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Thank you Paul. Version 2a works nicely, whilst I get an error message for 2b saying it wont function on a Mac ( The radio buttons I'm guessing). Now to complicate the issue even further rather than just selecting the last 30,60 or 90 days could this function be adapted to view the temp data for a seasonal period?

    As in Summer Autumn, Winter and Spring for each year. Is it possible to define a specific period rather than by a particular month defined range but by a specific date range. The reason for asking is that I'd like to see the lead in and out of a set defined period as in the below table as an example.

    Summer 23 December, January, Feburary 15/11/23 to 15/3/24
    Autumn 24 March, April, May 15/2/24 to 15/6/24
    Winter 24 June, July, August 15/5/24 to 15/9/24
    Spring 25 September, October, November 15/8/24 to 15/12/24

    A couple of things that come to mind. The temperature table will continue to grow over time hence the need to be able to continue to define additional periods to examine by graph.

    Secondly, I'm wondering if the data is simply being stored in excel should it be moved to Access/Filemaker Pro or something else and we just use Excel to show the graphing? I mean the temperature data is only growing at 365 rows X 3 Columns per year, so is it not really an issue?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    855
    Location
    About your "Secondly", you can chart an array. You can also store and retrieve arrays fairly simply from a text file. You could store all the different series in a text file and then retrieve whatever one is relevant. Just a suggestion. Jon Peltier has some nice code at this link to change your series data range.. seems like it might be relevant. HTH. Dave
    Macro to move chart data series reference down one row for all charts and all series in workbook. | MrExcel Message Board

  7. #7
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Thank you for the link Dave. Are you referring to this snippert of code?
    Sub MoveSeriesDataDownOneRow()
      Dim chtob As ChartObject
      For Each chtob In ActiveSheet.ChartObjects
        Dim iSrs As Long, nSrs As Long
        With chtob.Chart
          nSrs = .SeriesCollection.Count
          Dim sFmla As Variant
          ReDim sFmla(1 To nSrs)
          For iSrs = 1 To nSrs
            sFmla(iSrs) = .SeriesCollection(iSrs).Formula
          Next
          For iSrs = 1 To nSrs
            Dim vFmla As Variant
            vFmla = Split(sFmla(iSrs), ",")
            Dim rXVals As Range, rYVals As Range
            Set rXVals = Range(vFmla(1))
            Set rYVals = Range(vFmla(2))
            With .SeriesCollection(iSrs)
              .XValues = rXVals.Offset(1)
              .Values = rYVals.Offset(1)
            End With
          Next
        End With
      Next
    End Sub
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    @AB --

    I think your date ranges were a little off and I used the US MM/DD/YYYY date formats so you'll need to change them

    This is a little more hard coded than I would like, but if it's a problem I'll look after I get back from Spring Break
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Spring break.... I don't recall seeing your application for leave come across my desk? Just kidding, have a good time Paul and thank you for your assistance.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  10. #10
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    855
    Location
    @Aussiebear... Yes. Jon's code let's you both change the range for a chart series and change the series that is charted. Maybe it will be useful for you? Dave

  11. #11
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Thank you Dave, I will continue to look at that thread and the code contained within it.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  12. #12
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    855
    Location
    I can explain further. I'm sure the code that Paul (and/or Georgiboy) have provided is more than adequate and that this thread has already been "Solved" as indicated. But perhaps, this will provide an alternate approach. If I understand correctly, you're trying to chart different segments of your data for different seasons. Here's Jon's adjusted code with some operational commands following....
    Sub MoveSeriesData(RowMove As Integer, ColMove As Integer)
    Dim chtob As ChartObject
    On Error GoTo erfix
      For Each chtob In ActiveSheet.ChartObjects
        Dim iSrs As Long, nSrs As Long
        With chtob.Chart
          nSrs = .SeriesCollection.Count
          Dim sFmla As Variant
          ReDim sFmla(1 To nSrs)
          For iSrs = 1 To nSrs
            sFmla(iSrs) = .SeriesCollection(iSrs).Formula
          Next
          For iSrs = 1 To nSrs
            Dim vFmla As Variant
            vFmla = Split(sFmla(iSrs), ",")
            Dim rXVals As Range, rYVals As Range
            Set rXVals = Range(vFmla(1))
            Set rYVals = Range(vFmla(2))
            With .SeriesCollection(iSrs)
              .XValues = rXVals.Offset(RowMove)
              .Values = rYVals.Offset(RowMove, ColMove)
            End With
          Next
        End With
      Next
    erfix:
    If Err.Number <> 0 Then
    On Error GoTo 0
    MsgBox "No data Error!"
    End If
    End Sub
    
    
    'move X & Y series down one row
    Call MoveSeriesData(1, 0)
    'move X & Y series up one row
    Call MoveSeriesData(-1, 0)
    
    
    'move Y series column to right one column
    Call MoveSeriesData(0, 1)
    'move Y series column to left one column
    Call MoveSeriesData(0, -1)
    To test, set up a 2 series scatter chart with X values in A1:A10. Y values in B1:C10. Select A1 to B6 and create the chart. Trial the operational controls to change your range for a series or change the series location(column). For a simple example, if you had just a 1 series chart with all values in A & B rows 1 to 100, where spring values were rows 1 to 25; summer 26 to 50; fall 51 to 75; winter 76 to 100. Create a chart with A1 to B25 spring values. To show the next range (summer)...
    'move X & Y series down 25 rows
    Call MoveSeriesData(25, 0)
    To return to spring...
    'move X & Y series up 25 rows
    Call MoveSeriesData(-25, 0)
    You can set up some spin button code to change seasons.
    Alternatively, set up your X values in A, Spring values in B, Summer values in C, Fall values in D and Winter values in E. Select A & B spring values and create a chart. To select summer values...
    'move Y series column to right one column
    Call MoveSeriesData(0, 1)
    To return to spring values...
    Call MoveSeriesData(0, -1)
    Again, it seems like some spin button code could change your seasons. HTH. Dave

  13. #13
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Thank you Dave
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Tags for this Thread

Posting Permissions

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