Macro Copy Active Cell to Range Name, loop

[zigstick]

Hi, I need to create a macro to run the following sequence:

1. Copy the Active Cell (say it's A1, "mark")
2. Move up to the Name Box at top left corner to name the cell range
(Is this correct for that location? -- ActiveWorkbook.Names.Add
Name:="servera", RefersToR1C1:="=Sheet1!R1C1"
3. Paste what was copied from the active cell and Enter, to name the
cell range.
4. Move to another cell, say A3 ("david) and restart the process,
copying the active cell (which will hold a different name)

Please use example below:
A B C D E
1 mark
2 john
3 david
4 larry
5 michael

Thanks!!!!

 

[Ed]

Sub NameMe()

Dim strName As String
Dim strAddr As String

strName = ActiveCell.Value
strAddr = ActiveCell.Address(, , xlR1C1)

ActiveWorkbook.Names.Add _
Name:=strName, _
RefersToR1C1:="=Sheet1!" & strAddr

End Sub

You can use Offset to move from cell to cell, and loop as required.

HTH
Ed

 

[zigstick]

Thanks so much Ed.

One more question. Say I wanted to
1. copy the data from the active cell
2. select from the active cell to 3 rows down
3. move to the name box and paste the copied data, thus naming the
range what was in the initial active.cell

Thanks!!!!!

 

[Tom Ogilvy]

Assume the activecell is Cell B10 and it contains the string "ABCD". You
want to Name B13 "ABCD"

ActiveCell.Offset(3,0).Name = ActiveCell.Value

 

[zigstick]

Thanks, but actually trying to capture the entire range of say, Active
cell of b10 to B13 (B10:B13), then move to the name field and type only
the data copied from the initial active cell. This would create a
named range, with the name being whatever is in the first cell ... in
this case, b10.

Thanks!!

 

[Ed]

If you want to name a cell only, Tom's code is much better. If you want to
name a range of selected cells, I have this to offer:
Sub NameMe()

Dim strName As String
Dim rngMine As Range

'Get text from cell for range name
strName = ActiveCell.Value

'Selects from ActiveCell to 3 cells down in same column
'If you do not always want to do this,
'set up an If statement
Range(ActiveCell, ActiveCell.Offset(3, 0)).Select

'Sets a rnage to the selection and names it
Set rngMine = Selection
ActiveWorkbook.Names.Add _
Name:=strName, _
RefersTo:="=Sheet1!" & rngMine.Address

End Sub

A couple of other items:
-- Tom's code is ALWAYS much better than mine! I offerred this only because
the way I read your post you wanted a range of cells, and Tom's looked like
he only named a single cell. If Tom offers an improvement on this, go with
that one.
-- You do not "move to the name box" as you would move from cell to cell.
You access the Names collection of the Workbook and Add a name.
-- You will find a lot of answers faster than waiting for posts on the
newsgroup by working through the Help files in the VBA editor. You can type
a key word or two describing what you want to do ( such as "name cells") in
the Answer Wizard, and it will give you a list of topics. It's often a lot
to wade through, but you'll get farther than you will by sitting and
waiting. Another highly recommended way is a Google search of the
newsgroup. Ron DeBruin has created an excellent Add-In that I use all the
time - http://www.rondebruin.nl/Google.htm.

Ed
(PS - I'm offline now for about 18 hours, so I won't be able to answer
anything else for a while.)

 

[Tom Ogilvy]

ActiveCell.Resize(3,1).name = ActiveCell.Value