PDA

View Full Version : Defining Dynamic Named Ranges with VBA



mouzi4us
02-01-2016, 12:22 AM
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.

153261532715328

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

Jan Karel Pieterse
02-01-2016, 12:49 AM
Why are you defining dynamic names for the sparklines?

mouzi4us
02-01-2016, 04:22 AM
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
02-01-2016, 04:59 AM
Weird.
Nevertheless, try:
"=OFFSET(Sheet1!R" & i & "C4,0,0,COUNT(Sheet1!R2C4:R2C9))"

mouzi4us
02-01-2016, 05:38 AM
Weird.
Nevertheless, try:
"=OFFSET(Sheet1!R" & i & "C4,0,0,COUNT(Sheet1!R2C4:R2C9))"
It gives me syntax error :(

p45cal
02-01-2016, 05:54 AM
'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
02-01-2016, 06:41 AM
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