Excel VBA 特定列を検索対象にかけて別シートに転記をして、一覧にしたい

Question

Excel VBA 初心者です。お手柔らかによろしくお願いします。
（当方、Excel2013 32bit版を使用しております）

前提・実現したいこと

テストの得点の打ち込みと集計をしようとしています。
そこで、受験番号とテストの得点を縦に並べた"英語_得点"というシートがあります。

"英語_得点"

受験番号	得点
100A	20
200A	40
300A	60
500A	100
︙	︙

このシートの他に"国語_得点"と"数学_得点"という同様のシートもあります。

このデータを"得点一覧"というシートに移動させたいのです。
"得点一覧"シートの完成予想図は以下の通りです。

"得点一覧"

|名前|受験番号|英語|国語|数学|
|:--|:--|:--|:--:|
|安藤太郎|100A|20|60|40|
|伊藤次郎|200A|40||60|
|梅村三郎|300A|60|80||
|江口四郎|400A|||60|
|太田五郎|500A|100|80||
|︙|︙|︙|︙|︙|

上記のように、テストを受験しない人もいるので、その部分は何も記入せずに穴が空いています。
例えば、"英語_得点"で受験番号が400Aが抜けていますが、"得点一覧"にはその部分を空白にしたいのです。

そこで、"英語_得点"の受験番号を検索値として、"得点一覧"の受験番号の一覧を検索範囲として検索して、行番号を取得、シート名を変数として指定をし、その交差点に得点を入力したいと考えております。

---

私はVBAの知識は皆無であり、独学で模索しているので、どのようにググって良いのかすら、よくわかっていない状態です。
teratailで調べて、解決に近づく以下の投稿を見つけまして、色々と数字をいじったりしてみましたが、やりたいことが違うことやコードがよく分からなかったため、質問させていただいた次第です。

vba　行と列を指定→検索して値を転記
ExcelのVBAで他のシートのデータを検察し、値を置換したいです。

大変厚かましいですが、ご教授いただければ幸いです。
よろしくお願いします。

Answer 1

ワークシートのVLOOKUP関数の入力は出来ました。

それができているなら、
得点の範囲を選択して、コピー、貼り付けのオプションで値のみ貼り付けにしたらどうでしょう。

式か値に変換されますので、動作がもっさりはなくなるかと。

科目別得点シートのデータが変化したときに、式を入力しなおすので面倒という場合は、上記の操作をVBA化すればいいでしょう。

Public Sub 得点転記()
    Dim i As Long
    Dim rng As Range
    With Worksheets("得点一覧")
        For i = 3 To 5
            Set rng = .Range("A1").CurrentRegion.Columns(i)
            Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
            rng.Formula = _
                "=IFERROR(VLOOKUP(B2," & _
                rng.Cells(0, 1).Value & "_得点!A:B,2,False),"""")"
            rng.Value = rng.Value '式を値に変換
        Next
    End With
End Sub

Comments

[k_shin]

返信遅れて申し訳ないです。

わざわざコードを組んでくださり、ありがとうございます。
正常に動きました。

For文の i の数字をいじることで、応用が効くことができて、大満足です。
私の言葉足らずの話から汲み取っていただいて感謝しております。

本当にありがとうございました。助かりました。
BAにさせていただきました。

Answer 2

VLOOKUP関数 を使えば済むのでは？

Comments

[k_shin]

データがたくさんあり、時間がかかるので、関数は避けたいのです。
出来ればVBAで完結できるようにしたいのです。
無茶言ってすいません。

[hentaiman]

コード載せてないようですけどもうVOOKUPを使ったプログラムは試してみたって事ですよね？データ件数が分からないけどそんなに時間かかりますかね？２０列強×１００００行近くのデータを処理しても数秒で済んだ覚えがあるけど。どんなＶＢＡ組んだんでしょうかね。個人の感覚に依るとは思うけど、何秒以上で遅いと感じます？１～２秒で済ませたいなら多分無理だと思う。

[Orlofsky]

VBAの勉強も時間がかかるかと。各シートを用意して「相対参照」https://dekiru.net/article/4467/ を何万行か試してみては？

[k_shin]

ワークシートのVLOOKUP関数の入力は出来ました。
しかし、VLOOKUPをワークシートに入れると、動作がモッサリ（特にスクロール）するので、ワークシートに入れることは避けたいので、VBAで実現しようと考えました。

それともWorksheetFunctionを使うということでしょうか？

Forで出来ているケースがあったので、WorksheetFunctionを使わずに実現できないかと思い質問した次第でございます。

注文面倒くさくてごめんなさい(_ _;)

[nanami12]

考え方ですが
名前 受験番号 がどのシート"国語_得点""数学_得点"にあるのであれば
名前、受講番号のベースのシートに　英語列　国語列　数学列を作成して
おき各、シートの得点を得点が入力される行から最後の列までよみこませ
ベースのシートの得点開始行にマージ書き込ませればできます
仕様がこれであってるのか不明な為コードは記載してません。

[hentaiman]

スクロールが原因で遅いならスクロールさせなきゃいいじゃない？
WorksheetFunction使いたくない理由が遅いからなのか、それともWorksheetFunction自体を使いたくないのか
WorksheetFunction自体を使う事に抵抗は無いのならエクセル漁って自分がどんなやり方してたか確認して回答しますが笑
ただそれも何秒以上の動作で遅く感じるかによるしデータ件数によるので、なので何秒以上だと遅く感じるか聞きました。

[k_shin]

そもそもWorksheetFunctionを使いたくないのです。

仰ってたVLOOKUPのこと、をワークシートに式として入れる関数のことだと思っていましたが、多分hentaimanさんと話が食い違っているように思えます。
ワークシートに式として入れると、自動計算される影響なのか、動作がモッサリとするので、VBAで一発で実現できればと思ったのです。

言葉足らずで失礼しました。

[hentaiman]

いや、ワークシートに式で入れる必要ないんですけど。WorksheetFunctionを使ってVBAで一発で出来ると思うんだけど、どうして一発で出来なくなるのかが全く分からない・・・
自分もVBA知識ゼロの状態の状態から誰にも教わらずGoogle頼りに気合で業務をこなした程度の知識なので、質問の意図理解出来てないかもしんなかったですね