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

 

[samy4movies]

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

Hi Rick, I'm sorry but it didn't work, I have an error '1004' The name that you entered is not valid, Reasons, name does not begin with a letter,contains spaces or invalid characters,conflicts with Excel built-in name.

 

[Rick Rothstein]

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

Hi Rick, I'm sorry but it didn't work, I have an error '1004' The name that you entered is not valid, Reasons, name does not begin with a letter,contains spaces or invalid characters,conflicts with Excel built-in name.

That is not my code doing it... the text you have in the cells of Column S apparently do not start with a correct character for a tab name.

 

[samy4movies]

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

Hi Rick, I'm sorry but it didn't anything

 

[Rick Rothstein]

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

Hi Rick, I'm sorry but it didn't anything

Wait a minute... the cells for Column S are on a different worksheet, aren't they. Assuming that sheet name is INNER-WALLS-INPUTS as was shown in a previous message, try this then...

Sub NameRange()
  Dim RowRange As Long
  For RowRange = 41 To 49
    Cells(RowRange, "I").Resize(, 10).Name = Sheets("INNER-WALLS-INPUTS").Cells( _
                                             RowRange, "S").Value & (RowRange - 40)
  Next
End Sub

 

[samy4movies]

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

Not really, everything is in the same sheet I just added to make sure it doesn't go some place else. Bu I will try this last one

 

[samy4movies]

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

Sorry it doesn't retrieve the name from the cell at "S..." to name the cell. I tried the function key F8, to go one by one in line, it just goes through all fine but it doesn't name the cell.

 

[samy4movies]

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

Hi Rick,
I fixed the previous error '1004" it was an empty cell, sorry, but any way even that it didn't work.

 

[Rick Rothstein]

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

Hi Rick,
I fixed the previous error '1004" it was an empty cell, sorry, but any way even that it didn't work.

In that case, I am at a loss... when I create fake data and try the codes I posted, it works fine for me. Maybe someone else will come along with better ideas about the problem.

 

[samy4movies]

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

Hi Rick, but the initial code worked for me at first, I just wanted to change it to be automatic with a couple range loops right? I can't say that you are a loss, I think It might be at my side but I can't find the problem on that. Thanks I will continue testing it. Ok

This is the code I'm talking about

Dim rngCell As Range
Dim lngCnt As Long

lngCnt = 1
For Each rngCell In Range("A1:a15")
rngCell.Name = "Results" & lngCnt
lngCnt = lngCnt + 1

 

[samy4movies]

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