Range Syntax

J

John P

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
 
M

Mike H

Hi,

Try this

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

Mike
 
J

Jacob Skaria

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
 
J

John P

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.
 
M

Mike H

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"))
Click to expand...


Did you try my suggestion?

Mike
 
J

Jacob Skaria

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>").Range("CR2")).Select


If this post helps click Yes
 
J

John P

Yes. Same runtime error on your Range statement. I now suspect problem may
not be range syntax but other coding errors. Pasting my codes for you to
comment.

Private Sub cmdUpdate_Click()
'Copy and Paste Data
Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Range("CR2")).Select

Selection.Copy
Sheets("MCs").Select
Range("A2:G2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.PasteSpecial Paste:=xlPasteValues

End Sub


Probably other errors down the code but I am stuck on the second statement.
Note that code is in commandbutton in Sheet("MCs") and trying to copy a
range in Sheet("Cycle Data"). Thanks
 
J

John P

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

John
 
M

Mike H

Maybe this

Sheets("MCs").Select
Sheets("MCs").Range("A2:G2", Range("a2:G2").End(xlDown)).ClearContents
Sheets("Cycle Data").Select
Sheets("Cycle Data").Range("CR3:CX3").Resize(Range("CR2")).Copy
Sheets("MCs").Range("A2").PasteSpecial Paste:=xlPasteValues

Mike
 
S

Sam Wilson

Might be because you're trying to paste 7 columns by 78 rows into a different
sized space.

Rather than

Selection.PasteSpecial Paste:=xlPasteValues

Try

Range("A2").PasteSpecial Paste:=xlPasteValues

Sam
 
J

John P

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

John P

Mike, almost worked until the 4th statement ie the same range statement again
when the runtime error appears again. Thanks for taking so much of your time.
 
J

Jacob Skaria

John

The reason for error is that when you ClearContents the Copy will be
lost..Rearrange the code to clear contents before OR you can try out a better
way without selecting any sheets as below..

Sub Macro2()
Dim intResize As Integer, varRange As Variant
intResize = Sheets("Cycle Data").Range("CR2").Value
varRange = Sheets("Cycle Data").Range("CR3:CX3").Resize(intResize)
Sheets("MCs").Range("A2:G2").Resize(intResize).ClearContents
Sheets("MCs").Range("A2:G2").Resize(intResize) = varRange
End Sub

If this post helps click Yes
 
J

John P

Thanks Sam. Mike has given the same suggestion but it is the selecting the
range to copy that is giving the problem even before the copy and paste.
 
J

John P

Mike has rearranged my code to ClearContents first but it still encounter the
same problem when selecting the range to copy. I will try your suggested
code. Thanks
 
J

John P

Mike, your code works if I hardcode the 4th range statement to:
Sheets("Cycle Data").Range("CR3:CX803").Copy
from your:
Sheets("Cycle Data").Range("CR3:CX3").Resize(Range("CR2")).Copy

So the problem is in the range selection code. Somehow the above
Range/Resize statement works in the immediate window but not in the program.
Is there a way to create a sub-expression in the Range statement to work this
problem around? My failed attempt is this:

Sheets("Cycle
Data").Range("CR3",Range("CX3").Offset(Range("CR2",0)).Copy

Thanks
 
S

Sam Wilson

Hi John,

If you change

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

to

Range("CR3:CX3").Resize(Range("CR2")).interior.colorindex = 15

the macro will fail (obviously) but you should see a grey patch on the area
you want to copy. Can you try that and make sure it's selecting what you
think it ought to?

Sam
 
J

John P

Sorry I meant hardcode the range statement to:

Sheets("Cycle Data").Range("CR3:CX80").Copy

not Sheets("Cycle Data").Range("CR3:CX803").Copy
 
J

John P

Here is the code that works:

Private Sub cmdUpdate_Click()
Dim x As Integer
x = Sheets("Cycle Data").Range("CR2").Value
Sheets("MCs").Select
Sheets("MCs").Range("A2:G2", Range("A2:G2").End(xlDown)).ClearContents
Sheets("Cycle Data").Select
Sheets("Cycle Data").Range("CR3:CX3").Resize(x).Copy
Sheets("MCs").Range("A2").PasteSpecial Paste:=xlPasteValues

End Sub

By using a variable as suggested by Jacob to store cell CR2, the above code
from Mike works. Do not understand why this statement:
Sheets("Cycle Data").Range("CR3:CX3").Resize(Range("CR2")).Copy works in
immediate window but not in the program. Always thought that immediate window
is my Excel formula evaluator. Thanks for input from Mike and Jacob and Sam.
 
Top