Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range from an Offset for deletion - Help please
I have the following code in my macro but get a RunTime error 1004 where
indicated. I would much appreciate guidance on how to rectify this. Thanks in advance. Sub Test() Dim RowValue As Integer Dim ColumnValue As Integer Dim TradeAttribute As Integer 'ActiveCell is on Sheets("Control") RowValue = ActiveCell.Row ColumnValue = ActiveCell.Column TradeAttribute = Sheets("Control").Cells(11, ColumnValue) 'When I run the macro I get Run Time error 1004:Select method of Range Class failed ' and the line of code below is highlighted. Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, TradeAttribute).Select 'I want to select a range of 497 cells below the cell selected on Sheets("Detail") for deletion Range("A1:A497").Select Selection.ClearContents End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range from an Offset for deletion - Help please
You need to add
Sheets("Detail").Select prior to doing anything on that sheet. So add that line just prior to Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, _ TradeAttribute).Select -- HTH... Jim Thomlinson "MichaelC" wrote: I have the following code in my macro but get a RunTime error 1004 where indicated. I would much appreciate guidance on how to rectify this. Thanks in advance. Sub Test() Dim RowValue As Integer Dim ColumnValue As Integer Dim TradeAttribute As Integer 'ActiveCell is on Sheets("Control") RowValue = ActiveCell.Row ColumnValue = ActiveCell.Column TradeAttribute = Sheets("Control").Cells(11, ColumnValue) 'When I run the macro I get Run Time error 1004:Select method of Range Class failed ' and the line of code below is highlighted. Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, TradeAttribute).Select 'I want to select a range of 497 cells below the cell selected on Sheets("Detail") for deletion Range("A1:A497").Select Selection.ClearContents End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range from an Offset for deletion - Help please
Thank you Jim. That fixed the error message.
But I wanted the range selection to be Relative to the Offset cell selected (dependent on the RowValue and the TradeAttribute). Instead it selected the actual A1:A497 Range and deleted that. How can I correct the instruction? Thank you. (Great day here in Vancouver, isn't it!) "Jim Thomlinson" wrote: You need to add Sheets("Detail").Select prior to doing anything on that sheet. So add that line just prior to Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, _ TradeAttribute).Select -- HTH... Jim Thomlinson "MichaelC" wrote: I have the following code in my macro but get a RunTime error 1004 where indicated. I would much appreciate guidance on how to rectify this. Thanks in advance. Sub Test() Dim RowValue As Integer Dim ColumnValue As Integer Dim TradeAttribute As Integer 'ActiveCell is on Sheets("Control") RowValue = ActiveCell.Row ColumnValue = ActiveCell.Column TradeAttribute = Sheets("Control").Cells(11, ColumnValue) 'When I run the macro I get Run Time error 1004:Select method of Range Class failed ' and the line of code below is highlighted. Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, TradeAttribute).Select 'I want to select a range of 497 cells below the cell selected on Sheets("Detail") for deletion Range("A1:A497").Select Selection.ClearContents End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range from an Offset for deletion - Help please
Another fine day on the wet coast... It is hard to tell from your code
exactly what you are doing. Do you want to delete or just clear contents? The value of trade attribute, what is that? Where is range VarInput? Why are you subtracting 12 and multiplying by 500? All of this can probably be accomplished in just a few lines of code by avoiding the selections, similar to this... Sub Test() Sheets("Detail").Range("VarInput").Offset((Activec ell.Row - 12) * 500, _ Sheets("Control").Cells(11, Activecell.Column)).ClearContents End Sub instead of .... Sub Test() Dim RowValue As Integer Dim ColumnValue As Integer Dim TradeAttribute As Integer 'ActiveCell is on Sheets("Control") RowValue = ActiveCell.Row ColumnValue = ActiveCell.Column TradeAttribute = Sheets("Control").Cells(11, ColumnValue) 'When I run the macro I get Run Time error 1004:Select method of Range Class failed ' and the line of code below is highlighted. Sheets("Detail").Select Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, TradeAttribute).Select 'I want to select a range of 497 cells below the cell selected on Sheets("Detail") for deletion 'Range("A1:A497").Select Selection.ClearContents End Sub -- HTH... Jim Thomlinson "MichaelC" wrote: Thank you Jim. That fixed the error message. But I wanted the range selection to be Relative to the Offset cell selected (dependent on the RowValue and the TradeAttribute). Instead it selected the actual A1:A497 Range and deleted that. How can I correct the instruction? Thank you. (Great day here in Vancouver, isn't it!) "Jim Thomlinson" wrote: You need to add Sheets("Detail").Select prior to doing anything on that sheet. So add that line just prior to Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, _ TradeAttribute).Select -- HTH... Jim Thomlinson "MichaelC" wrote: I have the following code in my macro but get a RunTime error 1004 where indicated. I would much appreciate guidance on how to rectify this. Thanks in advance. Sub Test() Dim RowValue As Integer Dim ColumnValue As Integer Dim TradeAttribute As Integer 'ActiveCell is on Sheets("Control") RowValue = ActiveCell.Row ColumnValue = ActiveCell.Column TradeAttribute = Sheets("Control").Cells(11, ColumnValue) 'When I run the macro I get Run Time error 1004:Select method of Range Class failed ' and the line of code below is highlighted. Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, TradeAttribute).Select 'I want to select a range of 497 cells below the cell selected on Sheets("Detail") for deletion Range("A1:A497").Select Selection.ClearContents End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range from an Offset for deletion - Help please
Many thanks Jim. Your hassistance, coupled with your reply to SusanL helped
me figure this one out. In case anyone else is interested, here is the code I used although I am sure it is clumsy and could be much improved! What it is is a small control range of 10 x 12 input variables on the Control worksheet Each of which is changed, through iteration, to 500 values and outcomes on a large 5,000 x 25 worksheet called "Detail" So what I needed was a mechanism to identify the starting point in the Detail Sheet so I could clear its columns before starting a new iteration. (Yes this was necessary because the outcomes are graphed and it is helpful to observe the live updating of the chart) Michael "Jim Thomlinson" wrote: Another fine day on the wet coast... It is hard to tell from your code exactly what you are doing. Do you want to delete or just clear contents? The value of trade attribute, what is that? Where is range VarInput? Why are you subtracting 12 and multiplying by 500? All of this can probably be accomplished in just a few lines of code by avoiding the selections, similar to this... Sub Test() Sheets("Detail").Range("VarInput").Offset((Activec ell.Row - 12) * 500, _ Sheets("Control").Cells(11, Activecell.Column)).ClearContents End Sub instead of .... Sub Test() Dim RowValue As Integer Dim ColumnValue As Integer Dim TradeAttribute As Integer 'ActiveCell is on Sheets("Control") RowValue = ActiveCell.Row ColumnValue = ActiveCell.Column TradeAttribute = Sheets("Control").Cells(11, ColumnValue) 'When I run the macro I get Run Time error 1004:Select method of Range Class failed ' and the line of code below is highlighted. Sheets("Detail").Select Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, TradeAttribute).Select 'I want to select a range of 497 cells below the cell selected on Sheets("Detail") for deletion 'Range("A1:A497").Select Selection.ClearContents End Sub -- HTH... Jim Thomlinson "MichaelC" wrote: Thank you Jim. That fixed the error message. But I wanted the range selection to be Relative to the Offset cell selected (dependent on the RowValue and the TradeAttribute). Instead it selected the actual A1:A497 Range and deleted that. How can I correct the instruction? Thank you. (Great day here in Vancouver, isn't it!) "Jim Thomlinson" wrote: You need to add Sheets("Detail").Select prior to doing anything on that sheet. So add that line just prior to Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, _ TradeAttribute).Select -- HTH... Jim Thomlinson "MichaelC" wrote: I have the following code in my macro but get a RunTime error 1004 where indicated. I would much appreciate guidance on how to rectify this. Thanks in advance. Sub Test() Dim RowValue As Integer Dim ColumnValue As Integer Dim TradeAttribute As Integer 'ActiveCell is on Sheets("Control") RowValue = ActiveCell.Row ColumnValue = ActiveCell.Column TradeAttribute = Sheets("Control").Cells(11, ColumnValue) 'When I run the macro I get Run Time error 1004:Select method of Range Class failed ' and the line of code below is highlighted. Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, TradeAttribute).Select 'I want to select a range of 497 cells below the cell selected on Sheets("Detail") for deletion Range("A1:A497").Select Selection.ClearContents End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range from an Offset for deletion - Help please
Glad to help...
-- HTH... Jim Thomlinson "MichaelC" wrote: Many thanks Jim. Your hassistance, coupled with your reply to SusanL helped me figure this one out. In case anyone else is interested, here is the code I used although I am sure it is clumsy and could be much improved! What it is is a small control range of 10 x 12 input variables on the Control worksheet Each of which is changed, through iteration, to 500 values and outcomes on a large 5,000 x 25 worksheet called "Detail" So what I needed was a mechanism to identify the starting point in the Detail Sheet so I could clear its columns before starting a new iteration. (Yes this was necessary because the outcomes are graphed and it is helpful to observe the live updating of the chart) Michael "Jim Thomlinson" wrote: Another fine day on the wet coast... It is hard to tell from your code exactly what you are doing. Do you want to delete or just clear contents? The value of trade attribute, what is that? Where is range VarInput? Why are you subtracting 12 and multiplying by 500? All of this can probably be accomplished in just a few lines of code by avoiding the selections, similar to this... Sub Test() Sheets("Detail").Range("VarInput").Offset((Activec ell.Row - 12) * 500, _ Sheets("Control").Cells(11, Activecell.Column)).ClearContents End Sub instead of .... Sub Test() Dim RowValue As Integer Dim ColumnValue As Integer Dim TradeAttribute As Integer 'ActiveCell is on Sheets("Control") RowValue = ActiveCell.Row ColumnValue = ActiveCell.Column TradeAttribute = Sheets("Control").Cells(11, ColumnValue) 'When I run the macro I get Run Time error 1004:Select method of Range Class failed ' and the line of code below is highlighted. Sheets("Detail").Select Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, TradeAttribute).Select 'I want to select a range of 497 cells below the cell selected on Sheets("Detail") for deletion 'Range("A1:A497").Select Selection.ClearContents End Sub -- HTH... Jim Thomlinson "MichaelC" wrote: Thank you Jim. That fixed the error message. But I wanted the range selection to be Relative to the Offset cell selected (dependent on the RowValue and the TradeAttribute). Instead it selected the actual A1:A497 Range and deleted that. How can I correct the instruction? Thank you. (Great day here in Vancouver, isn't it!) "Jim Thomlinson" wrote: You need to add Sheets("Detail").Select prior to doing anything on that sheet. So add that line just prior to Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, _ TradeAttribute).Select -- HTH... Jim Thomlinson "MichaelC" wrote: I have the following code in my macro but get a RunTime error 1004 where indicated. I would much appreciate guidance on how to rectify this. Thanks in advance. Sub Test() Dim RowValue As Integer Dim ColumnValue As Integer Dim TradeAttribute As Integer 'ActiveCell is on Sheets("Control") RowValue = ActiveCell.Row ColumnValue = ActiveCell.Column TradeAttribute = Sheets("Control").Cells(11, ColumnValue) 'When I run the macro I get Run Time error 1004:Select method of Range Class failed ' and the line of code below is highlighted. Sheets("Detail").Range("VarInput").Offset((RowValu e - 12) * 500, TradeAttribute).Select 'I want to select a range of 497 cells below the cell selected on Sheets("Detail") for deletion Range("A1:A497").Select Selection.ClearContents End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting objects for deletion in 2007 | Excel Discussion (Misc queries) | |||
Selecting offset cells | Excel Discussion (Misc queries) | |||
Selecting a range offset from a cell | Excel Programming | |||
Range deletion | Excel Programming | |||
Selecting cell using range.offset | Excel Programming |