VBAで変数を使用して関数挿入

Question

【VBAに数式を挿入したい】

VBAでformula関数を使用し、数式挿入を試みたのですが
以下数式のダブルクォーテーションの位置が
誤っているのが原因なのか上手くデバッグ出来ない状況です。
実行したい事は単純に、[G行- H行]を4行目から最終行までfor文で繰り返したいだけです。

VBA==================================

For i = 4 To LastRow

Sheets(1).Range("M" & i).Formula = "=G" & i & "- H" & i"

Next i

======================================

Answer 1

Formulaに設定する文字列の組み立て方で混乱しているのなら以下のようにするとわかりやすいです。

For i = 4 To LastRow
	str = "=G{0}-H{1}"

	str = Replace(str, "{0}", Str(i))
	str = Replace(str, "{1}", Str(i))
	Sheets(1).Range("M" & i).Formula = str
Next

For i = 4 To LastRow
	str = "=100*(H{0}-G{1}) / G{2} < 5%"

	str = Replace(str, "{0}", Str(i))
	str = Replace(str, "{1}", Str(i))
	str = Replace(str, "{2}", Str(i))
	Sheets(1).Range("L" & i).Formula = str
Next

※ 式自体のエラーなどは考慮していません。質問者さんが記載されたものそのまま組み立てています。

Comments

[N.Mana]

ご回答頂き誠にありがとうございます！
参考にさせていただきます。

Answer 2

行末のダブルクォーテーションが余計なだけでは。

Sheets(1).Range("M" & i).Formula = "=G" & i & "- H" & i"
↓
Sheets(1).Range("M" & i).Formula = "=G" & i & "- H" & i

Comments

[N.Mana]

迅速にご対応頂きありがとうございます！大変助かります。

同様の質問になりますが、もし以下の関数につきましても
解決方法をご存知でしたらご教示いただきたいです。

【計算式】=======================
100×(今回申請額-前回申請額)÷(前回申請額)<5%
=============================

【VBA】=============================================
For i = 4 To LastRow

Sheets(1).Range("L" & i).Formula = "=100*(H" & i & "-G" & i") / G"& i & < 5%"

Next i
==================================================

[ttyp03]

式自体を正しく書くだけならこうなります。
Sheets(1).Range("L" & i).Formula = "=100*(H" & i & "-G" & i & ") / G"& i & "< 5%"
最後の「<5%」はどう扱いますか？
これだと単に比較結果でTRUEかFALSEが返るだけですが。
5%もそのままでは無理でしょうし。

Answer 3

最後の " が不要です。

    Sheets(1).Range("M" & i).Formula = "=G" & i & "- H" & i

別案

Forループを使わなくても下記のコード1行で同じことが可能です。

Sheets(1).Range("M4:M" & LastRow).Formula = "=G4-H4"

$ がついてないと相対参照なので。

Comments

[N.Mana]

解決しました。
早速ご回答頂き誠にありがとうございます！

[N.Mana]

なるほどですね！大変勉強になります。
追加になりますが以下の関数につきましても解決方法がわからない状況です...

【計算式】=======================
100×(今回申請額-前回申請額)÷(前回申請額)<5%
=============================

【VBA】=============================================
For i = 4 To LastRow

Sheets(1).Range("L" & i).Formula = "=100*(H" & i & "-G" & i") / G"& i & < 5%"

Next i
==================================================

[hatena19]

どのような計算をしたいのか日本語で説明してもらえますか。
あるいは、実際にセルに計算式を手入力するとしたら、どのような式になりますか。

[hatena19]

求めたいのは下記のようなことでしょうか。
今回申請額の前回申請額からの増加率が5％未満かどうか判定したい。
だとしたら式では =100*(H4-G4)/G4<5 ですので、
コードでこのような結果になるように考えてみましょう。