Dynamic Values in Excel Chart 1

[Mantle51]

Hello,
I'm trying to assign variables as dynamic coordinates in ActiveChart object. However, I keep 'Application Defined or Object Defined Error' Typing "F2:F30" does work. How can I keep this dynamic?

Set Rng1 = Range("F" & i, Range("F" & i + 77))
Set Rng2 = Range("X" & i, "X" & (i + 77))
Set Rng3 = Range(Rng1, Rng2)


Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("5_Min").Range ("Rng1", "Rng2"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = Sheets("5_Min").Range("Rng2")

 

[Andrzejek]

If:
Range"F2:F30" works OK, then try to replace it with:

i = 2
y = 30
Range"F" & i & ":F" & y

and work with this to start.

Have fun.

---- Andy

 

[Mantle51]

I've tried every conceivable variation like the one above and it does not work.

 

[SkipVought]

Hi,

You do not really need code to do this.

How can I rename a table as it changes size faq68-1331

So if your range name is WeekOf, then in your Chart/Source/Series Tab - WhateverSheetName!WeekOf for each Value/Caregory.

Skip,

_[red][/red]

 

[combo]

You mix objects, addresses and strings:
- the first three lines set Rng1 and Rng2 as ranges in active sheet, Rng3 as a range Fi:Xi+77,
- in two last lines you yse hard strings "Rng1" and "Rng2", that have nothing to do with initially set ranges.

If you want to work with strings, use:
Rng1 = "F" & i &":F" & i + 77
...
Rng3 = "F" & i &":X" & i + 77
(if necessary, Trim it) and next:
ActiveChart.SetSourceData Source:=Sheets("5_Min").Range (Rng3), PlotBy:=xlRows

Or when using ranges:
Set Rng3=Sheets("5_Min").Range("F" & i &":X" & i + 77)
...
ActiveChart.SetSourceData Source:=Rng3, PlotBy:=xlRows

combo

 

[Mantle51]

Thank you for the clarification, Combo!