Math with DataLabel.Caption

[marksc92]

It has been a while since I have been this stumped.. I am trying to compare points values in 2 series so I can adjust the position of data labels so they do not overlap. This works well for all but one of my charts, which after some troubleshooting I learned is because it is evaluating them as strings so comparing 70 and 80 is easy but it doesn't give me the result I am looking for when comparing 95 to 105, it thinks 95 is bigger because 9 > 1. Does anyone know a way to get the point value or convert the DataLabel.Caption to a number. Int(), Cint() didn't work. Here is the code

Sheet3.ChartObjects("MS").Activate ' Activate MS Chart


With ActiveChart
        ' Loop through data points in Series 2 and 3
        For J = 1 To .SeriesCollection(2).Points.Count
        
        '' If the point in series 2 is larger than the point in series 3,
        '' put 2's label above the line, and 3's below the line,
        '' otherwise, do the opposite.
        
            If .SeriesCollection(2).Points(J).DataLabel.Caption > .SeriesCollection(3).Points(J).DataLabel.Caption Then
            .SeriesCollection(2).Points(J).DataLabel.Position = xlLabelPositionAbove
            .SeriesCollection(3).Points(J).DataLabel.Position = xlLabelPositionBelow
            Else
            .SeriesCollection(3).Points(J).DataLabel.Position = xlLabelPositionAbove
            .SeriesCollection(2).Points(J).DataLabel.Position = xlLabelPositionBelow
           End If
        Next J ' Loop through next set of points
End With

 

[hotabae]

I honestly have no idea if this will help, but I figure its worth a shot. Stores the captions as an intermediate variable and then casts them as 'long' datatype for comparison.

Sheet3.ChartObjects("MS").Activate ' Activate MS Chart

Dim labelPosA As String
Dim labelPosB As String

With ActiveChart

        ' Loop through data points in Series 2 and 3
        For J = 1 To .SeriesCollection(2).Points.Count
        
        '' If the point in series 2 is larger than the point in series 3,
        '' put 2's label above the line, and 3's below the line,
        '' otherwise, do the opposite.
        
        labelPosA = .SeriesCollection(2).Points(J).DataLabel.Caption
        labelPosB = .SeriesCollection(3).Points(J).DataLabel.Caption
        
            If CLng(labelPosA) > CLng(labelPosB) Then

            .SeriesCollection(2).Points(J).DataLabel.Position = xlLabelPositionAbove
            .SeriesCollection(3).Points(J).DataLabel.Position = xlLabelPositionBelow

            Else

            .SeriesCollection(3).Points(J).DataLabel.Position = xlLabelPositionAbove
            .SeriesCollection(2).Points(J).DataLabel.Position = xlLabelPositionBelow

           End If

        Next J ' Loop through next set of points

End With

 

[marksc92]

Clever, but no that did not work. I also tried Dim as Long, and multiplying the value by 1, neither worked. Thanks for trying. I'll keep banging my head against the wall until I figure it out

 

[marksc92]

I got it figured out.

Sheet3.ChartObjects("MS").Activate ' Activate MS Chart


Dim X As Variant
Dim Y As Variant


With ActiveChart


        ' Loop through data points in Series 2 and 3
        X = .SeriesCollection(2).Values
        Y = .SeriesCollection(3).Values
        
        For J = 1 To UBound(X)
        
        '' If the point in series 2 is larger than the point in series 3,
        '' put 2's label above the line, and 3's below the line,
        '' otherwise, do the opposite.
        
        
            If X(J) > Y(J) Then
            .SeriesCollection(2).Points(J).DataLabel.Position = xlLabelPositionAbove
            .SeriesCollection(3).Points(J).DataLabel.Position = xlLabelPositionBelow
            Else
            .SeriesCollection(3).Points(J).DataLabel.Position = xlLabelPositionAbove
            .SeriesCollection(2).Points(J).DataLabel.Position = xlLabelPositionBelow
            End If
            
            
        Next J ' Loop through next set of points


End With