name cells with their adjuscent cells

[Lamb Chop]

How can I do a batch naming of a series of cells by the cells next to them.

e.g.

A1 = apple B1=10
A2= orange B2=12
A3= banana B3=5

I want to name B1 to apple, B2 to Orange and B3 to banana.

At the moment, I enter them manually. Are there any better way?

Thanks in advance.

 

[Dave]

Hi LC,
Try this small macro.
It will name cells in Column B according to info in Column A.
It will stop when it finds a blank cell in Cloumn A

Sub NameB()
Dim A As Integer
A = 1
Do Until Cells(A, 1) = ""
ActiveWorkbook.Names.Add Name:=Cells(A, 1), RefersToR1C1:=Cells(A, 2)
A = A + 1
Loop
End Sub

Regards - Dave.

 

[Don Guillett]

simple macro
Sub namecells()
For i = 1 To 3
Cells(i, "b").Name = Cells(i, "a")
Next
End Sub

 

[Dave Peterson]

One more:
Select A1:B3
Insert|Name|Create
Check Left Column and uncheck all the others.
Click ok.

 

[Dave]

Hi DP,
Thanks for pointing that out. Didn't know that was an option. Much simpler
and tidier than a macro.
Regards - Dave.

 

[Dave Peterson]

I've never used it in real life. I don't remember it's there until the macro
has finished <vbg>.

 

[Gord Dibben]

I've used the create names a few times to build a mileage matrix or similar.

city1, city2 etc. across the top and city_1, city_2 etc. down the side

Then =city4 city_12 retrieves the intersecting value.


Gord

 

[Dave Peterson]

This depends on the Tools|Options|Calculation tab|Accept labels in formulas
setting.

Since it was removed in xl2007, I guess MS didn't realize that you were one of
the few people who used this <vbg>.

 

[Gord Dibben]

That's it...........I'm never upgrading.

How presumptuous of MS to not include one of my favorite things.