VBA for loop using the counter integer to retrieve content from cells

Question

I'm really a beginner with VBA macros and their syntax so bear with me please. I have been trying to assign content from specific cells to labels in a scatter plot. Started out by recording my own manual assignment and "cleaning" that code. Works fine for the sample set I'm using right now.

Sub Rename_scatter()
'
' Rename_scatter Macro
'
'
    ActiveSheet.ChartObjects("Risikomatrix").Activate
    ActiveChart.FullSeriesCollection(1).DataLabels.Select

    ActiveChart.FullSeriesCollection(1).Points(1).DataLabel.Select
      ActiveChart.SeriesCollection(1).DataLabels(1).Format.TextFrame2.TextRange. _
        Characters.Text = ""
      ActiveChart.SeriesCollection(1).DataLabels(1).Format.TextFrame2.TextRange. _
        InsertChartField msoChartFieldFormula, "='Risiko-Log'!$B$2", 1

    ActiveChart.FullSeriesCollection(1).Points(2).DataLabel.Select
      ActiveChart.SeriesCollection(1).DataLabels(2).Format.TextFrame2.TextRange. _
        Characters.Text = ""
      ActiveChart.SeriesCollection(1).DataLabels(2).Format.TextFrame2.TextRange. _
        InsertChartField msoChartFieldFormula, "='Risiko-Log'!$B$3", 1

    ActiveChart.FullSeriesCollection(1).Points(3).DataLabel.Select
      ActiveChart.SeriesCollection(1).DataLabels(3).Format.TextFrame2.TextRange. _
        Characters.Text = ""
      ActiveChart.SeriesCollection(1).DataLabels(3).Format.TextFrame2.TextRange. _
        InsertChartField msoChartFieldFormula, "='Risiko-Log'!$B$4", 1

    ActiveChart.FullSeriesCollection(1).Points(4).DataLabel.Select
      ActiveChart.SeriesCollection(1).DataLabels(4).Format.TextFrame2.TextRange. _
        Characters.Text = ""
      ActiveChart.SeriesCollection(1).DataLabels(4).Format.TextFrame2.TextRange. _
        InsertChartField msoChartFieldFormula, "='Risiko-Log'!$B$5", 1

    ActiveChart.FullSeriesCollection(1).Points(5).DataLabel.Select
      ActiveChart.SeriesCollection(1).DataLabels(5).Format.TextFrame2.TextRange. _
        Characters.Text = ""
      ActiveChart.SeriesCollection(1).DataLabels(5).Format.TextFrame2.TextRange. _
        InsertChartField msoChartFieldFormula, "='Risiko-Log'!$B$6", 1

End Sub

Obviously, this is not very effective, so I'd like to recreate the approach in a for-Loop, picking each referenced cell using the i counter (Points and DataLabels would use i and the index or cell specification would use i+1).

I've been trying a few different approaches for a while but can't seem to get the syntax right. Does anyone have the idea that'll help me get through this and regain my motivation? Any help is appreciated! Here's where I'm at right now:

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

    Dim LastRow As Integer
    LastRow = Range("'Risiko-Log'!A" & Rows.Count).End(xlUp).Row


    For i = 1 To LastRow
    ActiveSheet.ChartObjects("Risikomatrix").Activate
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.FullSeriesCollection(1).Points(i).DataLabel.Select
      ActiveChart.SeriesCollection(1).DataLabels(i).Format.TextFrame2.TextRange. _
        Characters.Text = ""
      ActiveChart.SeriesCollection(1).DataLabels(i).Format.TextFrame2.TextRange. _
        InsertChartField msoChartFieldFormula, "'Risiko-Log'!.Cells(i + 1, 2)", 1
    Next i

End Sub

score 0 · Answer 1 · answered Sep 10 '18 at 10:05

0

in

 ActiveChart.SeriesCollection(1).DataLabels(i).Format.TextFrame2.TextRange. _
    InsertChartField msoChartFieldFormula, "'Risiko-Log'!.Cells(i + 1, 2)", 1

You enclose the i+1 in speech marks so it's treated as a string. You need it to be a calculation so it needs to be outside the speech marks. Use

ActiveChart.SeriesCollection(1).DataLabels(i).Format.TextFrame2.TextRange. _
    InsertChartField msoChartFieldFormula, "'Risiko-Log'!.Cells(" & i + 1 & ", 2)", 1

answered Sep 10 '18 at 10:05

Harassed Dad

4,669
1
10
12

Hey there! Thanks for having a look. That makes sense to me... I replaced the code snippet but unfortunately still get an "The specified parameter has an invalid value." error message for the same rows. Any idea where I went wrong? – T. Buc Sep 10 '18 at 10:16
DataLabels(i) - in VBA most indexes start at 0 but row numbers start at 1 so you need DataLabels(i-1) I think - at the end of your loop you try to access a non-existent index number – Harassed Dad Sep 10 '18 at 11:03

score 0 · Accepted Answer · answered Sep 12 '18 at 10:26

Continued to struggle with the For-Loop but just managed to solve all issues using "While".

Sub Rename_scat()
'
' Rename_scat Macro
'
'
    Dim i As Integer
    i = 2
    While IsEmpty("'Risiko-Log'!.Cells(" & i & ",1).Value")
        ActiveSheet.ChartObjects("Risikomatrix").Activate
        ActiveChart.FullSeriesCollection(1).DataLabels.Select
        ActiveChart.FullSeriesCollection(1).Points(i - 1).DataLabel.Select
        ActiveChart.SeriesCollection(1).DataLabels(i - 1).Format.TextFrame2.TextRange. _
            Characters.Text = ""
        ActiveChart.SeriesCollection(1).DataLabels(i - 1).Format.TextFrame2.TextRange. _
            InsertChartField msoChartFieldFormula, "'Risiko-Log'!.Cells(" & i & ", 2)", 1
        i = i + 1
    Wend

End Sub

Still skips the first entry of the risk log though (not just the header line) but even when I change the start value of i, it just doesn't work out right. Hiding that trouble-row had to do the trick in the end :D

Thanks for your help and input @Harassed Dad!

VBA for loop using the counter integer to retrieve content from cells

2 Answers2