| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 1004
The following macro is intended to accept, through two InputBoxes, a cell
address and a value to be entered into that cell. It then sets the interior color to gold. It then looks into a cross reference matrix. Each cell in the matrix contains a range of addresses in the format "L1:U1". It then sets the interior colors of the ten cells in that range within the first worksheet. It then does the same with a second crossreference and the second worksheet and again with the third. Sub Initialize() Dim clrGold As Integer Dim SelectCell As Range clrGold = Worksheets("Columns").Cells(13, 2).Interior.ColorIndex Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8) Worksheets("Columns").Range(SelectCell.Address).Va lue = _ Application.InputBox(prompt:="Enter value", Type:=1) Worksheets("Columns").Range(SelectCell.Address).In terior.ColorIndex = clrGold Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold Worksheets("Rows").Range(SelectCell.Address).Inter ior.ColorIndex = clrGold Worksheets("Rows").Range(Worksheets("RowList").Ran ge(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold Worksheets("Rows").Range(SelectRange).Interior.Col or = clrGold Worksheets("Areas").Range(SelectCell.Address).Inte rior.ColorIndex = clrGold Worksheets("Areas").Range(Worksheets("AreaList").R ange(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold End Sub When I try to run it. Everything seems fine. clrGold is set to 44. SelectCell.Adddress is set to B1 and its value is set to 5. Columns!$B$1 gets set to 5 and its color is set to gold, just as expected. ColumnList!$B$1 contains "W1:AF1". The instruction Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold returns a "Run-Time Error '1004' "Application-Defined or Object-Defined Error". Can someone help me? Thank you. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error 1004
"SelectCell.Adddress is set to B1 and its value is set to 5" So this... Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = "clrGold" Equals this ?... Worksheets("Columns").Range(5).Interior.ColorIndex = "clrGold" Which sheet is doing what to whom is difficult to determine, but it appears you can remove ".Value" and it should work. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jerry McNabb" wrote in message The following macro is intended to accept, through two InputBoxes, a cell address and a value to be entered into that cell. It then sets the interior color to gold. It then looks into a cross reference matrix. Each cell in the matrix contains a range of addresses in the format "L1:U1". It then sets the interior colors of the ten cells in that range within the first worksheet. It then does the same with a second crossreference and the second worksheet and again with the third. Sub Initialize() Dim clrGold As Integer Dim SelectCell As Range clrGold = Worksheets("Columns").Cells(13, 2).Interior.ColorIndex Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8) Worksheets("Columns").Range(SelectCell.Address).Va lue = _ Application.InputBox(prompt:="Enter value", Type:=1) Worksheets("Columns").Range(SelectCell.Address).In terior.ColorIndex = clrGold Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold Worksheets("Rows").Range(SelectCell.Address).Inter ior.ColorIndex = clrGold Worksheets("Rows").Range(Worksheets("RowList").Ran ge(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold Worksheets("Rows").Range(SelectRange).Interior.Col or = clrGold Worksheets("Areas").Range(SelectCell.Address).Inte rior.ColorIndex = clrGold Worksheets("Areas").Range(Worksheets("AreaList").R ange(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold End Sub When I try to run it. Everything seems fine. clrGold is set to 44. SelectCell.Adddress is set to B1 and its value is set to 5. Columns!$B$1 gets set to 5 and its color is set to gold, just as expected. ColumnList!$B$1 contains "W1:AF1". The instruction Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold returns a "Run-Time Error '1004' "Application-Defined or Object-Defined Error". Can someone help me? Thank you. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error 1004
You're a good man Jim Cone, I sure couldn't decipher it, but the Value part
didn't make sense to me either. I was also wondering if there shouldn't be a comma after the Worksheets("ColumnList") clause. I just couldn't see where a range was defined. "Jim Cone" wrote: "SelectCell.Adddress is set to B1 and its value is set to 5" So this... Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = "clrGold" Equals this ?... Worksheets("Columns").Range(5).Interior.ColorIndex = "clrGold" Which sheet is doing what to whom is difficult to determine, but it appears you can remove ".Value" and it should work. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jerry McNabb" wrote in message The following macro is intended to accept, through two InputBoxes, a cell address and a value to be entered into that cell. It then sets the interior color to gold. It then looks into a cross reference matrix. Each cell in the matrix contains a range of addresses in the format "L1:U1". It then sets the interior colors of the ten cells in that range within the first worksheet. It then does the same with a second crossreference and the second worksheet and again with the third. Sub Initialize() Dim clrGold As Integer Dim SelectCell As Range clrGold = Worksheets("Columns").Cells(13, 2).Interior.ColorIndex Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8) Worksheets("Columns").Range(SelectCell.Address).Va lue = _ Application.InputBox(prompt:="Enter value", Type:=1) Worksheets("Columns").Range(SelectCell.Address).In terior.ColorIndex = clrGold Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold Worksheets("Rows").Range(SelectCell.Address).Inter ior.ColorIndex = clrGold Worksheets("Rows").Range(Worksheets("RowList").Ran ge(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold Worksheets("Rows").Range(SelectRange).Interior.Col or = clrGold Worksheets("Areas").Range(SelectCell.Address).Inte rior.ColorIndex = clrGold Worksheets("Areas").Range(Worksheets("AreaList").R ange(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold End Sub When I try to run it. Everything seems fine. clrGold is set to 44. SelectCell.Adddress is set to B1 and its value is set to 5. Columns!$B$1 gets set to 5 and its color is set to gold, just as expected. ColumnList!$B$1 contains "W1:AF1". The instruction Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold returns a "Run-Time Error '1004' "Application-Defined or Object-Defined Error". Can someone help me? Thank you. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error 1004
Thanks a bunch guys. I'll try that.
"JLGWhiz" wrote: You're a good man Jim Cone, I sure couldn't decipher it, but the Value part didn't make sense to me either. I was also wondering if there shouldn't be a comma after the Worksheets("ColumnList") clause. I just couldn't see where a range was defined. "Jim Cone" wrote: "SelectCell.Adddress is set to B1 and its value is set to 5" So this... Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = "clrGold" Equals this ?... Worksheets("Columns").Range(5).Interior.ColorIndex = "clrGold" Which sheet is doing what to whom is difficult to determine, but it appears you can remove ".Value" and it should work. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jerry McNabb" wrote in message The following macro is intended to accept, through two InputBoxes, a cell address and a value to be entered into that cell. It then sets the interior color to gold. It then looks into a cross reference matrix. Each cell in the matrix contains a range of addresses in the format "L1:U1". It then sets the interior colors of the ten cells in that range within the first worksheet. It then does the same with a second crossreference and the second worksheet and again with the third. Sub Initialize() Dim clrGold As Integer Dim SelectCell As Range clrGold = Worksheets("Columns").Cells(13, 2).Interior.ColorIndex Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8) Worksheets("Columns").Range(SelectCell.Address).Va lue = _ Application.InputBox(prompt:="Enter value", Type:=1) Worksheets("Columns").Range(SelectCell.Address).In terior.ColorIndex = clrGold Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold Worksheets("Rows").Range(SelectCell.Address).Inter ior.ColorIndex = clrGold Worksheets("Rows").Range(Worksheets("RowList").Ran ge(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold Worksheets("Rows").Range(SelectRange).Interior.Col or = clrGold Worksheets("Areas").Range(SelectCell.Address).Inte rior.ColorIndex = clrGold Worksheets("Areas").Range(Worksheets("AreaList").R ange(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold End Sub When I try to run it. Everything seems fine. clrGold is set to 44. SelectCell.Adddress is set to B1 and its value is set to 5. Columns!$B$1 gets set to 5 and its color is set to gold, just as expected. ColumnList!$B$1 contains "W1:AF1". The instruction Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold returns a "Run-Time Error '1004' "Application-Defined or Object-Defined Error". Can someone help me? Thank you. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error 1004
Remember, SelectCell's address = B1. At this time Columns!$B$1 = 5 and
ColumnList!$B$1 = "W1:AF1". I want to set the interior color index of Columns!W1:AF1 to gold (44). "JLGWhiz" wrote: You're a good man Jim Cone, I sure couldn't decipher it, but the Value part didn't make sense to me either. I was also wondering if there shouldn't be a comma after the Worksheets("ColumnList") clause. I just couldn't see where a range was defined. "Jim Cone" wrote: "SelectCell.Adddress is set to B1 and its value is set to 5" So this... Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = "clrGold" Equals this ?... Worksheets("Columns").Range(5).Interior.ColorIndex = "clrGold" Which sheet is doing what to whom is difficult to determine, but it appears you can remove ".Value" and it should work. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jerry McNabb" wrote in message The following macro is intended to accept, through two InputBoxes, a cell address and a value to be entered into that cell. It then sets the interior color to gold. It then looks into a cross reference matrix. Each cell in the matrix contains a range of addresses in the format "L1:U1". It then sets the interior colors of the ten cells in that range within the first worksheet. It then does the same with a second crossreference and the second worksheet and again with the third. Sub Initialize() Dim clrGold As Integer Dim SelectCell As Range clrGold = Worksheets("Columns").Cells(13, 2).Interior.ColorIndex Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8) Worksheets("Columns").Range(SelectCell.Address).Va lue = _ Application.InputBox(prompt:="Enter value", Type:=1) Worksheets("Columns").Range(SelectCell.Address).In terior.ColorIndex = clrGold Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold Worksheets("Rows").Range(SelectCell.Address).Inter ior.ColorIndex = clrGold Worksheets("Rows").Range(Worksheets("RowList").Ran ge(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold Worksheets("Rows").Range(SelectRange).Interior.Col or = clrGold Worksheets("Areas").Range(SelectCell.Address).Inte rior.ColorIndex = clrGold Worksheets("Areas").Range(Worksheets("AreaList").R ange(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold End Sub When I try to run it. Everything seems fine. clrGold is set to 44. SelectCell.Adddress is set to B1 and its value is set to 5. Columns!$B$1 gets set to 5 and its color is set to gold, just as expected. ColumnList!$B$1 contains "W1:AF1". The instruction Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold returns a "Run-Time Error '1004' "Application-Defined or Object-Defined Error". Can someone help me? Thank you. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error 1004
Sorry, with .value deleted, I got the same error.
Remember, SelectCell's address = B1. At this time Columns!$B$1 = 5 and ColumnList!$B$1 = "W1:AF1". I want to set the interior color index of Columns!W1:AF1 to gold (44). "Jim Cone" wrote: "SelectCell.Adddress is set to B1 and its value is set to 5" So this... Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = "clrGold" Equals this ?... Worksheets("Columns").Range(5).Interior.ColorIndex = "clrGold" Which sheet is doing what to whom is difficult to determine, but it appears you can remove ".Value" and it should work. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jerry McNabb" wrote in message The following macro is intended to accept, through two InputBoxes, a cell address and a value to be entered into that cell. It then sets the interior color to gold. It then looks into a cross reference matrix. Each cell in the matrix contains a range of addresses in the format "L1:U1". It then sets the interior colors of the ten cells in that range within the first worksheet. It then does the same with a second crossreference and the second worksheet and again with the third. Sub Initialize() Dim clrGold As Integer Dim SelectCell As Range clrGold = Worksheets("Columns").Cells(13, 2).Interior.ColorIndex Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8) Worksheets("Columns").Range(SelectCell.Address).Va lue = _ Application.InputBox(prompt:="Enter value", Type:=1) Worksheets("Columns").Range(SelectCell.Address).In terior.ColorIndex = clrGold Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold Worksheets("Rows").Range(SelectCell.Address).Inter ior.ColorIndex = clrGold Worksheets("Rows").Range(Worksheets("RowList").Ran ge(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold Worksheets("Rows").Range(SelectRange).Interior.Col or = clrGold Worksheets("Areas").Range(SelectCell.Address).Inte rior.ColorIndex = clrGold Worksheets("Areas").Range(Worksheets("AreaList").R ange(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold End Sub When I try to run it. Everything seems fine. clrGold is set to 44. SelectCell.Adddress is set to B1 and its value is set to 5. Columns!$B$1 gets set to 5 and its color is set to gold, just as expected. ColumnList!$B$1 contains "W1:AF1". The instruction Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold returns a "Run-Time Error '1004' "Application-Defined or Object-Defined Error". Can someone help me? Thank you. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error 1004
Pardon me if I am repeating myself, but my first attempt at posting this
didn't seem to work. With .Value deleted, I got the same error message. Remember, SelectCell's address = B1. At this time Columns!$B$1 = 5 and ColumnList!$B$1 = "W1:AF1". I want to set the interior color index of Columns!W1:AF1 to gold (44). Is worksheets("ColumnList").Range(SelectCell) not = ColumnList!B1? "Jim Cone" wrote: "SelectCell.Adddress is set to B1 and its value is set to 5" So this... Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = "clrGold" Equals this ?... Worksheets("Columns").Range(5).Interior.ColorIndex = "clrGold" Which sheet is doing what to whom is difficult to determine, but it appears you can remove ".Value" and it should work. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jerry McNabb" wrote in message The following macro is intended to accept, through two InputBoxes, a cell address and a value to be entered into that cell. It then sets the interior color to gold. It then looks into a cross reference matrix. Each cell in the matrix contains a range of addresses in the format "L1:U1". It then sets the interior colors of the ten cells in that range within the first worksheet. It then does the same with a second crossreference and the second worksheet and again with the third. Sub Initialize() Dim clrGold As Integer Dim SelectCell As Range clrGold = Worksheets("Columns").Cells(13, 2).Interior.ColorIndex Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8) Worksheets("Columns").Range(SelectCell.Address).Va lue = _ Application.InputBox(prompt:="Enter value", Type:=1) Worksheets("Columns").Range(SelectCell.Address).In terior.ColorIndex = clrGold Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold Worksheets("Rows").Range(SelectCell.Address).Inter ior.ColorIndex = clrGold Worksheets("Rows").Range(Worksheets("RowList").Ran ge(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold Worksheets("Rows").Range(SelectRange).Interior.Col or = clrGold Worksheets("Areas").Range(SelectCell.Address).Inte rior.ColorIndex = clrGold Worksheets("Areas").Range(Worksheets("AreaList").R ange(SelectCell.Address) _ .Value).Interior.ColorIndex = clrGold End Sub When I try to run it. Everything seems fine. clrGold is set to 44. SelectCell.Adddress is set to B1 and its value is set to 5. Columns!$B$1 gets set to 5 and its color is set to gold, just as expected. ColumnList!$B$1 contains "W1:AF1". The instruction Worksheets("Columns").Range(Worksheets("ColumnList ") _ .Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold returns a "Run-Time Error '1004' "Application-Defined or Object-Defined Error". Can someone help me? Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
| Run time error 1004 Object defined error | Excel Programming | |||
| Run Time Error 1004: Application or Object Defined Error | Excel Programming | |||
| Run Time 1004 Error: Application or Object Difine Error | Excel Programming | |||
| run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Hybrid Mode
