Using Activecell.Address when defining name

[Hardy]

Hi,

When using
ActiveWorkbook.Names.Add Name:=sNameString,
RefersTo:=ActiveCell.AddressLocal

To put the name stored in sNameString to the active cell, it does not
work because ActiveCell.Addreslocal returns "$B$2" rather than $B$2.
What is the easiest way around this?

Thanks

 

[Norman Jones]

Hi Hardy,

Try:

ActiveWorkbook.Names.Add Name:=sNameString, _
RefersTo:=ActiveCell

 

[Tom Ogilvy]

Use Address instead of Addresslocal

ActiveWorkbook.Names.Add Name:=sNameString, _
RefersTo:="=" & ActiveCell.Address

VBA/Excel should make the necessary adjustments.

or

ActiveCell.Name = sNameString

 

[Tom Ogilvy]

the first should be:

ActiveWorkbook.Names.Add Name:=sNameString, _
RefersTo:="=" & ActiveCell.Address(external:=True)

--
Regards,
Tom Ogilvy

Use Address instead of Addresslocal

ActiveWorkbook.Names.Add Name:=sNameString, _
RefersTo:="=" & ActiveCell.Address

VBA/Excel should make the necessary adjustments.

or

ActiveCell.Name = sNameString

 

[Hardy]

Thanks Norma and Tom!
Both work fin