Macro using relative cell reference to set name for range

[Sandy]

Hello -

I have to define the name for a long list of 2-cell ranges. I need a macro
to do the following:

I place the cell in the first cell, then the steps for the macro would be as
follows:
( I am using these cells just for the example)

1 - start at B2 - copy the content of the cell (ctrl-C)
2 - Go to A2:A1 (move cursor 1 cell to the left and use SHIFT-UP ARROW to
select both cells)
3 - Go to the Name Box (at the top left corner) and Paste the content from
cell B2
4 - click ENTER to save the named range

Any help would be appreciated.
many thanks
sandra

 

[Norman Jones]

Hi Sandra,

Try turning turning macro recorder on and
perform the necessary operations manually.
This will provide you with code which may
be edited to afford more generic application.

If you experience problems in editing the
recorder code, post back with the
problematic code

 

[ND Pard]

Try this:

Sub Name_Range_for_Sandy()

'Name a range the value of the ActiveCell
'The range consists of two cells
'the cell to the Left
'and the cell above the cell to the left
Range(ActiveCell.Offset(-1, -1), ActiveCell.Offset(0, -1)).Name _
= ActiveCell.Value

End Sub

Good Luck.

 

[Sandy]

Hello Norman -

Here is the code from the macro:

Range("B2").Select
Selection.Copy
Range("A1:A2").Select
Range("A2").Activate
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="NameOfRange", RefersToR1C1:= _
"='Lookup Range'!R1C1:R2C1"
Range("B4").Select

The prblem is that the range always changes so I need it to be start at the
first cell, then move left 1 cell and (SHIFT) up 1cell - something like this:

Range("R2C2").Select
Selection.Copy
Range("RC-1:R-1C-1").Select etc ....

Also, the name of the range (Add Name:="NameOfRange")
needs to always change, based on what was copied and not be a fixed value.

Hope this clarifies the requirement a bit.

s-

 

[Sandy]

This worked perfectly - thank you so much!

Try this:

Sub Name_Range_for_Sandy()

'Name a range the value of the ActiveCell
'The range consists of two cells
'the cell to the Left
'and the cell above the cell to the left
Range(ActiveCell.Offset(-1, -1), ActiveCell.Offset(0, -1)).Name _
= ActiveCell.Value

End Sub

Good Luck.