(Excel) Define several incremental cells' names at once

[lemuffin]

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!

 

[Rick Rothstein]

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?

 

[samy4movies]

Re: (Excel) Define several incremental cells' names at once (MULTIPLE CELLS)

[TABLE="width: 155"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ELV_HD45_W0[/TD]
[/TR]
[TR]
[TD]ELV_LO_W0[/TD]
[/TR]
[TR]
[TD]ELV_STR_W0[/TD]
[/TR]
[TR]
[TD]ELV_LSTB_W0[/TD]
[/TR]
[TR]
[TD]ELV_RTNRB_W0[/TD]
[/TR]
[TR]
[TD]ELV_HD45_W10[/TD]
[/TR]
[TR]
[TD]ELV_LO_W10[/TD]
[/TR]
[TR]
[TD]ELV_STR_W10[/TD]
[/TR]
[TR]
[TD]ELV_LSTB_W10[/TD]
[/TR]
[TR]
[TD]ELV_RTNRB_W10[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Re: (Excel) Define several incremental cells' names at once (MULTIPLE CELLS)

[TABLE="width: 155"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ELV_HD45_W0[/TD]
[/TR]
[TR]
[TD]ELV_LO_W0[/TD]
[/TR]
[TR]
[TD]ELV_STR_W0[/TD]
[/TR]
[TR]
[TD]ELV_LSTB_W0[/TD]
[/TR]
[TR]
[TD]ELV_RTNRB_W0[/TD]
[/TR]
[TR]
[TD]ELV_HD45_W10[/TD]
[/TR]
[TR]
[TD]ELV_LO_W10[/TD]
[/TR]
[TR]
[TD]ELV_STR_W10[/TD]
[/TR]
[TR]
[TD]ELV_LSTB_W10[/TD]
[/TR]
[TR]
[TD]ELV_RTNRB_W10[/TD]
[/TR]
</tbody>[/TABLE]

This snippet of code is from the code you posted in Message #30...

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.

 

[samy4movies]

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

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

 

[samy4movies]

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 !

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

 

[CoffeeMe]

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!