VBA to create Named References to Spilled Ranges

Hi all.

I wrote a sub to automate creating Named Ranges, but I want the Named Range to dynamically refer to the spilled range.

For example if I have a name in A1, and a spilled range in 'Sheet 1'!A$3$ that spills A3:A12, I would want the Name Range to refer to A$1$#".
I can achieve this by manually typing into the Name Manager, but that is very tedious.

I can get use Range("A1#") to get a reference to the correct spilled range in vba.
But when I set the Named Range to refer to this Range object, the result is merely a named range statically referring to A3:A12.
ie

Please Login or Register  to view this content.

What am I doing wrong?

Here is my code in including an example calling sub

Please Login or Register  to view this content.

Sorry, I thought I posted a reply yesterday.
This worked perfectly. THANK YOU

(quote with only the most import code bits)

Originally Posted by jindon

Try to change like this and see how it goes.

Please Login or Register  to view this content.

I had previously tried setting the string address in "RefersTo", before trying with range objects.
The magic sauce was adding the "=". I had missed that.

Thanks again!