Help with run-time error

[Guest]

Hi, having some trouble here! Trying to search column B for some text and
then paste (values only) into the cell immediately below. Using the
following but clearly not working and my head is about to explode if I
continue to fumble for an answer. It's the last line that's causing me a
problem. Can anyone help please?

Thanks / Nick

ThisWorkbook.Sheets("Status Report").Select
Set rng = SH.Columns("B:B").Find(What:="Risk Description", _
After:=Range("B58"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

rng.Offset(1).PasteSpecial 'paste description column to status report

 

[aidan.heritage]

(A) It hasn't copied any data

(B) why would you want to copy data? Why not use rng.Offset(1).Value =
rng.Value


(C) it hasn't said paste special what which I think MIGHT be required
Paste:=xlPasteValues

 

[Guest]

A) Copy happens in previous line
B) ?
C) ?

getting more and more confused......


Range("F15:F" & LastYesRisk).Select
Selection.Copy
ThisWorkbook.Sheets("Status Report").Select
Set rng = SH.Columns("B:B").Find(What:="Risk Description", _
After:=Range("B58"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

rng.Offset(1).PasteSpecial 'paste description column to status report

 

[aidan.heritage]

OK, happy that the copy happens in the previous line the copy that you
are doing is F15:Fsomething.

My suggestion on B is to do away with the copy and paste

I'm ASSUMING (though I haven't run it) that the rng is returning a
single cell value - but I'm not sure you only want one co-ordinate, as
offset(1) would be one row down, not one column across.

C - again, haven't run the code, but I'm expecting that the line should
read
rng.offset(1).pastespecial :=xlPasteValues


would it help to mail me a file ([email protected])

 

[Guest]

OK, happy that the copy happens in the previous line the copy that you
are doing is F15:Fsomething.

My suggestion on B is to do away with the copy and paste

I'm ASSUMING (though I haven't run it) that the rng is returning a
single cell value - but I'm not sure you only want one co-ordinate, as
offset(1) would be one row down, not one column across.

C - again, haven't run the code, but I'm expecting that the line should
read
rng.offset(1).pastespecial :=xlPasteValues


would it help to mail me a file ([email protected])

 

[aidan.heritage]

Reply has gone back by email, trying to keep the methods you are using
the same, but for the benefit of the group my GUESS is that you haven'
t tested for a failure to find the data, and therefore are using a
NOTHING range to do things to - which excel isn't happy with - putting
in the error test that you have used elsewhere will probably get round
this.

 

[Guest]

Hi Aidan. I see your point re null range which seems to have solved the
issue. However, the paste special (values) doesn't seem to work properly now
as the destination cells are merged.... get "This Operation requires the
merged cells to be identically sized."

There's always something isn't there!

Thanks for your help.

Nick

 

[aidan.heritage]

As I may have mentioned, I don't like using copy and paste in code (not
often anyway) so would SUGGEST trying

rng.offset(1).value=rng.value

instead of the paste (which also removes the need for the copy of
course!)