Naming a range with VBA...

S

Steff_DK

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
 
T

Tom Ogilvy

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

Jako

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.Add 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
 
S

Steff_DK

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
 
P

Pete McCosh

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

Try:

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

Cheers, Pete.
 
S

Steff_DK

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
 
Top