Auto Naming for Cell Ranges

A

Adrian

Hi,

I just read that the method "CreateNames" can generate names
automatically for cell ranges based on the text labels in the sheet.

I tried to use the method in the context below but it fails to generate the
names as promised. Can someone kindly explain why does it fail in this case
and what can be done to rectify the problem ? Thanks.


Sample Worksheet: The worksheet below shows the temperature readings for
Monday to Wednesday. The readings are taken in the morning(AM), afternoon
(PM) and evening (EVE) of each day.

A B C D E
******************************
1 Mon Tues Wed
2 AM 25 23 18
3 PM 33 30 28
4 EVE 26 25 21
5


Task : To write a VBA routine to name the cells automatically such that

cell B2 will be named Mon_AM,
cell C2 will be named Tues_AM,
so on and so forth.

VBA Routine:
Sub Test()
Dim r as Range

r = Range(B2:D4)
r.CreateNames Top:=true , Left:=true
End Sub

Results: Nothing Happened ! Why ?


Please kindly advise. Thanking in advance.
 
M

Myrna Larson

You're misunderstanding what Excel means by creating names. It creates names
for a row or column of data, not for each cell.

In your case, Create Names will create these *6* names (not 9):

column ranges: Mon = B2:B5, Tues = C2:C5, Wed = D2:D5
row ranges: AM = B2:D2, PM = B3:D3, EVE = B4:D4

Verify that by using Insert/Name/Create, then go to Insert/Name/Define to see
what names have been created and what they refer to.

In a formula, to reference the data for Monday evening you write the reference
using the intersection operator,

=Mon AM

Note there's no underscore.

Given the way Excel does it, you can then write a formula like =SUM(AM) or
=SUM(Mon). You couldn't do that if each name referred to just one cell.
 
Top