Range Syntax

  • Thread starter Thread starter John P
  • Start date Start date
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
 
Hi,

Try this

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

Mike
 
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
 
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.
 
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
 
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
 
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
 
It contains integer 78. The thing is that statement works well in the
immediate window.

John
 
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
 
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
 
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.
 
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
 
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.
 
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
 
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
 
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
 
Sorry I meant hardcode the range statement to:

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

not Sheets("Cycle Data").Range("CR3:CX803").Copy
 
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.
 
Back
Top