Code doesn't work due to error on line: c.Address.Select

With Worksheets("Sheet2").Range("G2:G76")
Set c = .Find(F.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Worksheets("Sheet3").Range("Q2:V2").Select
Application.CutCopyMode = False
Selection.Copy
c.Address.Select
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Paste
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

I want to..

1) find where F.Value = the Value of a cell in
Worksheets("Sheet2").Range("G2:G76")
2) paste values from cells Worksheets("Sheet3").Range("Q2:V2") into columns
A through F (in the same row as the cell that was just found in .Find)

Address is a String value... you can't select a String. Try just c.Select
and see if that works for you.

--
Rick (MVP - Excel)



"MovingBeyondtheRecordButton"
.com wrote in message
...
Code doesn't work due to error on line: c.Address.Select

With Worksheets("Sheet2").Range("G2:G76")
Set c = .Find(F.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Worksheets("Sheet3").Range("Q2:V2").Select
Application.CutCopyMode = False
Selection.Copy
c.Address.Select
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Paste
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

I want to..

1) find where F.Value = the Value of a cell in
Worksheets("Sheet2").Range("G2:G76")
2) paste values from cells Worksheets("Sheet3").Range("Q2:V2") into
columns
A through F (in the same row as the cell that was just found in .Find)

You actually don't need to select a cell to copy/paste - you can just
refference them.
Also c.address retruns a string and not the cell (range) object itself
- and you can't select a string (you can select a range).
Also be careful when using ActiveSheet - be sure you really want to
paste into the ACTIVE sheet and not a specific one - e.g., the code
below would paste into Sheet3 as oppose to ActiveSheet that you had.
Amend as necessary.

Try this:

With Worksheets("Sheet2").Range("G2:G76")
Set c = .Find(F.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Worksheets("Sheet3").Range("Q2:V2").Copy destination:=
Worksheets("Sheet3").cells(c.row, "A")
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With


On Mar 29, 2:47*pm, MovingBeyondtheRecordButton
.com wrote:
Code doesn't work due to error on line: *c.Address.Select

With Worksheets("Sheet2").Range("G2:G76")
* * Set c = .Find(F.Value, LookIn:=xlValues)
* * If Not c Is Nothing Then
* * * * firstAddress = c.Address
* * * * Do
* * * * * * Worksheets("Sheet3").Range("Q2:V2").Select
* * * * * * Application.CutCopyMode = False
* * * * * * Selection.Copy
* * * * * * c.Address.Select
* * * * * * ActiveCell.Offset(0, -6).Range("A1").Select
* * * * * * ActiveSheet.Paste
* * * * * * Set c = .FindNext(c)
* * * * Loop While Not c Is Nothing And c.Address < firstAddress
* * End If
* * End With

I want to..

1) find where F.Value = the Value of a cell in
Worksheets("Sheet2").Range("G2:G76")
2) paste values from cells Worksheets("Sheet3").Range("Q2:V2") into columns
A through F (in the same row as the cell that was just found in .Find)

hi
c.address is not a selectable object. address is a string.
try
c.select or
range(c.address).select

also...seldom do you have to accually select. you can usually just reference
the ranges.
Worksheets("Sheet1").Range("Q2:V2").copy Destination:= _
c.Offset(0, -6).Range("A1")

Regards
FSt1

"MovingBeyondtheRecordButton" wrote:

Code doesn't work due to error on line: c.Address.Select

With Worksheets("Sheet2").Range("G2:G76")
Set c = .Find(F.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Worksheets("Sheet3").Range("Q2:V2").Select
Application.CutCopyMode = False
Selection.Copy
c.Address.Select
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Paste
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

I want to..

1) find where F.Value = the Value of a cell in
Worksheets("Sheet2").Range("G2:G76")
2) paste values from cells Worksheets("Sheet3").Range("Q2:V2") into columns
A through F (in the same row as the cell that was just found in .Find)

Using c.Select resulted in a run-time error '1004 Select method of range
class failed

"Rick Rothstein" wrote:

Address is a String value... you can't select a String. Try just c.Select
and see if that works for you.

--
Rick (MVP - Excel)



"MovingBeyondtheRecordButton"
.com wrote in message
...
Code doesn't work due to error on line: c.Address.Select

With Worksheets("Sheet2").Range("G2:G76")
Set c = .Find(F.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Worksheets("Sheet3").Range("Q2:V2").Select
Application.CutCopyMode = False
Selection.Copy
c.Address.Select
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Paste
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

I want to..

1) find where F.Value = the Value of a cell in
Worksheets("Sheet2").Range("G2:G76")
2) paste values from cells Worksheets("Sheet3").Range("Q2:V2") into
columns
A through F (in the same row as the cell that was just found in .Find)

