Using VBA to Loop Through and Automatically Position Data Labels?

[jim1234]

Hello - I am VBA novice, trying to make a macro that can loop through and automatically position data labels in a simple bar chart.

For the most part, outside end data labels are fine. However, for some values that are *slightly* negative, the data labels are overlapping with the X-Axis labels.

So I'd like to write something along the lines of the following:

* Loop through all data labels
* If bar < 0 & > -40, position data label at x position (I'd like to align with the -40 Y axis label)
* Else xlLabelPosistionOutsideEnd

I took a stab at writing this but its a little shoddy. I don't think I'm properly looping through the data points (I have to select all labels before running the macro for it to not send an error message), and I'm not sure the Selection.Top piece is what I want to place the data label where I want:

Sub Macro4()

Dim mychart As ChartObject
Set mychart = ActiveSheet.ChartObjects("Chart 4")


With mychart.Chart.SeriesCollection(1)
Dim myvalues
myvalues = .Values

Dim i As Long

For i = LBound(myvalues) To UBound(myvalues)

If .Points(i).HasDataLabel And myvalues(i) < 0 And myvalues(i) > -40 Then

Selection.Position = xlLabelPositionOutsideEnd
Selection.Top = 146.623

Else

Selection.Position = xlLabelPositionOutsideEnd

End If
Next
End With


End Sub

Any help you guys can give me here would be really appreciated.

Thanks!

 

[jim1234]

Update: I think I am pretty close to I want here. This should be a lot cleaner than above... I have just one small issue to iron out now, which is highlighted in red. I am getting an error message there. I am trying to see if data point i (there are 12 data points, so for loop is for i = 1 to 12) fits the conditions, and if it does then move it lower down than xlLabelPosistionOutsideEnd would place it...

Thank you in advance.

Sub Macro8()
'
' Macro8 Macro
'
Dim i As Integer


For i = 1 To 12

ActiveChart.FullSeriesCollection(1).Points(i).DataLabel.Select

If ActiveChart.FullSeriesCollection(1).Points(i) < 0 And ActiveChart.FullSeriesCollection(1).Points(i) > -40 Then

Selection.Position = xlLabelPositionOutsideEnd
Selection.Top = 146.623

Else

Selection.Position = xlLabelPositionOutsideEnd

End If

Next i

End Sub