CODEWITHSUNDEEP
excelvbaloopsrange
rzmudatr
rzmudatr

Reputation: 11

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

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

    Sub Macro()
    
        For i = 2 To 10
        Windows("Book1").Activate
        ActiveSheet.ChartObjects("Chart 1").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)" _

Upvotes: 0

Views: 267

Answers (3)

rzmudatr
rzmudatr

Reputation: 11

Okay, I found a workaround to my problem with selecting a value range differently. Code below worked:

For i = 2 To 10
newName = "=Sheet1!$A$" + CStr(i)
newValues = "=Sheet1!$B$" + CStr(i) + ":$F$" + CStr(i)
ActiveChart.FullSeriesCollection(1).Name = newName
ActiveChart.FullSeriesCollection(1).Values = newValues
Next i

Upvotes: 1

vovakirdan
vovakirdan

Reputation: 365

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)

Upvotes: 0

Rory
Rory

Reputation: 34075

You have some unneeded quotes:

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

Upvotes: 0

Related Questions