VBA 構文でどこが間違っているか 438エラー：オブジェクトは、このプロパティまたはメソッドをサポートしていません。

Question

VBA初心者です。お世話になっております。

問題の文
Cells(i + 8, j + 3).Value "=VLOOKUP(B" & i & ",'" & Worksh & "'!$A$1:$M$" & Row & ",5 + " & f & ",FALSE)"

例：Cells(1, 1).Value = "=VLOOKUP(B13,'シート1'!$A$1:$M$5,5,FALSE)"
のようにして、For文ネストを使用し、上記に関数の文章をセル内に入れたいと考えてます。
しかし、コード上で確かめてみると、

438エラー：オブジェクトは、このプロパティまたはメソッドをサポートしていません。

になります。
自分と構文の””や＆、空白がはいっていて単純に間違っていると思っているのですが、変数にはいっているものが間違っていてもこの表示はでるのでしょうか？

宜しくお願いします。

Answer 1

式が入ってるセルの.Valueを取得した時に何が却ってくるでしょう？式ですか？値ですよね。
セルに数式を入力/取得する(Formula/FormulaR1C1プロパティ)

Answer 2

問題の文
Cells(i + 8, j + 3).Value "=VLOOKUP(B" & i & ",'" & Worksh & "'!$A$1:$M$" & Row & ",5 + " & f & ",FALSE)"

Workshがなんなのか突然聞かれても、他人は解りません。
まぁ、なんとなくワークシートが代入されているだろうと想像は付くわけですが、
「本当に」ワークシートがちゃんと代入されているか、
回答側では確認不可能です。

で、エラーメッセージは、「ずばり」の指摘をしてくれません。
なので、エラーメッセージを参考に当たりを付けてデバッグしていくわけですが、
やはり、1行1行精査していくことになりますので、
全文を掲載（あるいは動作確認可能なコード）を掲載したほうがよいでしょう。

ここで注目するのは、
「オブジェクトは、」です。
原因はこれなので、この行の中でオブジェクト（≒操作の対象になっているもの）を探して、
文法に間違いがないか確認します。
この行でオブジェクトが登場している箇所は、
Cells(i + 8, j + 3)　と
Worksh　ですよね？
Cellsの方は、iとjの値をローカルウィンドウでチェックし、
シート上に存在しないセルを示すようになってないか確認します。
あと、Valueの綴りが間違ってないかも確認します。（一見大丈夫そうです。）

つぎに、Workshですが、これはシートそのもの（オブジェクト）が入ってます。
Worksheetというものは、名前だけで存在してません。
セルであったり、名前であったり、たくさんの要素で形作られた物が、「ワークシート」という存在に
なります。

ここで、いま、
数式の中(エクセル君が数式と認識する文字列)シート名を使ってシートを指定したいのですから、
「ワークシート」という存在のなかの「シート名」を利用したいのですから、
Worksh.Name と、
オブジェクトのさまざまな要素の中から、シートの名前を取り出すよう指示してやる必要がありそうです。

ただし、セルそのものは、必ずどこかのシートに属してますから、
セルの中から、「どのシートのどのセルか」という情報を引き出してやればOKです。

時間がないのでこの辺で。

Answer 3

情報が少ないので推測の話になりますが、Workshの中身がWorksheet型のオブジェクトではないでしょうか？

オブジェクトは文字列ではないため、&演算子で文字列として結合することはできません。

自動で値に変換するための、特殊なプロパティまたはメソッドを持つオブジェクトも存在しますが、Worksheetはそれには該当しません。

そのため、
オブジェクト(=Worksheet)は、このプロパティまたはメソッド(=自動で値に変換するためのプロパティまたはメソッド)をサポートしていません。
のエラーが発生します。

---

sazi さんも指摘されていますが、Valueプロパティに設定するよりはFormulaプロパティなどに設定した方が意図が明確になると思われます(現状でも動くはずですが)。

また、数式の組み立て方によっては、ある程度の範囲でまとめて数式を設定できるので、そもそものループを減らせそうな気もします。

Answer 4

Cells(i + 8, j + 3).Valueの後に代入の=がなく、Valueがプロパティではなくメソッドとして解釈されているのではないでしょうか。以下のように直せば良いと思います。

Cells(i + 8, j + 3).Value = "=VLOOKUP(B" & i & ",'" & Worksh & "'!$A$1:$M$" & Row & ",5 + " & f & ",FALSE)"

ちなみに、saziさんの仰る通りセルに式を入力する時はValueプロパティではなくFormulaプロパティに代入するのが正しい書き方です。しかし、実際にはValueに式を代入しても、勝手に解釈されてセルには式が入力されるようです（動作確認したところそういう挙動になりました）。