View original (French)

VBA Syntax for Sheets.Range with Range Variable

Solved
Nicoroth Posted messages 55 Status Membre -  
lermite222 Posted messages 9042 Status Contributeur -
Hi,

I have a little Excel macro to create charts, but I'm stuck on one point, and I admit that after testing countless things, my nerves are starting to fray. Here's the situation:

I determine my coordinates in the following way:

Set Coord = Range(Cells(q, 15), Cells(q, 16))

to have:

ActiveChart.SetSourceData Source:=Coord

However, my Coord is fetching the range from the sheet where my chart is created, but I want it to pull that range from a different sheet. I've seen a lot of things on the forum, but I still can't find the solution...
I've tried something like:

Set Coord = ThisWorkbook.Sheets("Calcul").Range(Cells(q, 15), Cells(q, 16))

or

Set Coord = ThisWorkbook.Sheets("Calcul").Range(Cells(q, 15), Cells(q, 16)).Value

or even

Set Sheet = Sheets("Calcul")
Set Coord = Sheet.Range(Cells(q, 15), Cells(q, 16))

Or

Set Sheet = Sheets("Calcul")
Set Coord = Range(Cells(q, 15), Cells(q, 16))
with
ActiveChart.SetSourceData Source:=Sheet.Coord

But still no luck....

If anyone has the solution, they would save my nerves ;)

Thank you again, developer friends, for your availability.

Best regards

Configuration: Windows XP / Firefox 3.6.13

11 réponses

Réponse 1 / 11
lermite222 Posted messages 9042 Status Contributeur 1 199
 
Hello, 

ActiveChart.SetSourceData Source:= Sheets("Calcul").Range("A" & q & ":B" & q)


Not tested but give it a try.
See you later
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
It should take place on the forum so that everyone can participate or benefit.
1