I never use the .formula method but I expect you need to change the last one
to
Offset(0, 1).Formula =
"=Vlookup(indirect("B"&target.row),Sheet2!$A$1:$B$ 7,2,False)"
But how will the worksheet know about "target row"
Why not try

Offset(0, 1).Formula = "=Vlookup(indirect("B" &
ROW()-2),Sheet2!$A$1:$B$7,2,False)"

I have not tested this!
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"JMay" wrote in message
...
I always BOMB when trying to UNDERSTAND How to place the " 's and the & 's
when stringing together say line 3 below. Can someone assist?

With Target
.Offset(0, -1).Formula = "=row()-2"
.Offset(0, 1).Formula =
"=Vlookup(B"&target.row,Sheet2!$A$1:$B$7,2,Fal se)"
End With

TIA,

Jim