Need help with creating a Range Name syntax

[Paul]

This lets a user select which columns to sort by:
A sort range is predefined.

Row 1 is blank

Now the user selects the columns to sort by:
Within row 1, the user types a "1" in the column that s/he wants to sort by,
a "2" in the second column to sort by, etc.

My code is supposed to scan across row 1, locate the values "1" and "2", and
plug them into the Sort Range value by creating Range names "ONE" and "TWO"
at the cells where the "1" and "2" were found.

But it crashes because I can't get the naming syntax right. Help is
appreciated. That "Refers to R1C1" type syntax totally confuses me.

Here's the code:

Sub CREATE_SORT()
Range("A1").Select

ActiveWorkbook.Names("ONE").Delete
ActiveWorkbook.Names("TWO").Delete

For n = 1 To 50 'scan across row 1, looking for "1" or "2"
Selection.Offset(0, 1).Select

'this is where I cant figure out how to name the selected cell, which
changes as the user does different sorts:

If Selection.Value = 1 Then ActiveWorkbook.Names.Add Name:="ONE" Refers to
XXXXX IT SHOULD BE THE CURRENT CELL
If Selection.Value = 2 Then ActiveWorkbook.Names.Add Name:="TWO" Refers
to ZZZZZ
Next n

Range("A3:Z400").Select
Selection.Sort Key1:=Range("ONE"), Order1:=xlAscending,
Key2:=Range("TWO") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom

End Sub

TIA
Paul

 

[Tom Ogilvy]

Your making it too hard:

Sub CREATE_SORT()
ActiveWorkbook.Names("ONE").Delete
ActiveWorkbook.Names("TWO").Delete

For n = 1 To 50 'scan across row 1, looking for "1" or "2"
If cells(1,n).Value = 1 Then
cells(1,n).Name = "One"
ElseIf cells(1,n).Value = 2 Then
cells(1,n).Name = "Two"
End if
Next n

Range("A3:Z400").Sort _
Key1:=Range("ONE"), _
Order1:=xlAscending, _
Key2:=Range("TWO"), _
Order2:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

End Sub

 

[Steven]

This is what I use to create a range name:

ActiveWorkbook.Names.Add Name:="One", RefersTo:="=" & Selection.Address

Steven