I have the following table of SKU and Volume (E4:F10)
| SKU | Volume |
|---|---|
| a | 10 |
| b | 20 |
| c | 30 |
| d | 40 |
| e | 50 |
| f | 60 |
I need to make a graph out of this data, with the number of rows determined by a number in E2. For example, if the number in E2 is 4, my graph should only have the SKUs up to c.
My code is as follows:
Sub selectVar()
Dim x As Integer
x = Range("f1").Value
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("E4" & ":" & "F" & x)
End Sub
But I get an error:
Run-time error '1004'
Method 'Range' of object'_Global' failed
Why is this error occurring?
I recreated the data you showed and ran your code. I got the same error. After clicking the Debug button when the error was thrown, I was directed to the line ActiveChart.SetSourceData Source:=Range("E4" & ":" & "F" & x). This line is the first place to check. I can see that the variable x is used, so what is its value? 0. The variable x gets its value from the cell F1 but there is no data in that cell, so it defaults to the value 0. So your code is trying to set the source range for the chart to be E4:F0 which is not possible because there is no cell F0 so the error is thrown.
Solution
You mentioned that cell E2 is supposed to contain the number of rows that your chart will plot, so the first thing is to change the source of x from cell F1 to cell E2. Assuming that E2 has the value of 4 like in your example, the range your code will try to use is then E4:F4 which is not the desired output. You want 4 rows of data, not the cell on row 4. Try this for the line to set the source data instead: ActiveChart.SetSourceData Source:=Range("E4" & ":" & "F" & x + 4) The +4 is added to the value of x because the first row of your range/table is in row 4. Note that the 4 rows of data here include the title row which means that only data rows 5, 6, and 7 will be plotted in this example.