.

Okay, I looked at your code a little more closely... the problem you are
having is because you are trying to select cells from a non-active
worksheet... you can't do that... and you don't have to. Look at AB's and
FSt1's responses to see how not to rely on selecting cells before you
perform operations with them.

Perhaps this previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you also...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever

you can almost always do this instead...

Range("A1").<whatever

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)



"MovingBeyondtheRecordButton"
.com wrote in message
...
Using c.Select resulted in a run-time error '1004 Select method of range
class failed

"Rick Rothstein" wrote:

Address is a String value... you can't select a String. Try just c.Select
and see if that works for you.

--
Rick (MVP - Excel)



"MovingBeyondtheRecordButton"
.com wrote in message
...
Code doesn't work due to error on line: c.Address.Select

With Worksheets("Sheet2").Range("G2:G76")
Set c = .Find(F.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Worksheets("Sheet3").Range("Q2:V2").Select
Application.CutCopyMode = False
Selection.Copy
c.Address.Select
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Paste
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

I want to..

1) find where F.Value = the Value of a cell in
Worksheets("Sheet2").Range("G2:G76")
2) paste values from cells Worksheets("Sheet3").Range("Q2:V2") into
columns
A through F (in the same row as the cell that was just found in .Find)

.

The copied cells contain formulas and I need to copy values only otherwise it
changes all the rows of data to equal the last row of data.

I tried...
Worksheets("Sheet3").Range("Q2:V2").Values.Copy
Destination:=Worksheets("Sheet2").Cells(c.Row, "A")

But...it doesn't like where I put the word Values

"AB" wrote:

You actually don't need to select a cell to copy/paste - you can just
refference them.
Also c.address retruns a string and not the cell (range) object itself
- and you can't select a string (you can select a range).
Also be careful when using ActiveSheet - be sure you really want to
paste into the ACTIVE sheet and not a specific one - e.g., the code
below would paste into Sheet3 as oppose to ActiveSheet that you had.
Amend as necessary.

Try this:

With Worksheets("Sheet2").Range("G2:G76")
Set c = .Find(F.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Worksheets("Sheet3").Range("Q2:V2").Copy destination:=
Worksheets("Sheet3").cells(c.row, "A")
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With


On Mar 29, 2:47 pm, MovingBeyondtheRecordButton
.com wrote:
Code doesn't work due to error on line: c.Address.Select

With Worksheets("Sheet2").Range("G2:G76")
Set c = .Find(F.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Worksheets("Sheet3").Range("Q2:V2").Select
Application.CutCopyMode = False
Selection.Copy
c.Address.Select
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Paste
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

I want to..

1) find where F.Value = the Value of a cell in
Worksheets("Sheet2").Range("G2:G76")
2) paste values from cells Worksheets("Sheet3").Range("Q2:V2") into columns
A through F (in the same row as the cell that was just found in .Find)

.

The "copy Destination:=" sounds like it would work but how do I tell it to
copy Destination Values only?

"FSt1" wrote:

hi
c.address is not a selectable object. address is a string.
try
c.select or
range(c.address).select

also...seldom do you have to accually select. you can usually just reference
the ranges.
Worksheets("Sheet1").Range("Q2:V2").copy Destination:= _
c.Offset(0, -6).Range("A1")

Regards
FSt1

"MovingBeyondtheRecordButton" wrote:

Code doesn't work due to error on line: c.Address.Select

With Worksheets("Sheet2").Range("G2:G76")
Set c = .Find(F.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Worksheets("Sheet3").Range("Q2:V2").Select
Application.CutCopyMode = False
Selection.Copy
c.Address.Select
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Paste
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

I want to..

1) find where F.Value = the Value of a cell in
Worksheets("Sheet2").Range("G2:G76")
2) paste values from cells Worksheets("Sheet3").Range("Q2:V2") into columns
A through F (in the same row as the cell that was just found in .Find)

Just split it up into 2 lines (and don't use the 'destination'):

Worksheets("Sheet3").Range("Q2:V2").Copy

Worksheets("Sheet2").Cells(c.Row, "A") .PasteSpecial xlPasteValues


On Mar 29, 4:16*pm, MovingBeyondtheRecordButton
.com wrote:
The "copy Destination:=" sounds like it would work but how do I tell it to
copy Destination Values only?