Run-time error 1004

J

Jerry McNabb

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).Value = _
Application.InputBox(prompt:="Enter value", Type:=1)
Worksheets("Columns").Range(SelectCell.Address).Interior.ColorIndex =
clrGold
Worksheets("Columns").Range(Worksheets("ColumnList") _
.Range(SelectCell.Address).Value).Interior.ColorIndex = clrGold
Worksheets("Rows").Range(SelectCell.Address).Interior.ColorIndex =
clrGold
Worksheets("Rows").Range(Worksheets("RowList").Range(SelectCell.Address) _
.Value).Interior.ColorIndex = clrGold
Worksheets("Rows").Range(SelectRange).Interior.Color = clrGold
Worksheets("Areas").Range(SelectCell.Address).Interior.ColorIndex = clrGold

Worksheets("Areas").Range(Worksheets("AreaList").Range(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.ColorIndex = clrGold
returns a "Run-Time Error '1004'
"Application-Defined or Object-Defined Error".

Can someone help me?

Thank you.
 
J

Jim Cone

Re: "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.ColorIndex = "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).Value = _
Application.InputBox(prompt:="Enter value", Type:=1)
Worksheets("Columns").Range(SelectCell.Address).Interior.ColorIndex =
clrGold
Worksheets("Columns").Range(Worksheets("ColumnList") _
.Range(SelectCell.Address).Value).Interior.ColorIndex = clrGold
Worksheets("Rows").Range(SelectCell.Address).Interior.ColorIndex =
clrGold
Worksheets("Rows").Range(Worksheets("RowList").Range(SelectCell.Address) _
.Value).Interior.ColorIndex = clrGold
Worksheets("Rows").Range(SelectRange).Interior.Color = clrGold
Worksheets("Areas").Range(SelectCell.Address).Interior.ColorIndex = clrGold

Worksheets("Areas").Range(Worksheets("AreaList").Range(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.ColorIndex = clrGold
returns a "Run-Time Error '1004'
"Application-Defined or Object-Defined Error".
Can someone help me?
Thank you.
 
J

JLGWhiz

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.
 
J

Jerry McNabb

Thanks a bunch guys. I'll try that.

JLGWhiz said:
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.
Click to expand...
 
J

Jerry McNabb

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).
 
J

Jerry McNabb

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).
 
J

Jerry McNabb

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?
 
J

Jerry McNabb

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?
 
J

Jim Cone

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)




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?
 
J

Jerry McNabb

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error '1004' 1
Object does not exist. 4
Run-time error 1004 8
Run Time Error '1004' 1
Public Variables 0
Run Time Error 1004 in Loop 0
Loop a code thru several worksheets 2
Error 1004 3

Top