MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

using macro to create dynamic named ranges

  Asked By: Dukker    Date: Feb 08    Category: MS Office    Views: 1902

I use the following code to create dynamic defined named ranges but when I get
to the Define Names Dialog to check the references I notice they are enclosed
in quotation marks and these make them not to work. I attach a sample of my
workbook for you to understand what I mean.
Sub definenames()
Dim d As String
For n = 1 To 9
Cells(1, n).Select
b = Selection.Offset(1, 0).Address
c = Selection.Offset(99, 0).Address
a = Cells(1, n).Value
a = Replace(a, " ", "_")
d = "OFFSET('defined names'!" & b & ",0,0,COUNTA('defined names'!" & b & ":" & c
& ")" & ",1)"
ActiveWorkbook.Names.Add Name:=a, RefersToR1C1:=d
End Sub
Instead of finding the referece in the Define Names Dialog as follows: =
OFFSET('defined names'!" & b & ",0,0,COUNTA('defined names'!" & b & ":" & c &
")" & ",1) it will be enclosed in quotation marks and will be found as:
="OFFSET('defined names'!" & b & ",0,0,COUNTA('defined names'!" & b & ":" & c &
")" & ",1)". These quotation marks must be manually removed before the defined
name will work.
Kindly suggest what to do to the code to make these quotation marks not to
appear in the defined name references. Thank you

There is none like unto the God of Jeshurun who rides through the heavens to
my help and in Majesty through the skies
The eternal God is my dwelling place and underneath me are his everlasting



5 Answers Found

Answer #1    Answered By: Farah Khan     Answered On: Feb 08

Change your code  to...

d = "=OFFSET...

Without the second equal sign Excel interprets your entry as a simple
text entry.

Answer #2    Answered By: Eline Bakker     Answered On: Feb 08

Thanks for your reply. However when I tried your suggestion I still got the same
old results. You may wish to test this result in the excel sample  I sent. Will
be glad if you can offer more suggestions.

Answer #3    Answered By: Harriet Hughes     Answered On: Feb 08

see if this helps:

in the VB line:

ActiveWorkbook.Names.Add Name:=a, RefersToR1C1:=d

try this:

ActiveWorkbook.Names.Add Name:=a, RefersTo:= _
"=" & d

this "space underline space", at the end of the vb line, forces the function
'build' to continue 'wrap' to the next line!
Not sure why this worked!

Answer #4    Answered By: Blandina Garcia     Answered On: Feb 08

Thanks a million. The suggestion worked like magic. I am very very grateful.
Once again thanks

Answer #5    Answered By: Addison Campbell     Answered On: Feb 08

I discovered what really works is the "RefersTo:" instead of RefersToR1C1 and
not the line break. It works without the line break when the first alternative
is used. Once again thanks for your contribution.

Didn't find what you were looking for? Find more on using macro to create dynamic named ranges Or get search suggestion and latest updates.