passing loop variable in vba to ActiveChart.SetSourceData Source:=Range[...]-CodePudding

I've problems passing variable i into a loop that selects chart source values.

    Sub Macro()
    
        For i = 2 To 10
        Windows("Book2").Activate
        ActiveSheet.ChartObjects("Chart 2").Activate
        ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$F$1,Sheet1!$A$2:$F$2" _
            )
    
        Next i
    
    End Sub

I want to change the series value Sheet1!$A$2:$F$2" and put i value there -> Sheet1!$A$i:$F$i"

I tried different approaches, but they didn't work
How to loop Ranges in VBA?
Excel vba Charting, editting the range

        ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$F$1,("Sheet1!$A$" & i & ":$F$" & i)" _

CodePudding user response：

You have some unneeded quotes:

ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$F$1,Sheet1!$A$" & i & ":$F$" & i)

CodePudding user response：

Don't remember to assign i to str value

ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$F$1,Sheet1!$A$"   cstr(i)   ":$F$"   cstr(i)

And the better way is createa variable

distinctrange = "Sheet1!$A$1:$F$1,Sheet1!$A$"   cstr(i)   ":$F$"   cstr(i)
ActiveChart.SetSourceData Source:=Range(distinctrange)