Using a variable value to select a range when creating a chart

[DeucesAx]

Hello,
vba newbie here. I am trying to write a macro which will create multiple charts for me. I have two tables below each other with the same "x axis" values in column A. Columns B to I contain values I want to plot as different series in multiple charts. So chart one plots ("B7:B21") and ("B24:B48") as series, chart 2 plots ("c7:c21") and ("c24:c48") etc
I successfully wrote a macro to create and label the chart. Now I want to create a loop so that all charts are created. In a first step I want to replace "("B7:B21")" with a variable column letter.
I tried replacing:

ActiveChart.SetSourceData Source:=Range("B7:B21")

with

Dim col As String
col = B
[...]
ActiveChart.SetSourceData Source:=Range(col & "7:" col & "21")

This did not give me an error, but the selected range was all wrong, Excel actually created 16 series or so.

All help is appriciated.

[Domenic]

Try...

Dim col As String

col = "B"

ActiveChart.SetSourceData Source:=Range(col & "7:" & col & "21")

Hope this helps!

[SamT]

You're missing an ampersand after the "7:" string.

You can also use

Col = 2
For Cht = 1 to Charts.Count - 1 Step 2
Charts(cht).SetSourceData Source:=Range(Cells(7, Col), Cells(21, Col))
Charts(Cht + 1).SetSourceData Source:=Range(Cells24, Col), Cells(48, Col))
Col = Col + 1
Next Cht

There is no guarantee that Excel has the Chart(s) in the Charts Collection in the order you want.

Dim MyChartsInOrder As Collection
'Add Charts in the order desired, Column B pair, Column C pair. Colum D pair, etc
With MyChartsInOrder
.Add 'first chart that uses Column B
.Add 'next chart. that uses "B"
'Etc
End With

[DeucesAx]

Thanks to both of you. I actually had the ampersand, not sure what happend. I did not however have the "" when I assigned B to the variable col. Works perfect now.
Never heard of charts collection, and do not quite understand your instructions. I hope google will be of help.

edit: For other potential readers: When using

ActiveChart.SetSourceData Source:=Range(Cells(7, col), Cells(21, col))

with col as an integer, "2" is needed instead of 2 when assigning col.

[Domenic]

Have a look at Sam's post where he provides you with an example. You'll notice that he assigns the number 2 to the variable Col, not the string "2". Also, you'll need to declare Col as Integer, not String.

[DeucesAx]

You are right. I swear to god, I had col as an integer and still did not work. I guess I am just to tired. My apologies.

[Domenic]

No problem, these things happen. :-)

Cheers!

[DeucesAx]

Thanks for the patience. I need to remember what stupid mistakes beginners make when I teach my wife German, which is my native language.

I already got the loop to work. This is very satisfying, even so I would have been faster doing it by hand
Could any of you recommend a good book so I get the basics right? I do know some c programming, so I am used to creating loops and so forth, but all excel specific commands are completely foreign to me.

[SamT]

Microsoft Visual Basic for Applications is the same language across all MS Office programs. It is not an OOP Language.

Each MS Office program is an object oriented program and has an Object model.

I have attached a spreadsheet version of the Excel 97 Object model. Each used cell in the spreadsheet has a comment that explains the contents of the cell.

Unlike the actual model. I place collections at the top of the hierarchies, since, IMO. it is more intuitive, although not technically correct. All cells are color coded as to Object, Collection, Method, Property and Event. I colored Properties that return Objects as Objects, since in code one can't tell the difference. IOW, although "Range" is a property of a Worksheet Object, writing Sheets("Sheet1").Range("A1"), means that you are dealing with the Range "A1" Object.

You will want to Edit the Comments to enlarge their Text Frame Objects so you can read the whole text.

[Domenic]

I would also recommend a book by John Walkenbach called "Excel Power Programming with VBA"...