mass definitions of cell names

G

Grymjack

Ok, I have a problem. I have to name a large number of cells in
different sheets. I'm hoping that there is a way in a macro, or some
other way to define these in an easier way other than one by one. Here
is an example:

1 2 3 4
1001 --------- --------- --------- ---------
1002 --------- --------- --------- ---------
1209 --------- --------- --------- ---------
1210 --------- --------- --------- ---------


This would be the column and row headers for the cells. Below is how
the cells would have to be named:


1 2 3 4
1001 S_1001_01 S_1001_02 S_1001_03 S_1001_04
1002 S_1002_01 S_1002_02 S_1002_03 S_1002_04
1209 S_1209_01 S_1209_02 S_1209_03 S_1209_04
1210 S_1210_01 S_1210_02 S_1210_03 S_1210_04


There are thousands of cells I have to name in a similar fashion. Is
there a shortcut way to define these cells through a macro? Thanks in
advance for any help any of you may pass my way.

-Dan Canham
 
R

Ron Coderre

I can almost guarantee that there is a better alternative than creating
thousands of range names.

Post the functionality you'd like to have and see what solutions are offered.

***********
Regards,
Ron

XL2002, WinXP
 
G

Grymjack

Because of the quirks of the business network setup (read IT
intransigence), I have to reproduce a database for sales and inventory
within excel. I have no ability to get at an access or any other
database. Believe me....this is a culmination of a 6 month fight with
the IM department (Information Maintenance...haha). They will be stand
alone sheets that will be reference externally based on cell name and
product code. So the column headers would be 1-31 for days of the month
and the rows would be product codes. I don't want to do it
positionally, because that would be bad when new product codes were
added in the middle of the numerical sequence later.
 
R

Ron Coderre

Grymjack

With all due respect to everyone involved in that project, attempting to
harvest the data in the way you describe should be abandoned immediately and
a alternative process should be pursued. I can't imagine an IT professional
attempting to pull thousands of data cells from a workbook by churning
through that many range names. But...if that's what you MUST deal with...my
condolences.

***********
Respectfully,
Ron

XL2002, WinXP
 
D

Don Guillett

I recently had a client pay me to remove MANY names and restore the formulas
to cell reference instead of name reference. Keep my name WHEN you need this
service. Change the procedure now.

OR, at least name only a few and use OFFSET formulas for the others.

However, what you say is doable.
 
D

Don Guillett

If you insist on this, here is one way

Sub namecells()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a2:a" & lr)
For i = 1 To 4
c.Offset(, i).Name = "S_" & c & "_0" & i
Next i
Next c
End Sub
 
G

Grymjack

Don said:
If you insist on this, here is one way

Sub namecells()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a2:a" & lr)
For i = 1 To 4
c.Offset(, i).Name = "S_" & c & "_0" & i
Next i
Next c
End Sub
Click to expand...

Thanks for the help
 
D

Don Guillett

You really shouldn't do this but if you do keep my email to pay me to change
it back.
 
Top