Object does not exist.

[Jerry McNabb]

I’m trying to write a macro in an excel workbook. So far I have the following:



Sub Initialize()
‘Line 1

Dim SelectCell As Range
‘Line 2
Dim clrGold As Integer
‘Line 3

clrGold = Worksheets("Columns").Cells(13, 2).Interior.ColorIndex
‘Line 4

Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8)
‘Line 5

Worksheets("Columns").Range(SelectCell).Value = _
Application.InputBox(prompt:="Enter value", Type:=1)
‘Line 6
Worksheets("Columns").Range(SelectCell).Interior.ColorIndex = clrGold
‘Line 7
Worksheets("Rows").Range(SelectCell).Interior.ColorIndex = clrGold
‘Line 8
Worksheets("Areas").Range(SelectCell).Interior.ColorIndex = clrGold
‘Line 9

End Sub

Line 4 sets clrGold to 44.
In the InputBox for line 5, I enter B1.
In the InputBox for line 6, I enter 5.
Line 6 flags an error that says the object doesn’t exist. I have tried hard
coding the 5 but get the same result. If I comment out line 6, I get the same
message on line 7. If I then comment out line 7, I get the same error on line
8.

Any ideas?
Thanks for the help.

 

[FSt1]

hi
wild guess but i would guess vb don't like your syntax.
what does this macro do?

regards
FSt1

 

[Tim Zych]

SelectCell is a Range object, so this line:

Worksheets("Columns").Range(SelectCell).Value

is returning the value in the SelectCell, not the address.

Because Value is the default property of a Range, the first code line is
equivalent to:

Worksheets("Columns").Range(SelectCell.Value).Value

I think what you really want is something like:

Dim SelectCell As Range
Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8)
SelectCell.Value = _
Application.InputBox(prompt:="Enter value", Type:=1)

This would also work:

Dim SelectCell As Range
Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8)
Worksheets("Columns").Range(SelectCell.Address).Value = _
Application.InputBox(prompt:="Enter value", Type:=1)

To see the fully qualified range address referenced by SelectCell:

Debug.Print SelectCell.Address(External:=True)

 

[Jerry McNabb]

I'm sorry about the layout of what I wrote. It was right but for some reason
got rearranged when I posted it.

This macro accepts, through two InputBoxes, a cell address and a value to be
entered into that address. It then sets the interior color to gold (44). It
then looks into a cross reference matrix, which I don't bother to declare as
a 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 second worksheet. It then does the same with a second
crossreference and the third worksheet

 

[Jerry McNabb]

I'm sorry about the layout of what I wrote. It was right but for some reason
got rearranged when I posted it.

This macro accepts, through two InputBoxes, a cell address and a value to be
entered into that address. It then sets the interior color to gold (44). It
then looks into a cross reference matrix, which I don't bother to declare as
a 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 second worksheet. It then does the same with a second
crossreference and the third worksheet