Set CopyRange Error

  • Thread starter Thread starter Ty
  • Start date Start date
T

Ty

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?
 
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...
 
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 linesin
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...










--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

The code is in the "VLOOKUP Insert & Copy" post. I think I have
confused my Mentor/MVP.
 
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)
Click to expand...

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)
 
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?).


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)







- Show quoted text -
Click to expand...

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.
 
Back
Top