(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!

 

[NewOrderFac33]

I have created a workbook containing two userforms, one to create names and one to delete them
The first requires you to input the root of the name e.g. "Sales", the start and end values (e.g. 1 to 10), the height and width of each range in rows and columns and the number of cells down and across you need to move before creating the next name.
For the above example, it would create names "Sales_1", Sales_2"..."Sales_10".

The second allows you to delete either a range of names once you specify the name root e.g. "Sales_1" to "Sales_5" or ALL names that begin with the name root e.g. "Sales_1" to "Sales_X".

They works really well when you add the code and the forms into the Personal Macro workbook, but you can also just open and close it as a standalone workbook, as it always works on the active worksheet.

I'd be happy to email it to anyone who PMs me. Then Rick can tell me how to improve it..!

Pete

 

[spontaneouslydia]

I'm definitely interested!! Send it my way...
gilbertandlydia330@yahoo.com

 

[NewOrderFac33]

At work now - will send tonight or tomorrow!

 

[spontaneouslydia]

Thank you!

 

[samy4movies]

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

I think this would be a simpler way to write that code...

Sub NameRange()
    Dim RowRange As Long
    For RowRange = 1 To 15
      Cells(RowRange, "A").Name = "Results" & RowRange
    Next
End Sub

I appreciate what you show in this trend, but now I'm more in detail how to name the cell not from a hard code word like "Results" but from a reference cell. i.e the name of the different Cells Names is located in a cell from an specific sheet. Please help me in fix the code that I am trying to write in order to get different name in a Range way. This is the code I have so far but I got a Run-time error'13' Type mismatch.

The purpose is to name different cells with different series of Names, the Name is located at The "S" column and the name change in different Rows.
The routine will name the cells from "I49" to "R49" with the name in "S49" then the next row and so forth.

Thanks everyone that come out with the help on this matter.
Sincerely, Samy


Sub NameRange()


Dim rngCell As Range
Dim lngCnt As Long

lngCnt = 1
For Each rngCell In Range("I49:R49")
rngCell.Name = Sheets("INNER-WALLS-INPUTS").Cells.Value("S49") & lngCnt
lngCnt = lngCnt + 1
Next


End Sub

 

[Rick Rothstein]

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

I appreciate what you show in this trend, but now I'm more in detail how to name the cell not from a hard code word like "Results" but from a reference cell. i.e the name of the different Cells Names is located in a cell from an specific sheet.

Your question is not entirely clear to me, but let me take a guess at a solution for you. Does this code do what you want?

Sub NameRange()
  Dim RowRange As Long
  For RowRange = 49 To Cells(Rows.Count, "I").End(xlUp).Row
    Cells(RowRange, "I").Resize(, 10).Name = Sheets("INNER-WALLS-INPUTS").Cells(RowRange, "S").Value
  Next
End Sub

 

[samy4movies]

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

Your question is not entirely clear to me, but let me take a guess at a solution for you. Does this code do what you want?

Sub NameRange()
  Dim RowRange As Long
  For RowRange = 49 To Cells(Rows.Count, "I").End(xlUp).Row
    Cells(RowRange, "I").Resize(, 10).Name = Sheets("INNER-WALLS-INPUTS").Cells(RowRange, "S").Value
  Next
End Sub

Thanks first of all for taking the time to help me out.
I'm very sorry if mislead what I need.
The code you posted it doesn't work, and probably because of my fault.
The code that worked fine was the one that it was suggested at your trend, but I don't want to change of the "Results" for every Name that I have in many cells, all are different.
This is what it worked for me;

Dim rngCell As Range
Dim lngCnt As Long

lngCnt = 1
For Each rngCell In Range("I41:R41") 'since it is in the same row but I need to increase the row as well here.
'Then the next line get the name and it works
rngCell.Name = "ELV_HD45_W" & lngCnt
' But I have to do this in many places so I would like to have the name from a cell located at "S41"
lngCnt = lngCnt + 1
Next

So my idea was to replace the "ELV_HD45_W" with the name at the cell

Is this more clear or I messed up again?

Thanks, I hope I can find the solution, last night I lost all the changes I did, that's why I wanted to do it in a macro.

Regards, Sam

 

[Rick Rothstein]

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

Thanks first of all for taking the time to help me out.
I'm very sorry if mislead what I need.
The code you posted it doesn't work, and probably because of my fault.
The code that worked fine was the one that it was suggested at your trend, but I don't want to change of the "Results" for every Name that I have in many cells, all are different.
This is what it worked for me;

Dim rngCell As Range
Dim lngCnt As Long

lngCnt = 1
For Each rngCell In Range("I41:R41") 'since it is in the same row but I need to increase the row as well here.
'Then the next line get the name and it works
rngCell.Name = "ELV_HD45_W" & lngCnt
' But I have to do this in many places so I would like to have the name from a cell located at "S41"
lngCnt = lngCnt + 1
Next

So my idea was to replace the "ELV_HD45_W" with the name at the cell

Is this more clear or I messed up again?

Sorry, this is still not clear to me... you changed from Row 49 to Row 41 (so I now have no idea what range of cells you need to process) and you changed from getting the name from Column S on a particular worksheet to using a hard-coded text string instead. You have to keep in mind when you ask questions on a newsgroup that the people you are asking to help you have no idea what your data looks, no idea where it is located and no idea what you want to do with it... all we know is what you tell us, so you must be very specific in describing your data, its layout and your desire as to what to do with it... you cannot assume we can figure out anything on our own.

 

[samy4movies]

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

Hi Rick,
Sorry for all the inconvenience, You're right I just posted what it worked before for me, according with a previous trend I found here in this trend.
The Rows (41 or 49) is irrelevant because it will change incrementally according with a Rowrange. What I just mentioned again is, that code I posted is how it worked in terms of one line at the time.
But Ill be happy to Name the cells from I41:R41 with a name at "S41" and incrementing the number at the end, then all the way to I49:R49 with a name at "S49". The name at the "S" column is different in every row.
My apologies for not being clear from the beginning, but I hope now you can understand my requirement.
Thanks,

 

[Rick Rothstein]

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

Okay, does this do what you want...

Sub NameRange()
  Dim RowRange As Long
  For RowRange = [B][COLOR="#0000FF"]41[/COLOR][/B] To 49
    Cells(RowRange, "I").Resize(, 10).Name = Cells(RowRange, "S").Value & (RowRange - [B][COLOR="#FF0000"]40[/COLOR][/B])
  Next
End Sub

Note that the red number is always one less than the blue number.