| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Syntax
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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")) Did you try my suggestion? Mike "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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 "Mike H" 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")) Did you try my suggestion? Mike "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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 "John P" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 "John P" wrote: 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 "Mike H" 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")) Did you try my suggestion? Mike "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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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. "Mike H" wrote: 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 "John P" wrote: 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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).Cle arContents Sheets("MCs").Range("A2:G2").Resize(intResize) = varRange End Sub If this post helps click Yes --------------- Jacob Skaria "John P" wrote: 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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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. "Sam Wilson" wrote: 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 "John P" wrote: 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 "Mike H" 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")) Did you try my suggestion? Mike "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 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 "Jacob Skaria" wrote: 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).Cle arContents Sheets("MCs").Range("A2:G2").Resize(intResize) = varRange End Sub If this post helps click Yes --------------- Jacob Skaria "John P" wrote: 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 |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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 "Mike H" wrote: 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 "John P" wrote: 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 |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
Hi John, If you change Range("CR3:CX3").Resize(Range("CR2")).Select to Range("CR3:CX3").Resize(Range("CR2")).interior.col orindex = 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 "John P" wrote: 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. "Sam Wilson" wrote: 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 "John P" wrote: 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 "Mike H" 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")) Did you try my suggestion? Mike "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 |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
Sorry I meant hardcode the range statement to: Sheets("Cycle Data").Range("CR3:CX80").Copy not Sheets("Cycle Data").Range("CR3:CX803").Copy "Mike H" wrote: 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 "John P" wrote: 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 |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Syntax
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. "Jacob Skaria" wrote: 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).Cle arContents Sheets("MCs").Range("A2:G2").Resize(intResize) = varRange End Sub If this post helps click Yes --------------- Jacob Skaria "John P" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Relative range syntax | Excel Programming | |||
| syntax - range name as a variable | Excel Programming | |||
| Syntax help with range | Excel Programming | |||
| Syntax to Set variable range | Excel Programming | |||
| Range syntax | Excel Programming | |||
![John P[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
