I have the following table of
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.
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.
Answered By – TehDrunkSailor
Answer Checked By – David Goodson (AngularFixing Volunteer)