Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a Range with Cells(r,c)
I have a spreadsheet with columns that extend beyond Z. I need to
programmatically select a range, and I opted not to use COLN = "A" sheet.range( COLN & "5:" & COLN+3 & "5" ) to avoid issues beyond Z. I'm using sheet.range(cells(row,coln),cells(row,coln+3)) which seems to work in most places. In this particular case, I need to select and copy / pastespecial cells from another sheet, and I keep getting error 1004 "Method range". My code is Set R=Sheets("other").Range(cells(row,coln),cells(row, coln+3)).Copy Is the issue the use of Cells() within the Range, or the way I'm using the other sheet? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a Range with Cells(r,c)
You don't use "Set R = ..." and "....Copy" in the same command.
Just describe the range and end with copy... Sheets("other").Range(cells(row, coln), cells(row, coln+3)).Copy -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Mark Parent" wrote: I have a spreadsheet with columns that extend beyond Z. I need to programmatically select a range, and I opted not to use COLN = "A" sheet.range( COLN & "5:" & COLN+3 & "5" ) to avoid issues beyond Z. I'm using sheet.range(cells(row,coln),cells(row,coln+3)) which seems to work in most places. In this particular case, I need to select and copy / pastespecial cells from another sheet, and I keep getting error 1004 "Method range". My code is Set R=Sheets("other").Range(cells(row,coln),cells(row, coln+3)).Copy Is the issue the use of Cells() within the Range, or the way I'm using the other sheet? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a Range with Cells(r,c)
Watch out for those unqualified ranges:
Sheets("other").Range(cells(row, coln), cells(row, coln+3)).Copy The cells() will refer to the active sheet (if the code is in a general module)--and Other may not be that active sheet. I'd qualify those ranges this way: with Sheets("other") .Range(.cells(row, coln), .cells(row, coln+3)).Copy end with (the dots mean that those objects belong to the object in the previous With statement. In this case, Sheets("other").) Or Sheets("other").cells(row, coln).resize(1, 4).Copy JBeaucaire wrote: You don't use "Set R = ..." and "....Copy" in the same command. Just describe the range and end with copy... Sheets("other").Range(cells(row, coln), cells(row, coln+3)).Copy -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Mark Parent" wrote: I have a spreadsheet with columns that extend beyond Z. I need to programmatically select a range, and I opted not to use COLN = "A" sheet.range( COLN & "5:" & COLN+3 & "5" ) to avoid issues beyond Z. I'm using sheet.range(cells(row,coln),cells(row,coln+3)) which seems to work in most places. In this particular case, I need to select and copy / pastespecial cells from another sheet, and I keep getting error 1004 "Method range". My code is Set R=Sheets("other").Range(cells(row,coln),cells(row, coln+3)).Copy Is the issue the use of Cells() within the Range, or the way I'm using the other sheet? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a Range with Cells(r,c)
That's helping... but I'm still stymied...
This statement works, and copies a single cell: Sheets("other").Cells(7, 4).Copy But this statement to select a range instead of a single cell fails: Sheets("other").Range(Cells(8, 4), Cells(40, 5)).Copy I get a Method 'Range' of object 'Worksheet' failed. If I can't set a range this way instead of Range("D8:E40"), how can I define a range beyond column "Z"?? "JBeaucaire" wrote: You don't use "Set R = ..." and "....Copy" in the same command. Just describe the range and end with copy... Sheets("other").Range(cells(row, coln), cells(row, coln+3)).Copy -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Mark Parent" wrote: I have a spreadsheet with columns that extend beyond Z. I need to programmatically select a range, and I opted not to use COLN = "A" sheet.range( COLN & "5:" & COLN+3 & "5" ) to avoid issues beyond Z. I'm using sheet.range(cells(row,coln),cells(row,coln+3)) which seems to work in most places. In this particular case, I need to select and copy / pastespecial cells from another sheet, and I keep getting error 1004 "Method range". My code is Set R=Sheets("other").Range(cells(row,coln),cells(row, coln+3)).Copy Is the issue the use of Cells() within the Range, or the way I'm using the other sheet? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with defining column only from range of cells | Excel Programming | |||
Defining a range by the contents of cells? | Excel Programming | |||
Defining Range using Cells | Excel Programming | |||
Drop down defining content of a range of cells | Links and Linking in Excel | |||
Defining a variable Range for cells with values in them! | Excel Programming |