Chart and Data

[AndyVoClosser]

Anyone can help me with this code. It does not work with If statement. Please. Thank you

Sub ShowDataChart1()
    Dim ws As Worksheet
    Dim myChart As ChartObject
    Dim ser As Series
    Dim selectedPointIndex As Long
   
    ' Set the active sheet
    Set ws = ActiveSheet
   
    ' Assuming the chart is the first chart on the sheet
    Set myChart = ws.ChartObjects(1)
   
    ' Assuming you want to work with the first series in the chart
    Set ser = myChart.Chart.SeriesCollection(1)
   
    ' Loop through each point in the series
    For selectedPointIndex = 1 To ser.Points.Count
        ' Check if the current point is selected
        If ser.Points(selectedPointIndex).Select Then
            ' Activate the "Data (4)" sheet
            Sheets("Chart Data").Activate
           
            ' Select the corresponding range based on selectedPointIndex
            Select Case selectedPointIndex
                Case 4
                    Range("D2:F2").Select
                Case 5
                    Range("D6:F6").Select
                ' Add more cases as needed
            End Select
           
            ' Exit the loop once a selected point is found
            Exit For
        End If
    Next selectedPointIndex
End Sub

 

[NateSC]

The IF isn't really a test. You have:

If ser.Points(selectedPointIndex).Select Then

selectedPointIndex will be 1 on the first pass and the If statement will always evaluate as TRUE. You are evaluating the ability to select the first point in the series? I could be misreading this.

When it evaluates as TRUE, the last step in the IF is to EXIT FOR at which point it goes to the EXIT SUB line (there isn't anything after it exits the FOR so it is at the end).

 

[AndyVoClosser]

Hello, I am trying to revised my code as

Sub ShowDataChart1()
Dim ws As Worksheet
Dim myChart As ChartObject
Dim ser As Series



Set ws = ActiveSheet
Set myChart = ws.ChartObjects(1)
Set ser = myChart.Chart.SeriesCollection(1)

If ser.Points(1).Select Then
Sheets("Chart Data").Activate
Range("D2:F2").Select
End If

If ser.Points(2).Select Then
Sheets("Chart Data").Activate
Range("D3:F3").Select
End If

If ser.Points(3).Select Then
Sheets("Chart Data").Activate
Range("D4:F4").Select
End If

If ser.Points(4).Select Then
Sheets("Chart Data").Activate
Range("D5:F5").Select
End If

If ser.Points(5).Select Then
Sheets("Chart Data").Activate
Range("D6:F6").Select
End If

End Sub

But it only works for the final IF statement. Could you help me check it. I appreciate you so much. Thank you

 

[NateSC]

I stepped through your code to be sure and it actually runs through all of the IF statements. Every one of those evaluates as TRUE. You only see the last one appear to work, but the code is running through all the IF's, selecting the specified range, and then proceeding. If you step through your code with F8 you will see what it is doing.

What exactly are you trying to test? It looks like you want to select a specific range, but I don't understand your criteria.

 

[NateSC]

Is this what you want?

Sub ShowDataChart1()
Dim p As Point

    If TypeOf Selection Is Point Then
        Set p = Selection
    
        ' Select the corresponding range
        Select Case CLng(Split(p.Name, "P")(1))
            Case 1
                Range("D2:F2").Select
            
            Case 2
                Range("D3:F3").Select
            
            Case 3
                Range("D4:F4").Select
            
            Case 4
                Range("D5:F5").Select
    
            Case 5
                Range("D6:F6").Select
            ' Add more cases as needed
            
        End Select
    End If

End Sub

 

[AndyVoClosser]

Hi NateSC. Yes, it is what I am looking for. However, there are 3 series in my chart. How I can do that for each series. Because each point of each series will refer to a different range of data. I appreciate your support here. Thank you so much

 

[NateSC]

I broke it down into a series of variables so you can build the response however you would like. My Case structure is just one option of how you can use it. I did not test this with >9 series, but it should work even if your number of series is double (or triple) digits. If you have trouble with the second half, let me know and I will try to help.

The major portion you need is what I defined as mySeries and myPoint.

Sub ShowDataChart1()
Dim p As Point

    If TypeOf Selection Is Point Then
        Set p = Selection
       
        Dim myLen As Integer
        Dim mySeries As Integer
        Dim myPoint As Integer
        Dim mySStart As String
        Dim mySEnd As String
        Dim myP As Integer
       
        myLen = InStr(1, p.Name, "P") - InStr(1, p.Name, "S") - 1
        mySeries = Mid(p.Name, InStr(1, p.Name, "S") + 1, myLen)
        myPoint = Split(p.Name, "P")(1)

        ' Select the corresponding range
        Select Case mySeries
            Case 1
                mySStart = "D"
                mySEnd = "F"
            Case 2
                mySStart = "G"
                mySEnd = "I"
            Case 3
                mySStart = "J"
                mySEnd = "L"
        End Select
       
        Select Case myPoint
            Case 1
                myP = 2
           
            Case 2
                myP = 3
           
            Case 3
                myP = 4
           
            Case 4
                myP = 5
   
            Case 5
                myP = 6
            ' Add more cases as needed
        End Select
       
        Range(mySStart & myP & ":" & mySEnd & myP).Select
       
    End If

End Sub

 

[AndyVoClosser]

Hello NateSC. Thank you so much. Your code works well with some change to fit to my requirement. I appreciate you so much.

 

[NateSC]

If you don't mind, please mark the thread as solved. Thank you!