VBA VLOOKUPとセルのコピーができません

質問

助けてください

マクロでVLOOKUPを使い、データを持ってこようとしてますが上手くいきません

やりたいこと：Sheet3(在庫を貼付）にある4列目の数値を、Sheet1のG列で照合し、Sheet1の9列目に持ってきたい

Sheet1：cells(6,9)にVLOOKUPの式を入れる　関数で言えば=VLOOKUP($G,Sheet3!C:F,4,FALSE)

cells(6,9)をcells(7,9)からcells(2000,9)までコピー

これを実行すると
「アプリケーション定義またはオブジェクト定義のエラーです。」
とでて止まってしまいます

ActivesheetsやWorkbooks、WorksheetsやSheetsで設定したりしてもエラーは変わりますが
止まってることには変わりありません

どうしたら動くようになりますか？

Sub vlook()
Dim Nname As String

macx = "ルーチンワーク20130223.xlsm" 'マクロ用ファイル「ルーチンワーク20130223.xlsm」がある
Sheets(1).Select
Workbooks(macx).Activate
Worksheets(1).Activate
ddir = Cells(33, 2) 'ｃｅｌｌｓ(33,2)にフォルダ情報がある
Nname = Dir(ddir & "在庫推移.xlsx") '在庫推移.xlsxというVLOOKUPしたいファイルがある
Workbooks.Open Filename:=ddir & Nname
Workbooks(Nname).Activate
Sheets(1).Select
Worksheets(1).Activate

'VLOOKUPで在庫を持ってくる

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC7,'在庫を貼付'!C[-6]:C[-3],4,FALSE)"
　　'自動記録マクロから持ってきてます
Worksheets(1).Activate
Sheets(1).Select
Range(Cells(6, 9)).Copy Destination:=Sheets(1).Range(Cells(7, 9)) 'この行でエラーが出る

回答No.1

puddingeater · ベストアンサー

エラー行に2個あるRange()が不要です。
Range(Cells(6, 9)).Copy Destination:=Sheets(1).Range(Cells(7, 9))
→Cells(6, 9).Copy Destination:=Sheets(1).Cells(7, 9)

Range(Cells(6, 9))だと、Cells(6, 9).Valueが表すアドレスを参照してしまう。
たとえば Cells(6, 9) に "B1" と書かれていたら、Range(Cells(6, 9)) は Range("B1") のことになる。
で、Cells(6, 9) が空欄なら Range("") みたいなことになって参照できないのでエラー。

お礼

すみません　自己解決しました

R1C1形式でも絶対列の設定ができました
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC7,'在庫を貼付'!C3:C6,4,FALSE)"

c[-6]:c[-3]→c3:c6と変更

F4を押して選ぶのは、関数の時と一緒ですね
（関数で言うとC列→$C列）
これで参照先が継承されました

昨日からずっと悩んでたのが解決して嬉しいです
ありがとうございました

補足

即レスありがとうございます！

ご指摘のようにRange()を外すとエラーが起きなくなりました

ただしVLOOKUPの式の入ったセルが、cells(1,1)にあってこれをcells(6,9)にコピーすると#REF!エラーとなり、参照先の列が消えてしまって上手く動いてません　R1C1形式でないVLOOKUPの設定はどうやるか教えて頂けますか？

※条件を書くのを忘れてました
OS:Windows7 Home Premium(64bit)
Ver:Excel2013(32bit)

回答No.2

mt2008

Range(Cells(6, 9))　この書き方が良くないです。

Range(Cells(6, 9)).Copy Destination:=Sheets(1).Range(Cells(7, 9)) 'この行でエラーが出る
↓
Cells(6, 9).Copy Destination:=Worksheets(1).Cells(7, 9)

と、書き換えてみて下さい。
2000行までコピーしたいなら

Cells(6, 9).Copy Destination:=Worksheets(1).Range(Cells(7, 9), Cells(2000, 9))

です。

お礼

すみません
自己解決しました

R1C1形式でも絶対列が指定できることが分かりました
（詳細はNo.1さんのお礼欄に記載してます）
これで式を2000行までコピーして使えます

ありがとうございまいた

補足

すばやいレスありがとうございます

ご指摘通り修正したら、コピーに関しては無事動くようになりました(2000行まで実行してます）

ただNo.1の方にも質問しましたが、VLOOKUPの式がcells(1,1)にあり、コピーでcells(7,9)からcells(2000,9)に持ってくると参照元のセル情報が消えているため#REF!エラーとなります
（関数で言えば=VLOOKUP($G7,'在庫を貼付'!#REF!,4,FALSE)のようになります）

VLOOKUPの式をcells(6,9)に設定するか、R1C1形式で設定できるのか教えてもらえますか？

※条件を書くのを忘れてました
OS:Windows7 Home Premium(64bit)
Ver:Excel2013(32bit)