Dynamic chart issue

[pkdash83]

Hi Guys,

I've a chart built out of sales data. There are 3 columns Selection, Category and Sales for year 2001 to 2014.

I need the chart populated dynamically based on the selection column.

I'm using the below code.

Please let me know what I'm doing wrong here. I'm pretty new to VBA and excuse me for any mistakes in syntax.


Sub get_chart_data()

Dim i As Integer
i = 20
For Each i In Sales.Range("B20:B29")
If Sales.Range("B" & i) = "x" Then
Charts("chart1").SetSourceData Source:=Sheets("Sales").Range("C"&i:"Q"&i)
Next i

End Sub

 

[Andrew Poulsom]

Syntactically it should be:

Range("C" & i & ":Q" & i)

 

[pkdash83]

Hi Andrew thanks a lot for the reply.
I've changed the code as below-

Sub get_chart_data()

Dim i As Range
For Each i In Sales.Range("B20:B29").Cells
If Sales.Range("B" & i).Value = "x" Then
Charts("chart1").SetSourceData Source:=Sheets("Sales").Range("D" & i & ":Q" & i)
End If
Exit For

Next i

End Sub

But I'm getting another error - Run time error 1004
: Application Defined or Object Defined Error

It seems to be generic one but I din't find a solution.

 

[Andrew Poulsom]

The variable i is a Range object not a number.