Insert named range problem
 

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)?

Insert named range problem
 

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)?

Insert named range problem
 

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)?

Insert named range problem
 

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"

Insert named range problem
 

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"

Insert named range problem
 

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.

Insert named range problem
 

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.

Insert named range problem
 

Hi Bob,

One of the 'advantages' of using a version of MS products that is almost US
is that internatiolisation issues are rarely a problem

FWIW, you might want to read http://www.oaltd.co.uk/ExcelProgRef/Ch22 for
some details of when internationalisation issues *are* a common problem.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Insert named range problem
 

I've read the book Stephen!

Bob


"Stephen Bullen" wrote in message
...
Hi Bob,

One of the 'advantages' of using a version of MS products that is almost
US
is that internatiolisation issues are rarely a problem

FWIW, you might want to read http://www.oaltd.co.uk/ExcelProgRef/Ch22 for
some details of when internationalisation issues *are* a common problem.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Insert named range problem
 

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"

Insert named range problem
 

Bob
that's Ron and me for example, aren't we <ebg




Bob Phillips wrote:
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.

Insert named range problem
 

Indeed it is!

Bob

"Frank Kabel" wrote in message
...
Bob
that's Ron and me for example, aren't we <ebg




Bob Phillips wrote:
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.