マクロを簡潔にしたい

１１行の各セルに下記の通り、数式をマクロで組み込みました。少しでもマクロを簡潔にしようと、これまでいただいた各方々のご回答を参考にして、INDEX、MATCH関数を用いて組み込んだマクロが後段2です。結果は同じようになりましたが、ループ，置換方等色々な組み方、表示方法があるのではないかと思います。参考にしたいため他の方法があればお教えください。

Sub 記録表初期設定()
Worksheets("記録表").Activate
ActiveSheet.Unprotect

Range("d11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,10,0)="""","""",VLOOKUP($B11,データ!$B:$AB,10,0)))"
Range("e11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,11,0)="""","""",VLOOKUP($B11,データ!$B:$AB,11,0)))"
Range("f11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,12,0)="""","""",VLOOKUP($B11,データ!$B:$AB,12,0)))"
Range("g11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,13,0)="""","""",VLOOKUP($B11,データ!$B:$AB,13,0)))"
Range("h11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,14,0)="""","""",VLOOKUP($B11,データ!$B:$AB,14,0)))"
Range("i11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,15,0)="""","""",VLOOKUP($B11,データ!$B:$AB,15,0)))"
Range("j11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,16,0)="""","""",VLOOKUP($B11,データ!$B:$AB,16,0)))"
Range("k11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,17,0)="""","""",VLOOKUP($B11,データ!$B:$AB,17,0)))"
Range("l11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,18,0)="""","""",VLOOKUP($B11,データ!$B:$AB,18,0)))"
Range("m11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,19,0)="""","""",VLOOKUP($B11,データ!$B:$AB,19,0)))"
Range("n11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,20,0)="""","""",VLOOKUP($B11,データ!$B:$AB,20,0)))"
Range("o11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,21,0)="""","""",VLOOKUP($B11,データ!$B:$AB,21,0)))"
Range("p11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,22,0)="""","""",VLOOKUP($B11,データ!$B:$AB,22,0)))"
Range("t11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,25,0)="""","""",VLOOKUP($B11,データ!$B:$AB,25,0)))"

ActiveSheet.Protect
End Sub

Sub 記録表初期設定2()
Worksheets("記録表").Activate
ActiveSheet.Unprotect

Range("d11:p11").Formula = _
"=IF($B11="""","""",IF(INDEX(データ!k:k,MATCH($B11,データ!$B:$B,0))="""","""",INDEX(データ!k:k,MATCH($B11,データ!$B:$B,0))))"

Range("t11").Formula = _
"=IF($B11="""","""",IF(INDEX(データ!z:z,MATCH($B11,データ!$B:$B,0))="""","""",INDEX(データ!z:z,MATCH($B11,データ!$B:$B,0))))"

　ActiveSheet.Protect
End Sub

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

• 大変申し訳ありませんが「COLUMN()+6」の部分の説明をお願いします。+6はどういうことでしょうか。

  No.2の回答に寄せられた補足コメントです。 補足日時：2022/09/16 13:27

No.4 ベストアンサー 回答者： fujillin 回答日時： 2022/09/16 12:21

こんにちは

複数セルへの入力は（同じ式を入力する場合は）、ループするよりもご提示の方法の方が効率的だと思います。
T列の式も同じならば、セルのコピペで行った方が簡単ですが、参照列がひとつずれているようなのでそうはいかないようですね。

設定している関数式内にも繰り返しがあるのと、参照列だけ異なる式を設定しているので、その辺りを少し簡略化するならば・・・
（コード上での関数式の視認性は若干悪くなりますけれど）

Const f1 = "INDEX(データ!@1,MATCH($B11,データ!$B:$B,0))"
Const f2 = "=IF($B11="""","""",IF(@2="""","""",@2))"

Range("D11:P11").FormulaLocal = Replace(f2, "@2", Replace(f1, "@1", "K:K"))
Range("T11").FormulaLocal = Replace(f2, "@2", Replace(f1, "@1", "Z:Z"))

みたいなのではいかがでしょうか。

この回答へのお礼

毎回、回答していただきありがとうございましす。何しろマクロは独学ですので理解するに時間がかかってしまいます。Replace（置換）を使用しての簡略化、私には思いつきませんが大変勉強になりました。他のシートにも同じような検索マクロを組んでありますので当てはめて使用させていただきます。

お礼日時：2022/09/17 09:14

No.6 回答者： Qchan1962 回答日時： 2022/09/16 14:36

＃５です

説明べたで何度もすみません

D列（最初に出力するセルD11）に　＝COLUMN()　を入れると　4　が返ります
＝COLUMN()　カッコ内は書かれているセルが省略されています

Vlookupで使う列インデックスは10なので+6としました

10が欲しいだけですので　COLUMN(J11)　とかで10を取得しても良いです
（こちらの方が実数を使わないのでしっくりくるかもです）

また、関数COLUMNに使用しているセルは相対参照なのでE11にコピー（出力）すると右に1つずれてCOLUMN(K11)となり、11が返る仕組みですね

この回答へのお礼

ありがとうございました。COLUMN()+6ですと実際にマクロを実行しますと
D11～P11セルに全く同じ数式が表示されますので戸惑います。COLUMN(J11）
としますと相対参照により1つずれていき変わることがわかりますので説明しやすいです。常にマクロの簡潔に向け取り組んでいますが大変参考になりました。
ループの方法も回答していただきありがとうございました。

お礼日時：2022/09/16 16:08

No.5 回答者： Qchan1962 回答日時： 2022/09/16 14:18

COLUMN()+6　少し乱暴だったかな・・・

関数はすでに書かれているので理解されているとして
10のままですと数値は変わらないので、出力先カラムで変化する関数をと思ったのですが、むしろ判り難くなってしまいましたね
COLUMN(J11)とかCOLUMN(J1)と書いた方が良かったかな・・

No.3 回答者： Qchan1962 回答日時： 2022/09/16 12:01

ごめんなさい一応

訂正
関数を・・・

訂正：ワークシート関数を・・・

MATCHでOKなのでB列の値は一意と判断してFindとしました

No.2 回答者： Qchan1962 回答日時： 2022/09/16 11:52

こんにちは

＞結果は同じようになりましたが、ループ，置換方等色々な組み方、表示方法があるのではないかと思います。参考にしたいため他の方法があればお教えください。
確かに
＞Sub 記録表初期設定()はこんな感じでも
protect内を書いています

Range("d11:p11").Formula = "=IF($B11="""","""",if(VLOOKUP($B11,データ!$B:$AB,COLUMN()+6,0)="""","""",VLOOKUP($B11,データ!$B:$AB,COLUMN()+6,0)))"
Range("t11").Formula = "=IF($B11="""","""",IF(VLOOKUP($B11,データ!$B:$AB,25,0)="""","""",VLOOKUP($B11,データ!$B:$AB,25,0)))"


関数を残すか否か・・・ループ結果のみ出力（＃１様と少し違うループで）
Dim key, r As Range, n As Integer
key = Range("B11")
For Each r In Range("d11:p11,t11")
n = 6
If key <> "" Then
If r.Column = 20 Then n = 5
r.Value = Application.VLookup(key, Worksheets("データ").Range("B:AB"), r.Column + n, 0)
End If
Next

関数を使わず
Dim key, r As Range, n As Integer, fd As Range
key = Range("B11")
For Each r In Range("d11:p11,t11")
n = 5
If key <> "" Then
If r.Column = 20 Then n = 4
Set fd = Worksheets("データ").Range("B:B").Find(What:=key, LookIn:=xlValues, LookAt:=xlWhole)
If Not fd Is Nothing Then r.Value = fd.Offset(, r.Column + n)
End If
Next

などなど・・

No.1 回答者： t_fumiaki 回答日時： 2022/09/16 11:21

Range("d11").Formulaの部分は、

d,e,f,g・・・・と進むので、cellsにすれば列は変数で出来ます。
[cells(n,m):ｎ行m列でn,mは整数]

Range("d11").Formula　⇒　cells(11,4).Formula
Range("e11").Formula　⇒　cells(11,5).Formula
Range("f11").Formula　⇒　cells(11,6).Formula

VLOOKUP($B11,データ!$B:$AB,10,0)も10が変化するので、変数にする

VLOOKUP($B11,データ!$B:$AB," & 変数 & ",0)・・・・

どの変数も整数なので、for文で変数をカウントアップして回せます。

この回答へのお礼

早々にご回答いただきありがとうござい。以前、for文で変数をカウントアップするマクロを組みましたがうまく組めなかったため、再度試みて見ます。

お礼日時：2022/09/16 11:43