VBAで実行時エラー1004が出ます

質問

VBAで実行時エラー1004が出ます。
「Rangeメソッドは失敗しました。Worksheetオブジェクト」です。
あらゆる可能性を調べたのですが、分かりません。誰か教えて頂けますでしょうか？
下記コードの「Cells(m, 7) =・・・」の部分がエラーになりました。

Sub ボタン1_Click()
Dim 現シート As Worksheet
～
現シート.Activate
Cells(m, 7) = WorksheetFunction.VLookup(現シート.Range(現シート.Cells(m, 4)).Select, 現シート.Range(現シート.Cells(4, 104), 現シート.Cells(15, 107)).Select, 4, False)
～
End Sub

何卒宜しくお願いいたします。

回答No.7

tsubu-yuki

> 「WorksheetFunctionクラスのVLookupプロパティを取得できません」が出たので、この原因から

それについては
>> その時のエラーは「1004 ～～プロパティを取得できません。」です。
と、前の回答で書いたつもりですよ。
Vlookup関数が「返すべき値を見つけられない」のが原因です。

もう一度、記載しておきますが
「Cells(m,4)の値と完全一致するセルがCZ列に存在するか？」
まずは、ご確認ください。

お礼

ご指摘のとおりやったらうまくいきました。ありがとうございました。

回答No.6

HohoPapa

検査値：現シート.Cells(m, 4).Value
範囲　：Range(現シート.Cells(4, 104), 現シート.Cells(15, 107))
列番号：4
検索方法：False
ならば

Cells(m, 7).Value = WorksheetFunction.VLookup _
　( _
　現シート.Cells(m, 4).Value, _
　Range(現シート.Cells(4, 104), 現シート.Cells(15, 107)), _
　4, _
　False _
　)

と思います。

お礼

ご教示ありがとうございます。結果、「WorksheetFunctionクラスのVLookupプロパティを取得できません」が出たので、この原因から調べてみます。

回答No.5

tsubu-yuki

すいません。
やはり個人的に読み辛いので書き換えます。
こちらからの疑問も幾つかあるので、それも含めて。
というか、一部しか見えないので、何とも言えないところもあります。

Sub ボタン1_Click()
Dim 現シート As Worksheet
Dim 行(8) As String　　
Dim m As Long
～
　　行(1) = 4　　　　 ' 次行もまとめて、m = 4 で良いのでは。
　　m = 行(1)　　　　 ' 他のところで 行(1) を使うなら別ですが。

　　' きっとこの前に変数「現シート」には何かが代入されていると信じて
　　現シート.Activate　　'これを付けるなら、次式のシート指定は無意味。
　　Cells(m, 7) = WorksheetFunction.VLookup(Cells(m, 4), Range("CZ4:DC15"), 4, False)
'　　あるいは With でまとめる
'　　With 現シート
'　　　　.Cells(m, 7) = WorksheetFunction.VLookup(.Cells(m, 4), .Range("CZ4:DC15"), 4, False)
'　　End

　　～
End Sub

Range(Cells(行, 列))　← Range(　　)を使う理由がわかりません。
Cells(行, 列)だけで指定できます。
同様、参照範囲が固定されているのであれば、
そんなに回りくどい書き方は全く必要が無いです。
Range("CZ4:DC15")で充分です。

で。
肝心のエラーの件。
上記に書き換えてもエラーが出る場合、
考えられる理由はいくつかありますが、
現段階で考えやすいエラー理由は・・・
「そもそも、VLOOKUP関数でエラーが返っている」です。
ワークシート上でVLOOKUP関数を完全一致指定で使っているとき、
参照範囲の１列目に「完全一致するデータが無い」と、
「#N/A」エラーが返りますね。
要するに、セルに入力しようとした計算式がエラーを返したからエラー停止。
この状態である可能性が考えやすいです。
なお、その時のエラーは「1004 ～～プロパティを取得できません。」です。
というわけで、まず確認すべきは、
Cells(m,4)の値と完全一致するセルがCZ列に存在するか？です。

