# Using R1C1 to reference data sources in VBA (Excel 2007 Macros)



## livejc

Hello I am currently working on a macro which builds a chart from a flexible range of data. I have included the entire code at the bottom of this post, but the line of code I am having particular difficulty with is as follows:

ActiveSheet.Shapes.AddChart.Select
ActiveSheet.ChartObjects(1).Name = "Chart1"
ActiveChart.SetSourceData Source:=Range("R1C1, R" & iRow & "C" & iColumn)

iRow and iColumn are integers that were given values using an increment loop earlier in the sub

When this part of the macro runs, I get an error on the following line:

ActiveChart.SetSourceData Source:=Range(R1C1, R5C5)

The error says:

" Run-time error '1004':
Method 'Range' of object '_Global' failed. "

The reason I need to use the R1C1 format is because the variable I am using for the value of the column - is an integer. The full code below will demonstrate why a string cannot be used...

I would really appriciate help on this, I have been trying for hours to solve this problem !!

Sub Create_Predictive()

Dim iRow As Long
Dim iColumn As Long

iRow = 2 ' row 1 will be empty, so we begin at row 2
While Not IsEmpty(Worksheets("Data").Cells(iRow, "A"))
iRow = iRow + 1
Wend

iColumn = 2 ' column 1 will be empty, so we begin at column 2
While Not IsEmpty(Worksheets("Data").Cells(2, iColumn))
iColumn = iColumn + 1
Wend

ActiveSheet.Shapes.AddChart.Select
ActiveSheet.ChartObjects(1).Name = "Chart"
ActiveChart.SetSourceData Source:=Range("R1C1, R" & iRow & "C" & iColumn)
ActiveChart.ChartType = xlLineMarkers

End Sub


----------



## livejc

I have managed to solve this problem myself and I will post the solution below for other people who might be having this problem.

The most efficient way of referencing a range using the R1C1 format is as follows

ActiveSheet.Shapes.AddChart.Select
ActiveSheet.ChartObjects(1).Name = "Charter"
ActiveChart.SetSourceData Source:=Range(Cells(1, 1), Cells(5, 5))

Range(Cells(1, 1), Cells(5, 5)) is the same as saying Range(A1:E5) but using numbers which is extremely handy when your column value is an integer


----------

