Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro using relative cell reference to set name for range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro using relative cell reference to set name for range
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 "Sandy" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro using relative cell reference to set name for range
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro using relative cell reference to set name for range
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- "Norman Jones" wrote: 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 "Sandy" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro using relative cell reference to set name for range
This worked perfectly - thank you so much!
"ND Pard" wrote: 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" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro relative cell reference | Excel Worksheet Functions | |||
Using Relative Reference Function to apply to Range | Excel Programming | |||
Macro to capture cell value then use it for a relative range selec | Excel Programming | |||
How do I set up a macro using relative cell reference? | Excel Programming | |||
Start relative reference macro from specific cell | Excel Programming |