That code works fine for me as long as the value is found. If it is not,
then dCell takes a value of Nothing, so when you try to offset that, it will
fail.

You need to test that Find worked, and take appropriate action.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"cereldine" wrote
in message ...

Thats what i thought, i guess i'm not declaring the range properly, when
debugging the range variables (rng,dcell) they have text values, is this
correct, it hasn't been a problem before when i make the particular
workbook activated just before the offset code. This is currently the
method that i'm using, it works but you may be able to see what i need
to change to make it more efficent.

Range("B2").Select ' look at first value to search for
Set rng = ActiveCell 'set the procedure up to read first
value in B2
Set bk2 = Workbooks.Open(filepath1)

Do
If rng = "GVA" Then
sCode = "GDPM_1622"
Else
sCode = rng.Text
End If

Set dCell =
bk2.Worksheets("i_calcs").Cells.Find(sCode)
bk2.Worksheets("i_calcs").Activate

dCell.Offset(1, 0).Select

Range(Selection, dCell.End(xlDown)).Copy

bk1.Activate ''change eventually

rng.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Set rng = rng.Offset(1, 0)
Application.CutCopyMode = False

Loop Until rng = ""
bk2.Close
End Sub


--
cereldine
------------------------------------------------------------------------
cereldine's Profile:
http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=538000