Naming cells using partial cell data

[Ronny]

I get the following error when I run this code:

-----
Error:

Run-time error '1004':
That name is not valid.
-----

Code:

Dim lastrow As Long
Dim row_index As Long

For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "D").Value, 4) <> Left
(Cells(row_index + 1, "D").Value, 4) Then
Cells(row_index + 1, "D").Resize(2,
1).EntireRow.Insert (xlShiftDown)
Cells(row_index + 1, "C").FormulaR1C1
= "=SUMPRODUCT(--(LEFT(R1C4:R[-1]C4,4)=LEFT(R[-1]
C4,4)),R1C3:R[-1]C3)"
Cells(row_index + 1, "C").Font.ColorIndex = 3
Cells(row_index + 1, "C").Font.Bold = True
Cells(row_index + 1, "C").Font.Size = 14
Cells(row_index + 1, "C").Name = Left(Cells
(row_index, "D").Value, 4)
End If
Next

Debugging points to the last line in the for loop (naming
the cell using the first four characters from the cell
above.)

Thanks in advance!

 

[DavidC]

Try replace "D" with 4 in the cells(row_index),"D" part of
the routine. Cells needs to be defined as a row number
(rowindex) and a column number not the column name.

best of luck

DavidC

 

[mudraker]

Ronny

I can not see where in your code you are setting the value for you
variable lastrow which looks like it would be equal to 0 or - 1


DavidC

I hate to say it but you have erred. Yoiu can use both column numbe
and column letters in the Cell Command

Cells(1,1)

or

cells(1,"a"

 

[Ronny]

DavidC -

Tried changing the "D" to a 4 and that didn't work.


mudraker -

I have failed to copy that here (Sorry!) - Just
before the For loop, I have this:

lastrow = ActiveSheet.Cells(Rows.Count, "E").End
(xlUp).Row

There is data in column E (column #5) as well.

I am using Excel 97 SR-2, and I haven't tried this on any
of the later versions to see if it's just Excel 97 or
not...

Ronny

 

[mudraker]

ronny

I am using excel 97 and do not get the same error that you are.

You may be suffering from a broken line wrap command


try this code which is your code with line wrap added in at appropriat
places. This allows a single line of code to be spread over severa
continuose lines



Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.Count, _
"E").End(xlUp).Row

For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "D").Value, 4) _
<> Left(Cells(row_index + 1, "D").Value, 4) Then
Cells(row_index + 1, "D").Resize(2, 1). _
EntireRow.Insert (xlShiftDown)
Cells(row_index + 1, "C").FormulaR1C1 _
= "=SUMPRODUCT(--(LEFT(R1C4:R[-1]C4,4)" _
& "=LEFT(R[-1]C4,4)),R1C3:R[-1]C3)"
Cells(row_index + 1, "C").Font.ColorIndex = 3
Cells(row_index + 1, "C").Font.Bold = True
Cells(row_index + 1, "C").Font.Size = 14
Cells(row_index + 1, "C").Name = _
Left(Cells(row_index, "D").Value, 4)
End If
Nex

 

[Ronny]

mudraker -

Thanks for the help, but it still gave me the same error
message oddly enough...

Maybe it's just a bad installation. Unfortunately, the
installation is a work-related thing, so maybe I might
have to contact our help desk about this.

 

[mudraker]

Ronny

The only other thing I can think of that could be causing your proble
is that the name you are trying to asign to the cell is a name that i
reserved by Excel for it's own use. Have you tried manually giving th
cell the same name.

I have been able to generate an error messages (Not the same as you
error message) when the cell in column D was Blank.

What call name is the macro trying to use when the Error is generated

 

[Tom Ogilvy]

do the left 4 characters of the cell meet the requirements for a valid
range:

no spaces
doesn't look like a cell reference
doesn't start with a number


there may be more, but these immediately come to mind.

 

[Ronny]

Ah ha...

Some of the cells (of course the ones in the middle of the
spreadsheet) start with a number...

Is there any way to do this naming then? Perhaps by
adding text before the four characters that it pulls for
the name? (for instance, since the name can't be 9999,
can text like "Department" be added to it at the beginning
like "Department")

Thank you!

PS - I hate to ask, but can anyone recommended a few good
books on this stuff (references, etc.)


Thank you again -
Ronny

 

[mudraker]

Ronny


You certainly change your code to add additional letters to the name

eg


Cells(row_index + 1, "C").Name = _
"Dep " & Left(Cells(row_index, "D").Value, 4