| 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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error 1004
I did it! It finally works. I had entered the references in ColumnList etc.
with quotation marks so they appeared in the set statements with two quotatiion marks in front and two behind. Something you said gave me a clue. Thanks for the patience, Jim. This was not an important project, but it was sticking in my craw. I really appreciate the help. "Jim Cone" wrote: I'll give it another try, however things are still not clear. Once you have established (set) a range object you do not qualify it when you use it. Assuming the range object SelectCell refers to WS1.Range("B5") then WS1.Range(SelectCell).Value = 44 fails, while SelectCell.Value = 44 is valid. However, the address of a range object is a string and the string has no reference by itself to any particular worksheet. So the string (address) can be used with the Range method on another sheet... WS2.Range(SelectCell.Address).Value = 123 is valid and is the same as WS2.Range("B5").Value = 123 -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jerry McNabb" wrote in message ... Okay, I finally got the message through my head. It means that The error is one I defined in the application or one defined by the object. Is there any way to find out what this error is? |
| 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 | |||
Linear Mode
