Formulaプロパティーを使ってセルに数式を組んだのですが簡潔にしたい。

D3:O3,D5;O5セルに数式を組み保護をかけてありますが、誤って消去、削除された場合を考えて下記の通りマクロを組みましたが簡潔にならないでしょうか。
Sub 集計表初期設定()

Sheets("集計表").Select
ActiveSheet.Unprotect

Application.ScreenUpdating = False '画面描画を停止
Application.EnableEvents = False 'イベント無効

Range("d3").Formula = "=IF(項目説明!$G10="""","""",項目説明!$G10)"
Range("e3").Formula = "=IF(項目説明!$G11="""","""",項目説明!$G11)"
Range("f3").Formula = "=IF(項目説明!$G12="""","""",項目説明!$G12)"
Range("g3").Formula = "=IF(項目説明!$G13="""","""",項目説明!$G13)"
Range("h3").Formula = "=IF(項目説明!$G14="""","""",項目説明!$G14)"
Range("i3").Formula = "=IF(項目説明!$G15="""","""",項目説明!$G15)"
Range("j3").Formula = "=IF(項目説明!$G16="""","""",項目説明!$G16)"
Range("k3").Formula = "=IF(項目説明!$G17="""","""",項目説明!$G17)"
Range("l3").Formula = "=IF(項目説明!$G18="""","""",項目説明!$G18)"
Range("m3").Formula = "=IF(項目説明!$G19="""","""",項目説明!$G19)"
Range("n3").Formula = "=IF(項目説明!$G20="""","""",項目説明!$G20)"
Range("o3").Formula = "=IF(項目説明!$G21="""","""",項目説明!$G21)"

Range("d5").Formula = "=IF(ISERROR(VLOOKUP(d3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(d3,記録表!$X$11:$AA$21,4,0))"
Range("e5").Formula = "=IF(ISERROR(VLOOKUP(e3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(e3,記録表!$X$11:$AA$21,4,0))"
Range("f5").Formula = "=IF(ISERROR(VLOOKUP(f3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(f3,記録表!$X$11:$AA$21,4,0))"
Range("g5").Formula = "=IF(ISERROR(VLOOKUP(g3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(g3,記録表!$X$11:$AA$21,4,0))"
Range("h5").Formula = "=IF(ISERROR(VLOOKUP(h3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(h3,記録表!$X$11:$AA$21,4,0))"
Range("i5").Formula = "=IF(ISERROR(VLOOKUP(i3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(i3,記録表!$X$11:$AA$21,4,0))"
Range("j5").Formula = "=IF(ISERROR(VLOOKUP(j3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(j3,記録表!$X$11:$AA$21,4,0))"
Range("k5").Formula = "=IF(ISERROR(VLOOKUP(k3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(k3,記録表!$X$11:$AA$21,4,0))"
Range("l5").Formula = "=IF(ISERROR(VLOOKUP(l3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(l3,記録表!$X$11:$AA$21,4,0))"
Range("m5").Formula = "=IF(ISERROR(VLOOKUP(m3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(m3,記録表!$X$11:$AA$21,4,0))"
Range("n5").Formula = "=IF(ISERROR(VLOOKUP(n3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(n3,記録表!$X$11:$AA$21,4,0))"
Range("o5").Formula = "=IF(ISERROR(VLOOKUP(o3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(o3,記録表!$X$11:$AA$21,4,0))"

ActiveSheet.Protect
Application.EnableEvents = True 'イベントを開始
Application.ScreenUpdating = True 'ちらつき防止解除

End Sub

質問者からの補足コメント

• 今回、マクロが長くなるため一部省略して質問させていただきましたが、D3:O3,D5;O5セルのほかにD4:O4セルに D3:O3と同じような数式を入力しています。ご回答いただきました関数式で行った結果うまくいきました。「イベントの停止」は外しました。
  4行目の数式
  　 Range("d4").Formula = "=IF(項目説明!$h10="""","""",項目説明!$h10)"
  を
  Const h = "INDEX(項目説明!$H10:$H21,COLUMN(A1))"
  Range("D4:O4").FormulaLocal = Replace("=IF(@="""","""",@)", "@", h)
  で採用させていただきました。

  No.3の回答に寄せられた補足コメントです。 補足日時：2022/08/22 14:04

No.3 ベストアンサー 回答者： fujillin 回答日時： 2022/08/22 08:44

