実行時エラー '424' オブジェクトが必要です

質問

デバッグ中で、下記のエラーが出て、進めません。

実行時エラー '424' オブジェクトが必要です。
Dim x1, x2, st1, st2, st3 As String
x1 = Cells(41, 5).Value
Set st1.Value = Application.WorksheetFunction.VLookup(x1, Range(Cells(102, 2), Cells(106, 4)), 3, False)

x1　　Cells(102, 2)　 Cells(106, 4)には値が入っています。

アドバイス宜しくお願い致します。

回答No.2

WindFaller

こんにちは。

コードを見る限りは、まだ入門編の段階でしょうから、本来、型の宣言は無視しても構いません。慣れるまでは、データ型の宣言はしなくてよいです。データ型の意味が分かるようになるまでは、かなり練習が必要です。データ型で、返る答えが違ってくることがあるからです。

>Dim x1, x2, st1, st2, st3 As String
>「型」はひとまとめには書けません。は知りませんでした。
書けないわけではなく、ただ指定されていない型がVairantになるだけです。
これは、VBAの仕様です。なお、あえて、型の宣言をナシにしても初級段階ではよいです。

Application.関数名は、古い書き方ですが、エラー値が返る時に、実行時エラーになりません。しかし、この場合の、st1 は、Variant にしないといけません。それは、エラー値が入るからです。これは、上級です。

st1 = WorksheetFunction.VLookup(x1, Range(Cells(102, 2), Cells(106, 4)), 3, False)
範囲の書き方は、上記のようにしても間違いありません。Applicationが省略されています。Applicationは、もちろん、Excelのオブジェクトということです。

A1スタイルのほうが、読みやすいなら、Range("B102:D106")としても良いし、Cellsスタイルで、行番号、列番号を入れても良いです。ただし、RangeとCellsを組み合わせた場合は、お互いが連結しあっていませんから、シートを指定する時は、出来る限り、ActiveSheet など、シートを With ステートメントで括ったほうがエラーは出にくいです。（分からなければ、無視して構いません。）

>x1　　Cells(102, 2)　 Cells(106, 4)には値が入っています。
検索値が、範囲の中に値が必ずあるというなら、エラー回避は不要です。

'//
Sub Test1()
　 Dim x1, x2, st1, st2, st3 'データ型を省きます
　 x1 = Range("E41").Value
　 If x1 <> "" And WorksheetFunction.CountIf(Range("B102:B106"), x1) > 0 Then　'エラー回避（空白でないことと、"B102:B106"の範囲に、値があることの確認
　　 st1 = WorksheetFunction.VLookup(x1, Range("B102:D106"), 3, False)
　　 MsgBox st1, vbInformation
　 Else
　　　MsgBox "値が見つかりません。", vbExclamation
　 End If
End Sub

'//
なお、角括弧は、直接、Rangeの役割はなく、Evaluate関数の省略形ですから、たとえ使えたとしても、有効性が限定されますから、VBAで、利用する時は細心の注意が必要です。上記で書いたRangeとCellと同じく、そのまま用いると、親オブジェクトのシートは、ActiveSheetだけになってしまいますし、パラメータが文字型ではないので、応用力がありません。。（これも、意味が分からないのでしたら、無視して構いません。ただ、なるべく標準的な書き方を学んでください。）

お礼

重ねて丁寧にコメント戴きありがとうございます。
エラーは回避できました。
エラー回避の方法も参考にさせて戴きます。

回答No.1

yaritsusozai

問題点１：DIM宣言の仕方が間違っている

問題点２：Setの使い方が間違っている

問題点３：範囲指定の仕方が間違っている

変数定義に関しては、どの変数をどの型で宣言したいのかよくわからないので、ここでは全てバリアント型としておきます。データ内容を知っているご自身で調整なさってください。参考までに、Dim宣言は複数の変数をまとめて扱えますが、「型」はひとまとめには書けません。仮に、全て文字型ならば、x1 As String,x2 As String,…と面倒でも一つ一つ書く必要があります。省略するとバリアント型になります。

以下修正サンプル：

Dim x1 As Variant,x2 As Variant '…以下略
x1 = Cells(41,5).Value
st1 = Application.VLookup(x1, [B102:D106], 3, False)
MsgBox st1

抜き出したst1をどうしたいのか不明なので、ここでは簡易的に、メッセージボックスに結果を表示する形にしています。

お礼

初歩的なミスですみません。「型」はひとまとめには書けません。は知りませんでした。
大変ありがとうございました。