Hi. Appreciate if anyone can point out the error in the follwing statements
which work in the immediate window but keep turning up runtime error 1004 on
the second statement when I run the program:

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Range("CR2")).Select

Note that cell CR2 contains an integer. Thanks

Hi,

Try this

Dim MyRange As Range
Set MyRange = Range("CR3:CX3").Resize(Range("CR2").Value)
MyRange.Select

Mike

"John P" wrote:

Hi. Appreciate if anyone can point out the error in the follwing statements
which work in the immediate window but keep turning up runtime error 1004 on
the second statement when I run the program:

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Range("CR2")).Select

Note that cell CR2 contains an integer. Thanks

Activate the sheet and try the second line alone in your immediate window;
which should return an error. Please refer the help on Resize. Resize expects
a the rowsize and columnsize to be passed.

expression.Resize(RowSize, ColumnSize)

If this post helps click Yes
---------------
Jacob Skaria


"John P" wrote:

Hi. Appreciate if anyone can point out the error in the follwing statements
which work in the immediate window but keep turning up runtime error 1004 on
the second statement when I run the program:

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Range("CR2")).Select

Note that cell CR2 contains an integer. Thanks

Do you mean

Range("CR3:CX3","CR2").Select

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Activate the sheet and try the second line alone in your immediate window;
which should return an error. Please refer the help on Resize. Resize expects
a the rowsize and columnsize to be passed.

expression.Resize(RowSize, ColumnSize)

If this post helps click Yes
---------------
Jacob Skaria


"John P" wrote:

Hi. Appreciate if anyone can point out the error in the follwing statements
which work in the immediate window but keep turning up runtime error 1004 on
the second statement when I run the program:

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Range("CR2")).Select

Note that cell CR2 contains an integer. Thanks

No. I want to increase the number of rows in Range("CR3:CX3") by an integer
located in Range("CR2"). Hence, Range("CR3: CX3").Resize(Range("CR2"))

Reference book says for resizing rows only, can omit column parameter.
Program is to copy a range in Sheet1 and paste to sheet2. Code is in a
commandbutton embedded in Sheet2. Thanks.

"Jacob Skaria" wrote:

Do you mean

Range("CR3:CX3","CR2").Select

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Activate the sheet and try the second line alone in your immediate window;
which should return an error. Please refer the help on Resize. Resize expects
a the rowsize and columnsize to be passed.

expression.Resize(RowSize, ColumnSize)

If this post helps click Yes
---------------
Jacob Skaria


"John P" wrote:

Hi. Appreciate if anyone can point out the error in the follwing statements
which work in the immediate window but keep turning up runtime error 1004 on
the second statement when I run the program:

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Range("CR2")).Select

Note that cell CR2 contains an integer. Thanks

Apologies.. I didnt notice "CR2 contains an integer"

With your code make sure CR2 of Sheets("Cycle Data") contains a value...If
it is blank or 0 then this will return error...

OR

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Sheets("<sheetname").Rang e("CR2")).Select


If this post helps click Yes
---------------
Jacob Skaria


"John P" wrote:

No. I want to increase the number of rows in Range("CR3:CX3") by an integer
located in Range("CR2"). Hence, Range("CR3: CX3").Resize(Range("CR2"))

Reference book says for resizing rows only, can omit column parameter.
Program is to copy a range in Sheet1 and paste to sheet2. Code is in a
commandbutton embedded in Sheet2. Thanks.

"Jacob Skaria" wrote:

Do you mean

Range("CR3:CX3","CR2").Select

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Activate the sheet and try the second line alone in your immediate window;
which should return an error. Please refer the help on Resize. Resize expects
a the rowsize and columnsize to be passed.

expression.Resize(RowSize, ColumnSize)

If this post helps click Yes
---------------
Jacob Skaria


"John P" wrote:

Hi. Appreciate if anyone can point out the error in the follwing statements
which work in the immediate window but keep turning up runtime error 1004 on
the second statement when I run the program:

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Range("CR2")).Select

Note that cell CR2 contains an integer. Thanks

It contains integer 78. The thing is that statement works well in the
immediate window.

John

"Jacob Skaria" wrote:

Apologies.. I didnt notice "CR2 contains an integer"

With your code make sure CR2 of Sheets("Cycle Data") contains a value...If
it is blank or 0 then this will return error...

OR

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Sheets("<sheetname").Rang e("CR2")).Select


If this post helps click Yes
---------------
Jacob Skaria


"John P" wrote:

No. I want to increase the number of rows in Range("CR3:CX3") by an integer
located in Range("CR2"). Hence, Range("CR3: CX3").Resize(Range("CR2"))

Reference book says for resizing rows only, can omit column parameter.
Program is to copy a range in Sheet1 and paste to sheet2. Code is in a
commandbutton embedded in Sheet2. Thanks.

"Jacob Skaria" wrote:

Do you mean

Range("CR3:CX3","CR2").Select

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Activate the sheet and try the second line alone in your immediate window;
which should return an error. Please refer the help on Resize. Resize expects
a the rowsize and columnsize to be passed.

expression.Resize(RowSize, ColumnSize)

If this post helps click Yes
---------------
Jacob Skaria


"John P" wrote:

Hi. Appreciate if anyone can point out the error in the follwing statements
which work in the immediate window but keep turning up runtime error 1004 on
the second statement when I run the program:

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Range("CR2")).Select

Note that cell CR2 contains an integer. Thanks

Hi Jacob. Used your range statement but still same runtime error on the range
statement.

"Jacob Skaria" wrote:

Apologies.. I didnt notice "CR2 contains an integer"

With your code make sure CR2 of Sheets("Cycle Data") contains a value...If
it is blank or 0 then this will return error...

OR

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Sheets("<sheetname").Rang e("CR2")).Select


If this post helps click Yes
---------------
Jacob Skaria


"John P" wrote:

No. I want to increase the number of rows in Range("CR3:CX3") by an integer
located in Range("CR2"). Hence, Range("CR3: CX3").Resize(Range("CR2"))

Reference book says for resizing rows only, can omit column parameter.
Program is to copy a range in Sheet1 and paste to sheet2. Code is in a
commandbutton embedded in Sheet2. Thanks.

"Jacob Skaria" wrote:

Do you mean

Range("CR3:CX3","CR2").Select

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Activate the sheet and try the second line alone in your immediate window;
which should return an error. Please refer the help on Resize. Resize expects
a the rowsize and columnsize to be passed.

expression.Resize(RowSize, ColumnSize)

If this post helps click Yes
---------------
Jacob Skaria


"John P" wrote:

Hi. Appreciate if anyone can point out the error in the follwing statements
which work in the immediate window but keep turning up runtime error 1004 on
the second statement when I run the program:

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Range("CR2")).Select

Note that cell CR2 contains an integer. Thanks