Selecting a Range from an Offset for deletion - Help please

[MichaelC]

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((RowValue - 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

 

[Jim Thomlinson]

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((RowValue - 12) * 500, _
TradeAttribute).Select

 

[MichaelC]

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]

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((Activecell.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((RowValue - 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

 

[MichaelC]

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]

Glad to help...
--
HTH...

Jim Thomlinson

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