| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Object does not exist.
Im 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.C olorIndex = clrGold €˜Line 7 Worksheets("Rows").Range(SelectCell).Interior.Colo rIndex = clrGold €˜Line 8 Worksheets("Areas").Range(SelectCell).Interior.Col orIndex = 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 doesnt 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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Object does not exist.
hi
wild guess but i would guess vb don't like your syntax. what does this macro do? regards FSt1 "Jerry McNabb" wrote: Im 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.C olorIndex = clrGold €˜Line 7 Worksheets("Rows").Range(SelectCell).Interior.Colo rIndex = clrGold €˜Line 8 Worksheets("Areas").Range(SelectCell).Interior.Col orIndex = 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 doesnt 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Object does not exist.
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).Valu e 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).Va lue = _ Application.InputBox(prompt:="Enter value", Type:=1) To see the fully qualified range address referenced by SelectCell: Debug.Print SelectCell.Address(External:=True) -- Tim Zych SF, CA "Jerry McNabb" wrote in message ... 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.C olorIndex = clrGold 'Line 7 Worksheets("Rows").Range(SelectCell).Interior.Colo rIndex = clrGold 'Line 8 Worksheets("Areas").Range(SelectCell).Interior.Col orIndex = 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Object does not exist.
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 "FSt1" wrote: hi wild guess but i would guess vb don't like your syntax. what does this macro do? regards FSt1 "Jerry McNabb" wrote: Im 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.C olorIndex = clrGold €˜Line 7 Worksheets("Rows").Range(SelectCell).Interior.Colo rIndex = clrGold €˜Line 8 Worksheets("Areas").Range(SelectCell).Interior.Col orIndex = 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 doesnt 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Object does not exist.
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 "Tim Zych" wrote: 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).Valu e 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).Va lue = _ Application.InputBox(prompt:="Enter value", Type:=1) To see the fully qualified range address referenced by SelectCell: Debug.Print SelectCell.Address(External:=True) -- Tim Zych SF, CA "Jerry McNabb" wrote in message ... 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.C olorIndex = clrGold 'Line 7 Worksheets("Rows").Range(SelectCell).Interior.Colo rIndex = clrGold 'Line 8 Worksheets("Areas").Range(SelectCell).Interior.Col orIndex = 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| looking for links that do not exist | Excel Worksheet Functions | |||
| Exist or Not. | Excel Discussion (Misc queries) | |||
| Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
| Exist a function to... | Excel Worksheet Functions | |||
| Am I Looking For Something That Doesn't Exist? | Excel Discussion (Misc queries) | |||
Linear Mode
