Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming cells using partial cell data
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming cells using partial cell data
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 -----Original Message----- 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! . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming cells using partial cell data
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" -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming cells using partial cell data
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 -----Original Message----- Ronny I can not see where in your code you are setting the value for your 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 number and column letters in the Cell Command Cells(1,1) or cells(1,"a") --- Message posted from http://www.ExcelForum.com/ . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming cells using partial cell data
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming cells using partial cell data
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. -----Original Message----- 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 appropriate places. This allows a single line of code to be spread over several 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 Next --- Message posted from http://www.ExcelForum.com/ . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming cells using partial cell data
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 -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming cells using partial cell data
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. -- Regards, Tom Ogilvy --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming cells using partial cell data
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 -----Original Message----- 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. -- Regards, Tom Ogilvy --- Message posted from http://www.ExcelForum.com/ . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming cells using partial cell data
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation of partial data in a cell. | Excel Discussion (Misc queries) | |||
Formula for Pulling partial data from one cell into another | Excel Worksheet Functions | |||
remove partial data from cell | Excel Worksheet Functions | |||
combining partial data from 2 different cells | Excel Discussion (Misc queries) | |||
Bringing partial data from one cell into another | Excel Worksheet Functions |