これを回避するにはエラー無視が手っ取り早いです。
一度、Vlookup関数の返り値を変数にとって、
それを評価して、セルに入力します。

Dim mm As Variant
On Error Resume Next　　' エラー無視
　　' 次行がエラーだと代入せずに進むので、「エラーの時mmは空白」
　　' 計算が正常終了したら、返り値を代入
　　mm = WorksheetFunction.VLookup(Cells(m, 4), Range("j4:m10"), 4, False)
On Error GoTo 0　　'エラー処理を初期値に戻す

　　' mmが空白じゃなかったら、セルに入力
　　If Not mm = "" Then Cells(m, 7) = mm　

これで強引に回避できます。
ただし、これだと「その他のエラー」も無視してしまうので注意です。

お礼

ご教示ありがとうございます。結果、「On Error Resume Next」でVLookupは素通りしたようです。これを削除すると、「WorksheetFunctionクラスのVLookupプロパティを取得できません」が出たので、この原因から調べてみます。

回答No.4

watabe007

現シートがフクティブだからRange、Cellaの現シートへの参照は要らないですね
現シート.Activate
Cells(m, 7).Value = WorksheetFunction.VLookup(Cells(m, 4), Range(Cells(4, 104), Cells(15, 107)), 4, False)

お礼

ご教示ありがとうございました。「今度はWorksheetFunctionクラスのVLookupプロパティを取得できません」が出てきました。後は自力で調べて解決できそうです。

回答No.3

watabe007

With 現シート
　　.Activate
　　Cells(m, 7).Value = WorksheetFunction.VLookup(.Cells(m, 4), .Range(.Cells(4, 104), .Cells(15, 107)), 4, False)
End With

回答No.2

HohoPapa

VLookup の引数は
　検査値
　範囲
　列番号
　検索方法
です。

検査値：現シート.Cells(m, 4).Value
範囲　：Range(現シート.Cells(4, 104), 現シート.Cells(15, 107))
列番号：4
検索方法：False

ですか？

補足

ご教示ありがとうございます。ご指摘のとおりです。
なお、CZ4:DC15"ではなくCellsを使用しているのは、他の場所で例えばCells(ｘ, 104)としてLoopを廻すコードもあるため、纏めてエラーを潰したいためです。

回答No.1

tsubu-yuki

まずは、変数mにどんな値が入っているか？から確認が必要ですね。

んで、変数mにはちゃんと数値が入っているものと仮定して、
「.Select」が邪魔をしているように見えます。
ココはむしろ省略しなきゃならないところです。
つまり、
　　Cells(m, 7) = WorksheetFunction.VLookup(現シート.Range(現シート.Cells(m, 4)), 現シート.Range(現シート.Cells(4, 104), 現シート.Cells(15, 107)), 4, False)
こんな感じ。
どうしても何かつけたいなら「.Value」をどうぞ。

あとは好みの問題ですが、全体的に読みづらくないですか？
　　Cells(m, 7) = WorksheetFunction.VLookup(現シート.Cells(m, 4).Value, 現シート.Range("CZ4:DC15"), 4, False)
と素直に書く方がまだ読みやすいかと。

補足

ご教示ありがとうございます。selectを消去したのですが、同じ結果でした。今のコードは次のとおりで、同じ所でエラーとなりました。他に何を試してみたらよろしいでしょうか？

Sub ボタン1_Click()
Dim 現シート As Worksheet
Dim 行(8) As String
Dim m As Long
～
行(1) = 4
m = 行(1)
現シート.Activate
Cells(m, 7) = WorksheetFunction.VLookup(現シート.Range(現シート.Cells(m, 4)), 現シート.Range(現シート.Cells(4, 104), 現シート.Cells(15, 107)), 4, False)
～
End Sub