Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find maximum in chart range

    Hi guys,

    I have a question and i've working on it for 2 weeks now.

    I have a chart with some graphs on it. in any of the line i need to mark the initial point and the final point then the program will mark the maximum in this range

    Our chart in some point will become like (almost) and in this range, i need to get the maximum...

    Code:
    Sub Ponto1()
        Set x1 = Selection
        With x1
            .MarkerStyle = -4168
            .MarkerSize = 5
            .Format.Line.Visible = msoTrue
            .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
            .Format.Line.Transparency = 0
            .Format.Line.Weight = 0.75
        End With
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("Sheet1")
        'ws.Range("G4").Value = x1
        'ws.Range("G4").Value = Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
        ws.Range("G4").Value = Application.Transpose(x1.Values)
    End Sub
    Sub Ponto2()
        
        Set x2 = Selection
        With x2
            
            .MarkerStyle = -4168
            .MarkerSize = 5
            .Format.Line.Visible = msoTrue
            .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
            .Format.Line.Transparency = 0
            .Format.Line.Weight = 0.75
        End With
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("Sheet1")
        ws.Range("H4").Value = x2
        'Call calcula_Max
    End Sub
    Sub calcula_Max()
        
        Dim xR As Object
        Set xR = x1
        
        
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("Sheet1")
        ws.Range("G4").Value = x1
        ws.Range("H4").Value = x2
        
        With xR
            .MarkerStyle = -4168
            .MarkerSize = 5
            .Format.Line.Visible = msoTrue
            .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
            .Format.Line.Transparency = 0
            .Format.Line.Weight = 0.75
            .ApplyDataLabels
            .DataLabel.NumberFormat = "0"
        End With
        
        x1.MarkerStyle = -4142
        x2.MarkerStyle = -4142
        
        'Set x1 = ""
        'Set x2 = ""
        
    End Sub
    it is incomplete because i could't find the first and the last value (x1 and x2) to find the hiest y in this range

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Dear Ric:

    Welcome to the lounge. Perhaps the following article can assist. In effect, you have excel find the Maximum, set this as its own chart value and make it part of the chart as a "point" rather than a line. If you needed to have as VBA, turn on the Macro Recorder, go throught the steps and review the Macro VBA lines to determine what your missing in the above code.
    The article can be found at Chandoo.org

  3. #3
    New Lounger
    Join Date
    Feb 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It did not work, i'm stil stucked

  4. #4
    New Lounger
    Join Date
    Nov 2015
    Posts
    17
    Thanks
    8
    Thanked 2 Times in 2 Posts
    You can setup a duplicate series in your chart with column of data that only had the max point.

    ie: with original data in A1:A100, a formula in column such as =if(a1=max($A$1:$A$100),a1,na()) will capture the max value, then plot the data as a line (just turn off the line and only show the points, I usually make the marker a circle with no fill, marker line color red, and marker size large enough to have the circle larger than the markers in the original dataset that I'm trying to flag)

    From here, it's a simple step to modify the formula for the top 10 (for example) values .........

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    ric.de.abreu,

    Here is some code that will allow the user to select two points (successively) on the graph then it will format the points, change the line color between the two points, and then marks the highest and lowest point.

    The code builds on Zeno's suggestion of Max and Min columns as additional series but performs the calculations in the code then dynamically adjusts the ranges of these functions to match the selected points.

    HTH,
    Maud

    Before points selected:
    graph1.png

    After points selected:
    graph2.png

    Code:
    Private PT1, PT2, State As Integer
    Private Sub Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    '--------------------------------------
    'SET AND DECLARE VARIABLES
    Dim I As Integer
    '--------------------------------------
    'TEST IF ONE MARKER IS SELECTED
    If ElementID <> 3 Then 'IS MARKER?
        State = 0
        PT1 = 0
        PT2 = 0
        Exit Sub
    End If
    If Arg2 = -1 Then 'IS SELECTION > 1
        State = 0
        PT1 = 0
        PT2 = 0
        Exit Sub
    End If
    '--------------------------------------
    'DETECT THE CURRENT STEP IN THE PROCESS
    Select Case State
        Case 0 'CLEAR AND POPULATE POINT1
            PT1 = Arg2 'REMEMBER FIRST SELECTED POINT
            Worksheets("Data").Range("F2:G100").ClearContents 'CLEAR MIN/MAX FORMULAS
            Application.EnableEvents = False
    '--------------------------------------
    'RESET CHART SERIES (MARKER STYLE,SIZE,FILL AND LINE COLOR)
            ActiveChart.FullSeriesCollection(1).Select
            Selection.MarkerStyle = -4142
            Selection.MarkerSize = 5
            Selection.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1
            Selection.Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent1
    '--------------------------------------
    'FORMAT FIRST SELECTED POINT
            ActiveChart.FullSeriesCollection(1).Points(PT1).Select
            Application.EnableEvents = True
            With Selection
                .MarkerStyle = 8
                .MarkerSize = 10
                Selection.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1
            End With
            State = 1
            Exit Sub
    '--------------------------------------
    'FORMAT SECOND SELECTED POINT
        Case 1 'POPULATE POINT2 AND FIND MAX
            PT2 = Arg2
            With Selection
                .MarkerStyle = 8
                .MarkerSize = 10
                Selection.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1
            End With
            State = 0
            Application.EnableEvents = False
    '--------------------------------------
    'FORMAT GRAPH LINE BETWEEN SELECTED POINTS
            For I = PT1 + 1 To PT2
                ActiveChart.FullSeriesCollection(1).Points(I).Select
                Selection.Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2
            Next I
    '--------------------------------------
    'ADD MIN/MAX FORMULAS TO DATA SHEET COLUMNS F AND G
            With Worksheets("Data")
                For I = 2 To 14
                    If I > PT1 And I <= PT2 + 1 Then
                        If .Cells(I, "E") = WorksheetFunction.Max(.Range("$E" & PT1 + 1 & ":E$" & PT2 + 1)) Then
                            .Cells(I, "F") = .Cells(I, "E")
                        Else:
                            .Cells(I, "F") = "#N/A"
                        End If
                    Else:
                        .Cells(I, "F") = "#N/A"
                    End If
                Next I
                For I = 2 To 14
                    If I > PT1 And I <= PT2 + 1 Then
                        If .Cells(I, "E") = WorksheetFunction.Min(.Range("$E" & PT1 + 1 & ":E$" & PT2 + 1)) Then
                            .Cells(I, "G") = .Cells(I, "E")
                        Else:
                            .Cells(I, "G") = "#N/A"
                        End If
                    Else:
                        .Cells(I, "G") = "#N/A"
                    End If
                Next I
            End With
            Application.EnableEvents = True
        End Select
    End Sub
    Attached Files Attached Files

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    For those who may be interested, The code was developed in 2013 but to make it backwards compatible with 2010 or less, change all instances of FullSeriesCollection to just SeriesCollection.

    Maud

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
  •