(Excel) Define several incremental cells' names at once

lemuffin

New Member
Joined
Oct 31, 2011
Messages
4
Good morning folks!

I am trying to rename a row of cells with incremental names. Ex.

A1=Results1, A2=Results2, A3=Results3, ...., AN=ResultsN

Of course, it can be done by setting names for each cell separately but it takes ages. I would like to know if this is possible to do that quickly thanks to an add-in or VBA.

P.S. I use Excel 2003.

Thanks a LOT for you help!
 
Re: (Excel) Define several incremental cells' names at once (MULTIPLE CELLS)

Hi Rick, I am trying a simple long code but I receive an error '5' Invalid procedure call or argument.
This code I modified in the Dim to be able to read the cell name, it does read it, but then it stop at the 'HERE I'm marking. this might give you the idea in comparison with yours. Regards,

Sub NameRange()


Dim rngCell As Variant
Dim lngCnt As Long
Dim codeName As Variant
Dim RowRg As Long

For RowRg = 41 To 49

codeName = Cells(RowRg, "S").Value


lngCnt = 1
For Each rngCell In Range("I49:R49")
rngCell.Name = Cells(codeName) & lngCnt 'HERE is where it stops
lngCnt = lngCnt + 1
Next
Next


End Sub

What text do you have in cells S41:S49?
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Re: (Excel) Define several incremental cells' names at once (MULTIPLE CELLS)

ELV_HD45_W0
ELV_LO_W0
ELV_STR_W0
ELV_LSTB_W0
ELV_RTNRB_W0
ELV_HD45_W10
ELV_LO_W10
ELV_STR_W10
ELV_LSTB_W10
ELV_RTNRB_W10

<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0
Re: (Excel) Define several incremental cells' names at once (MULTIPLE CELLS)

ELV_HD45_W0
ELV_LO_W0
ELV_STR_W0
ELV_LSTB_W0
ELV_RTNRB_W0
ELV_HD45_W10
ELV_LO_W10
ELV_STR_W10
ELV_LSTB_W10
ELV_RTNRB_W10

<colgroup><col></colgroup><tbody>
</tbody>
This snippet of code is from the code you posted in Message #30...

Code:
For RowRg = 41 To 49
  [B][COLOR="#008000"]codeName = Cells(RowRg, "S").Value[/COLOR][/B]
  lngCnt = 1
  For Each rngCell In Range("I49:R49")
    rngCell.Name = [B][COLOR="#FF0000"]Cells(codeName[/COLOR])[/B] & lngCnt  'HERE is where it stops
    lngCnt = lngCnt + 1
  Next
Next
Let's examine what you have for RowRg equal to 41... codeName becomes "ELV_HD45_W0". Now look at how you used it in the red highlighted part of the code where you say the program stopped... substituting "ELV_HD45_W0" for codeName give you this for the red highlighted text...

Cells("ELV_HD45_W0")

The problem is two fold... the Cells property takes two arguments, not one... the row number and the column number or letter(s). Does "ELV_HD45_W0" look like either one of those kind of values? Of course not... so that is why your code stopped there.
 
Upvote 0
Re: (Excel) Define several incremental cells' names at once (MULTIPLE CELLS)

Hi Rick, I see what you saying now, it looks that is not complete the (row, col) right?
I would like to send you another option that I worked late tonight, and it seems that is working but my suffix increments are increasing for the first row went OK ELV_HD45_W01 to ...W010. But the next row 42
it showed ELV_LO_W011 trhough ...W020, then for the next row ELV_STR_W021 through ...W030 and so forth.
I changed from the previous one because I couldn't find the solution, but it can be done in both ways I guess, and since you are an expert on making it shorter, and if you don't mind I will appreciate your comment on this lately code. Thanks
Code:
Sub NameCellRange_short()
Dim rngCell As Range
Dim lngCnt As Long
Dim col As Long
Dim row As Long
Dim colcode As Long
Dim rowmax As Long

row = 41
rowmax = 50
'irow = row - 1
col = 9
lngCnt = 1    'increments for the sufix number in the last part of the cell name Range
For row = 41 To rowmax
    For Each rngCell In Range(Cells(row, col), Cells(row, col + 9))
    rngCell.Name = Range(Cells(row, "S"), Cells(row, "S")) & lngCnt
    lngCnt = lngCnt + 1
    Next
Next
End Sub
 
Upvote 0
Re: (Excel) Define several incremental cells' names at once (MULTIPLE CELLS)

I fixed acouple things in there and it doesn't stop anymore the only problem is the suffix. Thanks for you advise , Now i'm back in track thanks to you !
Code:
Sub NameRange()


Dim rngCell As Variant
Dim lngCnt As Long
Dim codeName As Variant
Dim RowRg As Long
Dim col As Long

col = 9
For RowRg = 41 To 49
   codeName = Cells(RowRg, "S").Value
   lngCnt = 1

   For Each rngCell In Range(Cells(RowRg, col), Cells(RowRg, col + 9))
      rngCell.Name = codeName & lngCnt
      lngCnt = lngCnt + 1
  Next
Next


End Sub
 
Upvote 0
Hi,

I am looking to define multiple names for a data set at once as there are 60+ ranges to define. I have a column ("F") containing numbers increasing by a value of 1 (1,2,3 etc.) however there are varying amounts of each number i.e. 20 rows with the value "1", 30 rows with value "2", 65 rows with value "3" etc. Is there a way to define these ranges with manually defininf each one?

Your help would be greatly appreciated!
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top