Defining Dynamic Named Ranges with VBA

[mouzi4us]

Hi Friends

I have a Problem in VBA and looking for solution for my case .
I want to insert Dynamic Spark lines in my excel, so I have to define Dynamic Range for that and assign it to my spark lines, I did it with Offset and Count function . My problem is that there are so many Rows ( Abou 200 Rows ) that I should define name ranges for that .
So I recorded a Macro of defining Name and used a For loop to create Name Ranges . my Problem is that it refers to RC Ranges that wont work in excel .
I insert 3 pics to show my problem.

33.jpg111.jpg222.jpg

I would be so thankful if u guys help me on that.

[Jan Karel Pieterse]

Why are you defining dynamic names for the sparklines?

[mouzi4us]

Why are you defining dynamic names for the sparklines?

Becuz , my data will be updated daily and my range should be dynamic so that i can have it in my spark line .

[p45cal]

Weird.
Nevertheless, try:
"=OFFSET(Sheet1!R" & i & "C4,0,0,COUNT(Sheet1!R2C4:R2C9))"

[mouzi4us]

Weird.
Nevertheless, try:
"=OFFSET(Sheet1!R" & i & "C4,0,0,COUNT(Sheet1!R2C4:R2C9))"

It gives me syntax error

[p45cal]

'It' doesn't, but:
Name:=x
might (unless you have a variable x containing a valid name as a string).
Change it to:
Name:="x"

I can't tell if there's anything in cells C5 and C6 from your pictures.

[mouzi4us]

Weird.
Nevertheless, try:
"=OFFSET(Sheet1!R" & i & "C4,0,0,COUNT(Sheet1!R2C4:R2C9))"

Tnx So much Buddy, Worked Perfect , My bad for wrong copy and paste !
U r pro Buddy