Naming a range with VBA...
 

This won't work (either...)

sAddr dosn't get accepted as a cell address...


sAddr = ActiveCell.Address
ActiveWorkbook.Names.Add Name:="test"
RefersToR1C1:="=Lister!R1C7:sAddr

--
Message posted from http://www.ExcelForum.com

Naming a range with VBA...
 

Dim sAddr as String
sAddr = ActiveCell.Address(1,1,xlR1C1)
ActiveWorkbook.Names.Add Name:="test", _
RefersToR1C1:="=Lister!R1C7:" & sAddr

--
Regards,
Tom Ogilvy

"Steff_DK " wrote in message
...
This won't work (either...)

sAddr dosn't get accepted as a cell address...


sAddr = ActiveCell.Address
ActiveWorkbook.Names.Add Name:="test",
RefersToR1C1:="=Lister!R1C7:sAddr"


---
Message posted from http://www.ExcelForum.com/

Naming a range with VBA...
 

Stef here is a reply i got many months ago from an MVP on this forum.
The code was NOT written by me.


Try something like the following:

If being done from VBA in Excel

Workbooks("Book1.xls").Names.Add Name:="test",
RefersTo:="=sheet1!$a$1:$c$20"

If being done from VBA in any application other than Excel

<ApplicationObject.Workbooks("Book1.xls").Names.A dd Name:="test",
RefersTo:="=sheet1!$a$1:$c$20"

Where <ApplicationObject is the variable that refers to the Excel
Application instance

Easier would be:

rnum = 21
With xlwksht2
.Range("A3:A" & rnum).Name = Name1
End With

Hope this is of help

--
Message posted from http://www.ExcelForum.com

Naming a range with VBA...
 

Dim sNewname As String
sNewname = ""
Do While sNewname = ""
sNewname = InputBox("Enter initials for new person:")
If sNewname = "" Then iSvar = MsgBox("Do you want to quit?", vbYesNo)
If iSvar = vbYes Then
Exit Do
End If

Loop

Range("C2").CurrentRegion.Select

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Value = sNewname
Dim sAddr As String
sAddr = ActiveCell.Address

MsgBox sAddr
ActiveWorkbook.Names("ansvar").Delete
ActiveWorkbook.Names.Add Name:="ansvar", RefersTo:="=Lister!$c$1:"
sAddr

Range("C2").CurrentRegion.Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNorma

--
Message posted from http://www.ExcelForum.com

Naming a range with VBA...
 

sAddr dosn't get accepted as a cell address...
sAddr = ActiveCell.Address
ActiveWorkbook.Names.Add Name:="test",
RefersToR1C1:="=Lister!R1C7:sAddr"

Try:

RefersTo:="=Lister!G1:" & sAddr

Cheers, Pete.

Naming a range with VBA...
 

Dim sNewname As String
sNewname = ""
Do While sNewname = ""
sNewname = InputBox("Enter initials for new person:")
If sNewname = "" Then iSvar = MsgBox("Do you want to quit?", vbYesNo)
If iSvar = vbYes Then
Exit Do
End If

Loop

Range("C2").CurrentRegion.Select

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Value = sNewname
Dim sAddr As String
sAddr = ActiveCell.Address

MsgBox sAddr
ActiveWorkbook.Names("ansvar").Delete
ActiveWorkbook.Names.Add Name:="ansvar", RefersTo:="=Lister!$c$1:"
sAddr

Range("C2").CurrentRegion.Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNorma

--
Message posted from http://www.ExcelForum.com