Macro using relative cell reference to set name for range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandy - 30 May 2008 14:21 GMT

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

Reply to this Message

Norman Jones - 30 May 2008 14:30 GMT

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

---
Regards.
Norman

> Hello -
>
[quoted text clipped - 16 lines]
> many thanks
> sandra

Reply to this Message

Sandy - 30 May 2008 15:09 GMT

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-

> Hi Sandra,
>
[quoted text clipped - 31 lines]
> > many thanks
> > sandra

Reply to this Message

ND Pard - 30 May 2008 14:55 GMT

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.

> Hello -
>
[quoted text clipped - 15 lines]
> many thanks
> sandra

Reply to this Message

Sandy - 30 May 2008 15:30 GMT

This worked perfectly - thank you so much!

> Try this:
>
[quoted text clipped - 30 lines]
> > many thanks
> > sandra

Reply to this Message