Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CopyRange Error
I have another post but did not want to add more confusion with just
one line that I'm trying to resolve and understand. This line is giving me an error when I try to execute: Set CopyRange = _ .Range("C" & c.Row & ":H" & c.Row) CopyRange.Copy Destination:=Sheets("Sheet2").Range("C" & RowCount) I googled it and found the following: Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) Is it the "_" underscore and "." period after the equal sign causing the line to error? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CopyRange Error
There were more line wraps in the code you posted.
You actually have two logical lines in what you posted: Set CopyRange = .Range("C" & c.Row & ":H" & c.Row) and CopyRange.Copy Destination:=Sheets("Sheet2").Range("C" & RowCount) I'm not sure what the problem you're having is--or what the missing lines in your code do. But maybe something like: with activesheet ' or with worksheets("Sheet9999") '<--change the name Set CopyRange = .Range("C" & c.Row & ":H" & c.Row) end with copyrange.copy _ destination:=Sheets("Sheet2").Range("C" & RowCount) (I like the .copy and destination:= on two different physical lines--even though they're part of a single logical line.) But all this depends on what the error is--and that could depend on what c is and rowcount is... Ty wrote: I have another post but did not want to add more confusion with just one line that I'm trying to resolve and understand. This line is giving me an error when I try to execute: Set CopyRange = _ .Range("C" & c.Row & ":H" & c.Row) CopyRange.Copy Destination:=Sheets("Sheet2").Range("C" & RowCount) I googled it and found the following: Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) Is it the "_" underscore and "." period after the equal sign causing the line to error? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CopyRange Error
On Aug 12, 11:14*am, Dave Peterson wrote:
There were more line wraps in the code you posted. You actually have two logical lines in what you posted: Set CopyRange = .Range("C" & c.Row & ":H" & c.Row) and CopyRange.Copy Destination:=Sheets("Sheet2").Range("C" & RowCount) I'm not sure what the problem you're having is--or what the missing lines in your code do. But maybe something like: with activesheet ' or with worksheets("Sheet9999") '<--change the name * *Set CopyRange = .Range("C" & c.Row & ":H" & c.Row) end with copyrange.copy _ * *destination:=Sheets("Sheet2").Range("C" & RowCount) (I like the .copy and destination:= on two different physical lines--even though they're part of a single logical line.) But all this depends on what the error is--and that could depend on what c is and rowcount is... Ty wrote: I have another post but did not want to add more confusion with just one line that I'm trying to resolve and understand. *This line is giving me an error when I try to execute: Set CopyRange = _ * * * * * * * * * .Range("C" & c.Row & ":H" & c.Row) * * * * * * * *CopyRange.Copy Destination:=Sheets("Sheet2").Range("C" & RowCount) I googled it and found the following: Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) Is it the "_" underscore and "." period after the equal sign causing the line to error? -- Dave Peterson- Hide quoted text - - Show quoted text - The code is in the "VLOOKUP Insert & Copy" post. I think I have confused my Mentor/MVP. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CopyRange Error
There are several problems with the code. First, there is no With
statement that would allow the period before the ".Range("C".... line of code. Perhaps you just didn't include it in the code you posted. The second problem is that the line starting with "CopyRange.Copy" got split into two lines. This may be merely a by-product of the newsgroup posted text and not really a problem in your code. As far as the With statement goes, you need an object of which the elements that begin with a period are children. For example, With Worksheets("Sheet1") .Range("A1").Value = 1234 .Range("B1").Value = 4321 End With In this example, Worksheets("Sheet1") is the target of the With statement and all statements below that (until the End With is reached) that begin with a period refer to the target of the With statement. The two .Range statement, since they begin with a period, refer to Worksheets("Sheet1"), regardless of what sheet happens to be active. Without the periods, the Ranges would point to the active sheet. In other words, the code above is functionally equivalent to Worksheets("Sheet1").Range("A1").Value = 1234 Worksheets("Sheet1").Range("B1").Value = 4321 A With statement makes the code easier to write, understand, debug, and makes it execute marginally faster. The underscore character (actually, a space followed by an underscore at the end of a line) is a line continuation character that allows you to write code which is really one logical line of code over two or more lines of text in the editor. For example, Set R = Range("A1:A10").Find(what:="abc", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows) can be written on a single line of code in the editor (it got wrapped in this newsgroup post, but really is on a single line in the editor). However, for readability, you could write the same code as Set R = Range("A1:A10").Find( _ what:="abc", _ LookIn:=xlValues, _ lookat:=xlPart, _ searchorder:=xlByRows) This is more readable. The <space<underscore at the end of each line has no effect on the operation of the code, but merely tells the VBA compiler that the code continues on the next line in the editor. There is a limit to the number of line continuations you can have in a single logical line of code, but I forget what that is (7 perhaps?). Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) That is no good for a variety of reasons. First, "Row Set" is invalid syntax and will cause a compiler error. Next, the code that begins with "CopyRange.Copy" should be on its own line in the editor. If you modify it to Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) it should work fine. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 12 Aug 2009 08:40:37 -0700 (PDT), Ty wrote: I have another post but did not want to add more confusion with just one line that I'm trying to resolve and understand. This line is giving me an error when I try to execute: Set CopyRange = _ .Range("C" & c.Row & ":H" & c.Row) CopyRange.Copy Destination:=Sheets("Sheet2").Range("C" & RowCount) I googled it and found the following: Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) Is it the "_" underscore and "." period after the equal sign causing the line to error? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CopyRange Error
On Aug 12, 6:08*pm, Chip Pearson wrote:
There are several problems with the code. First, there is no With statement that would allow the period before the ".Range("C".... line of code. Perhaps you just didn't include it in the code you posted. The second problem is that the line starting with "CopyRange.Copy" got split into two lines. This may be merely a by-product of the newsgroup posted text and not really a problem in your code. * As far as the With statement goes, you need an object of which the elements that begin with a period are children. *For example, With Worksheets("Sheet1") * * .Range("A1").Value = 1234 * * .Range("B1").Value = 4321 End With In this example, Worksheets("Sheet1") is the target of the With statement and all statements below that (until the End With is reached) that begin with a period refer to the target of the With statement. *The two .Range statement, since they begin with a period, refer to Worksheets("Sheet1"), regardless of what sheet happens to be active. Without the periods, the Ranges would point to the active sheet. In other words, the code above is functionally equivalent to Worksheets("Sheet1").Range("A1").Value = 1234 Worksheets("Sheet1").Range("B1").Value = 4321 A With statement makes the code easier to write, understand, debug, and makes it execute marginally faster. The underscore character (actually, a space followed by an underscore at the end of a line) is a line continuation character that allows you to write code which is really one logical line of code over two or more lines of text in the editor. For example, Set R = Range("A1:A10").Find(what:="abc", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows) can be written on a single line of code in the editor (it got wrapped in this newsgroup post, but really is on a single line in the editor). However, for readability, you could write the same code as Set R = Range("A1:A10").Find( _ * * what:="abc", _ * * LookIn:=xlValues, _ * * lookat:=xlPart, _ * * searchorder:=xlByRows) This is more readable. The <space<underscore at the end of each line has no effect on the operation of the code, but merely tells the VBA compiler that the code continues on the next line in the editor. There is a limit to the number of line continuations you can have in a single logical line of code, but I forget what that is *(7 perhaps?). Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) That is no good for a variety of reasons. First, "Row Set" is invalid syntax and will cause a compiler error. Next, the code that begins with "CopyRange.Copy" should be on its own line in the editor. If you modify it to Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) it should work fine. Cordially, Chip Pearson Microsoft Most Valuable Professional * * Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLCwww.cpearson.com (email on web site) On Wed, 12 Aug 2009 08:40:37 -0700 (PDT), Ty wrote: I have another post but did not want to add more confusion with just one line that I'm trying to resolve and understand. *This line is giving me an error when I try to execute: Set CopyRange = _ * * * * * * * * *.Range("C" & c.Row & ":H" & c.Row) * * * * * * * CopyRange.Copy Destination:=Sheets("Sheet2").Range("C" & RowCount) I googled it and found the following: Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) Is it the "_" underscore and "." period after the equal sign causing the line to error?- Hide quoted text - - Show quoted text - There is an Error on the the first line. When I place the cursor over the Set part it says "copy range is empty". When place the cursor over the Range it says "Method 'Range' of object '_global' failed". Maybe we need to add the With Sheet 3 line. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error handling error # 1004 Run-time error | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |