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

(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

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

" wrote:

(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

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 )

on it's way!

" wrote:

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 )

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.