こんにちは

すでに解説されているように、一つ目の関数式が行列入換えのような形になっているので、単純にフィルコピーはできない形式ですね。
どうしてもそのままに拘るのなら、既回答のようにループで処理することになりそうです。

既に回答は出ていますけれど、ご提示の関数式を同じ内容でフィルコピー可能な形式に変えても良いのなら、まとめて入力することが可能になります。
以下は、そのような方法での一例です。

もしも、関数式を変えても良いのであるなら・・
一番目の式（3行目の式）は参照先の指定を
　=INDEX(項目説明!$G10:$G21,COLUMN(A1))
のようにすることでフィルコピー可能になります。
（上記は、わかりやすくするため空白の場合の処理を除いてあります）

また、二番目の式は、ISERRORの代わりにIFERROR関数を用いることで短縮が可能です。
　=IFERROR(VLOOKUP(D3,記録表!$X$11:$AA$21,4,0),"")
といった感じ。


もしもこちらでもよければ、ご提示のコードは以下のようにまとめることが可能でしょう。

Sub 集計表初期設定()
Const f = "INDEX(項目説明!$G10:$G21,COLUMN(A1))"
Const g = "=IFERROR(VLOOKUP(D3,記録表!$X$11:$AA$21,4,0),"""")"

Worksheets("集計表").Activate
Application.EnableEvents = False
ActiveSheet.Unprotect

Range("D3:O3").FormulaLocal = Replace("=IF(@="""","""",@)", "@", f)
Range("D5:O5").FormulaLocal = g

ActiveSheet.Protect
Application.EnableEvents = True
End Sub
　
※ 他のセルで揮発性関数や配列計算が大量に使われていなければ、たかだか20数個のセルの入力で「ちらつき」が発生するとも思えないので省略してあります。
もしも、そのような場合には、既に指摘があるように、自動計算を一時停止する方が効果的と思います。
※ 同様に「イベントの停止」も不要ではないかと推測しますが、全体の仕組みが不明なので、念のため、こちらは残してあります。

この回答へのお礼

ご回答いただきありがとうございました。長いマクロが数行のコードで組めることにびっくりしています。私にはまだまだ組むことができませんが、少しずつ理解して挑戦して行きたいです。

お礼日時：2022/08/22 14:15

No.2 回答者： Qchan1962 回答日時： 2022/08/21 23:00

＃１

勘違い・・すみません。
試して見ましたら、行№10が列の変化で変わるのですね　AutoFillで列方向にデータを作っても変化しないですね
やり方があったか記憶にないので　ループで

Dim i As Integer
For i = 0 To Range("d3:o3").Count
Range("d3").Offset(, i).Formula = _
"=IF(項目説明!$G" & 10 + i & "="""","""",項目説明!$G" & 10 + i & ")"
Next

Range("d5")はOK

＃１の下記はダメ　行方向と列方向が違うので　Excelが変えてくれない
With Range("d3")
.Formula = "=IF(項目説明!$G10="""","""",項目説明!$G10)"
.AutoFill Destination:=Range("d3:o3")
End With

この回答へのお礼

確認させていただきました。77才ボケ防止にとマクロに挑戦、長いマクロになってしまい、少しでも短めにマクロが組めないかと質問させていただきました。指定のセルに式が入力しました。ありがとうございました。

お礼日時：2022/08/22 10:42

No.1 回答者： Qchan1962 回答日時： 2022/08/21 22:23

こんばんは

書き方は色々ありそうですが、

'手動計算
Application.Calculation = xlCalculationManual
With Range("d3")
.Formula = "=IF(項目説明!$G10="""","""",項目説明!$G10)"
.AutoFill Destination:=Range("d3:o3")
End With
Range("d5").Resize(, 12).Formula = _
"=IF(ISERROR(VLOOKUP(d3,記録表!$X$11:$AA$21,4,0)),"""",VLOOKUP(d3,記録表!$X$11:$AA$21,4,0))"
'自動計算
Application.Calculation = xlCalculationAutomatic

でどうでしょう

Application.EnableEvents = True 'イベントを開始
Application.ScreenUpdating = True 'ちらつき防止解除
それをするなら、Calculationを加えた方が良いかも

この回答へのお礼

早々のご回答ありがとうございます。確認をさせていただきます。

お礼日時：2022/08/22 10:18