Variable of source of data for chart In Excel With VBA

Question

I’m using Excel 2007 VBA to create a ScatterSmoothNoMarkers type chart in the same data sheet. My source data are depended on a number of text files opened. The Xvalue is fixed @ location A2: A200. And ID of columns of Series value will be changed.

If I opens 2files, my source data will be: Range(A2:A200, F2:G200). To open 3files, my source data will be: Range(A2:A200, H2:J200) And so on… So I need to replace those ID of columns by variables. But I got an error when I set the source of data. Here is my code:

Sub addChart()
Dim n as integer ‘files count and also the number of columns for chart1 
Dim intColStart, intColStop as integer  ‘number of columns for chart 1
intColStart = n*2+2  ‘this is a formula to find the ID of the start column of chart1
intColStop = n*3+1  ‘this is a formula to find the ID of the stop column of chart1
…..
With ActiveSheet.ChartObjects.Add _
         (Left:=100, Width:=375, Top:=75, Height:=225)
        .Chart.SetSourceData Source:=Sheets("Sheet1").Range("A2:A200, intColStart:intColStop ")  ‘’’’’PROBLEM RIGHT HERE‘’’’’’’
        .Chart.ChartType = xlXYScatterSmoothNoMarkers
……..
End With
End Sub 

Any help will be much appreciated.

Answer 1

You cannot reference variables within " marks. VBA doesn't know to convert them and so it thinks you are setting a Range including text which you are literally attempting to use

A2:A200, intColStart:intColStop 

which does not make any sense to VBA.

Why not change line

    .Chart.SetSourceData Source:=Sheets("Sheet1").Range("A2:A200, intColStart:intColStop ") 

to

    .Chart.SetSourceData Source:=Sheets("Sheet1").Range(Cells(2,2), Cells(200,intColStop))

---

If I'm misunderstanding your interest, I would suggest something where you manually create the Range String input - using something like the following:

dim rngStr as String
dim firstColStr as String
dim secondColStr as String
firstColStr = ColumnLetter(intColStart)
secondColStr = ColumnLetter(intColStop)
rngStr = "A2:A200," & firstColStr & ":2:" & secondColStr & "200"
...

.Chart.SetSourceData Source:=Sheets("Sheet1").Range(rngStr)

This answer defines the function ColumnLetter I am referencing above (which gets the letter from the index).