range names

Posted by Marvin on May 14, 2001 10:08 PM

I want to set up a loop that will run x times and get the range names it needs to copy from a list in my worksheet. I have no problem capturing the text for the range names, example copyName=ActiveCell.Formula,but I can't seem to pass this text to the Range object, example ActiveSheet.Range(copyName). This syntax always return an object definition error. I have to run this loop over 100 times so any help would be greatly appreciated.

Posted by Dave Hawley on May 14, 2001 10:28 PM

Hi Marvin

I'm no too clear on what you are tying to do, but give this code of mine a run, it will list all Named ranges that are within the Workbook.

Sub ListAllNames()
'Wriiten by OzGrid Business Applications
'www.ozgrid.com

Dim nNames As Name
Dim i As Integer

i = 1
For Each nNames In ActiveWorkbook.Names
Cells(i, 1) = nNames.Name
Cells(i, 2) = nNames.Parent.Name
Cells(i, 3) = nNames.RefersToRange.Address
i = 1 + 1
Next

End Sub

If this is not what you wanted, could you post you code here and I'll fix it up for you.

Dave

OzGrid Business Applications

Posted by Ivan Moala on May 15, 2001 7:00 AM

Dave
Just a small error in above
i = 1 + 1 should be
i = i + 1
Dave works hard @ giving help :-) I'm sure he
meant the above.
Another way, BUT to get sheet reference names
as well;

Sub Listnames()
Range("A1").ListNames
Range("A:B").Columns.AutoFit
End Sub

But then as dave has said, I'm unsure what you want.

Ivan

Posted by Dave Hawley on May 16, 2001 6:27 AM

Cheers Ivan, That's the second time in the last week I done that!
I've gotta start using another Variable name.

Dave

OzGrid Business Applications