I want to be able to insert a named range using an A1 reference based on the
activecell using VBA. Based on Help, I tried the following code:

With ActiveWorkbook
.Names.Add Name:="Hold1", RefersTo:=RefersToLocal
End With
It does insert a name, but the range it refers to is #N/A. Is there a way
to do this so that it actually refers to the cell (for example, =B45)?

Hi FinChase,

Try:

RefersTo := ActiveCell

Or for any other cell just use, e.g.:
RefersTo := [B45]

-Erik


--
This posting is provided "AS IS" with no warranties, and confers no rights.

"FinChase" wrote in message
...
I want to be able to insert a named range using an A1 reference based on
the
activecell using VBA. Based on Help, I tried the following code:

With ActiveWorkbook
.Names.Add Name:="Hold1", RefersTo:=RefersToLocal
End With
It does insert a name, but the range it refers to is #N/A. Is there a way
to do this so that it actually refers to the cell (for example, =B45)?

RefersToLocal is another property thatyiu could use rather than Refersto, if
the formula is in local language.

You need to rfefrence the range, but it can be shortcut with

Activecell.Name:="Hold1"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"FinChase" wrote in message
...
I want to be able to insert a named range using an A1 reference based on
the
activecell using VBA. Based on Help, I tried the following code:

With ActiveWorkbook
.Names.Add Name:="Hold1", RefersTo:=RefersToLocal
End With
It does insert a name, but the range it refers to is #N/A. Is there a way
to do this so that it actually refers to the cell (for example, =B45)?

it aint that simple...

ReferstoLocal argument of the Names.Add method
does NOT accept local formulas...

it accepts local FUNCTIONS but needs USenglish
separators and references.




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Bob Phillips wrote in message
:

RefersToLocal is another property thatyiu could use rather than
Refersto, if the formula is in local language.

You need to rfefrence the range, but it can be shortcut with

Activecell.Name:="Hold1"

I think you are missing the point, it is not a value to the RefersTo
property as the OP was using it, it was being used as an (undefined)
variable. He doesn't want RefersToLocal AFAICS, he got confused by the help.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"keepITcool" wrote in message
...
it aint that simple...

ReferstoLocal argument of the Names.Add method
does NOT accept local formulas...

it accepts local FUNCTIONS but needs USenglish
separators and references.




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Bob Phillips wrote in message
:

RefersToLocal is another property thatyiu could use rather than
Refersto, if the formula is in local language.

You need to rfefrence the range, but it can be shortcut with

Activecell.Name:="Hold1"

true

my comment was more a NOTE to you <g
stay away from the RefersTolocal/ReferstoR1c1Local
arguments of the names.add method.

they were programmed by a xenophobe.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Bob Phillips wrote in message
:

I think you are missing the point, it is not a value to the RefersTo
property as the OP was using it, it was being used as an (undefined)
variable. He doesn't want RefersToLocal AFAICS, he got confused by the
help.

Jurgen,

I can assure you that I do stay well away from it <vbg.

One of the 'advantages' of using a version of MS products that is almost US
is that internatiolisation issues are rarely a problem, and when they are I
use my continental colleagues to sort it for me :-).

Bob

"keepITcool" wrote in message
...
true

my comment was more a NOTE to you <g
stay away from the RefersTolocal/ReferstoR1c1Local
arguments of the names.add method.

they were programmed by a xenophobe.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Bob Phillips wrote in message
:

I think you are missing the point, it is not a value to the RefersTo
property as the OP was using it, it was being used as an (undefined)
variable. He doesn't want RefersToLocal AFAICS, he got confused by the
help.

Hi
I just would consider this porperty buggy and nearly useless :-)

--
Regards
Frank Kabel
Frankfurt, Germany


keepITcool wrote:
it aint that simple...

ReferstoLocal argument of the Names.Add method
does NOT accept local formulas...

it accepts local FUNCTIONS but needs USenglish
separators and references.




www.XLsupport.com | keepITcool chello nl | amsterdam

Bob Phillips wrote in message
:

RefersToLocal is another property thatyiu could use rather than
Refersto, if the formula is in local language.

You need to rfefrence the range, but it can be shortcut with

Activecell.Name:="Hold1"