Defining Name refering to dynamic range VBA?

[magicbob007]

OK VBA gurus, I have what I believe to be a simple problem here. I am designing a userform that creates dynamic named ranges for Data Validation. This takes the guess work out of it for my end users who are not savvy enough to name a dynamic range on their own.

My problem is that when I get to line 53 of the code and go to assign the "refersto:=" I always wind up with quotation marks in my final refers to formula in the name manager. Consequently my equations are referenced right, but wont work because of the inclusion of extra quotes.

the code as is produces:

="offset(Data!$C$27,0,0,countA(Data!$C$27:$C$1000)-0,1)"

instead of:

=offset(Data!$C$27,0,0,countA(Data!$C$27:$C$1000)-0,1)

This has been frustrating the pants off me for the whole day and everything I have tried doesn't seem to fix the issue. Can you guys tell me how to get this right??

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

You're missing the =

i.e.
NewRng = "=offset(Data!" & TopRef & ",0,0,countA(Data!" & TopRef & ":$" & CurColInt & "$1000)-0,1)"

[magicbob007]

I had it in there before, but it wasn't working. There must have been something else wrong also because with the = it works perfect now. Thanks. I knew it would be something simple I was just over looking.