質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
86.02%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

Q&A

解決済

VBA:ブック間で値が一致したときに転記

andrehisamoto
andrehisamoto

総合スコア1

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

2回答

0グッド

0クリップ

659閲覧

投稿2022/11/01 15:08

VBAで値が一致したときに転記するマクロを組んでいるのですが、うまいこと転記できず困っております。

前提・実現したいこと

ざっくりとどういう動きをさせたいかですが、
商品コードのマスタと、棚卸した結果のブックがあり、両ブックの項番が一致したとき、
棚卸結果をマスタに転記させるというものです。

発生している問題・エラーメッセージ

以下コードにて実行したのですが、そもそもVBAへの理解度が乏しく全量把握できておりません...;;
圧倒的な知識不足により質問の意図がうまく伝えられず申し訳ございませんが何卒、皆様のお力をお借りしたく...

以下、当サイトで質問されていたコードを基に作成いたしました。

Public Sub ブック間転記() 'ループ用の変数 Dim m As Long Dim n As Long Dim row1 As Long 'マスタ C,D列へ書き込む行番号 Dim s1 As Worksheet Dim s2 As Worksheet Set s1 = Workbooks("マスタ.xlsx").Worksheets(1) Set s2 = Workbooks("棚卸結果.xlsx").Worksheets(1) row1 = 2 'それぞれのシートの比較行を最終行までループ For m = 2 To s1.Cells(Rows.Count, 1).End(xlUp).Row For n = 2 To s2.Cells(Rows.Count, 4).End(xlUp).Row 'マスタと棚卸結果列の比較 これだと列ごとに比較してしまうので 'マスタの(m,1)を棚卸結果のD列の全てのデータと比較して、転記が終わってから次の処理に移りたい。 If s1.Cells(m, 1).Value = s2.Cells(n, 4).Value Then s1.Cells(row1, "C").Value = s2.Cells(n, "A").Value row1 = row1 + 1 End If Next Next End Sub

試したこと

補足情報(FW/ツールのバージョンなど)

・マスタは連番で採番されている
・棚卸結果は項番がバラバラ
・マスタ、棚卸結果、どちらも40万行ほど

マスタ例↓
イメージ説明 

棚卸結果例↓
イメージ説明

マスタA列と、棚卸結果D列が一致したとき、マスタC,D列に棚卸結果A,B列を転記したいです。
また、要件が40万行と多いこともあり処理速度の向上についても助言いただきたく...

当方、業務でVBAを使用したことがないのですが必要だとのことで依頼されまして、
周りはVBAを使ったことがない方で解決にも至らず大変困っております...;;;

何卒よろしくお願い致します。

以下のような質問にはグッドを送りましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

グッドが多くついた質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

下記のような質問は推奨されていません。

  • 間違っている
  • 質問になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

適切な質問に修正を依頼しましょう。

meg_

2022/11/01 16:06

VBAではなくエクセルの関数ではダメなのでしょうか?
andrehisamoto

2022/11/01 22:24

コメントありがとうございます! 要件が40万行ほどある事もあり、今後も使いたいのでVBAで、とのご依頼でして…;;
okakemetal

2022/11/01 23:22

商品コードマスタのデータは固定で、棚卸のデータが可変として理解したので その内容で話を進めます(そもそもの理解がずれていたらすいません) 商品コードのマスタを配列に入れます。(データ数固定の配列) 棚卸のデータも配列に入れいます(データ数変動の配列) 棚卸のデータをループを読み込んで、商品コードマスタとマッチングさせる 結果を配列に格納 全チェックが終わった時点で棚卸データの配列をセルに貼り付け というような流れでコーディングを検討ください
pig_vba

2022/11/02 00:49 編集

一つ気になったのですが、一致したときに転記するマスタ側の行はm行目ではなくrow1行目なのですか?これだと仮に2行目該当なし→3行目該当番ありだった時に2行目に転記されると思うのですが仕様通りですか? もう一点、棚卸結果の項番は被り無しですか?それとも、「同じ項番が複数あって最新のものを採用したい」ですか?どちらかで実装仕様が変わってきますね
tatsu99

2022/11/02 00:43

マスタの項番は、間違いなく1からの連番で作成されていますか。(歯抜けなし) もし、それが保証されているなら、検索処理を行う必要はありません。 棚卸データの項番の値+1が、マスタデータの対象行になります。 例 棚卸データの項番=10 →マスタの11行がマスタの項番=10 棚卸データの項番=15 →マスタの16行がマスタの項番=15 この方法が処理速度では圧倒的に速くなります。 1からの連番であることが、保証されないなら、使用出ません。
hatena19

2022/11/02 02:21

連番が保証されているなら、前のコメントの方法が高速でしょう。さらに、配列を使用するとより高速になります。 連番が保証されていない、抜けがあるなどなら、Dictionaryオブジェクトと配列を使うのが高速です。
andrehisamoto

2022/11/07 11:49

皆様、お返事できておらず大変申し訳ございません。 突発的な出張が重なり本日帰宅いたしました。 今からいろいろと試してみますので今しばらくお待ちいただけると幸いです。。。
andrehisamoto

2022/11/07 11:55

>okakemetalさん 理解あっております。 アドバイスありがとうございます!! 配列がいまいち理解できておらず絶賛勉強中でございまして... とはいえ、流れはなんとなくですが理解できました! ありがとうございます!
andrehisamoto

2022/11/07 11:57

>pig_vbaさん おっしゃる通りで、とんちんかんな場所に転記されており見逃しておりました(;´д`)トホホ 棚卸結果の項番は被り無しでございます。
andrehisamoto

2022/11/07 12:03

>tatsu99さん 連番で作成されており、抜け無しです。 その発想は全くございませんでした... アドバイスありがとうございます!
andrehisamoto

2022/11/07 12:06

>hatena19さん 配列...やはり処理速度のことを考えると必須ですね... 項番に抜けがある場合のアドバイスについてもありがとうございます!

回答2

2

マスターの項番が必ず1から始まる連番で抜けはなし。
棚卸結果の項番はマスターと一対一で対応していて、重複や抜けはない。
上記が保証されいる場合のコード例

vba

1Public Sub Sample() 2 Dim aryResult(), aryMaster() 3 With Workbooks("棚卸結果.xlsx").Worksheets(1) 4 aryResult = .Range(.Cells(2, 1), .Cells(Rows.Count, "D").End(xlUp)).Value 5 End With 6 ReDim aryMaster(1 To UBound(aryResult), 1 To 2) 7 8 Dim i As Long, n As Long 9 For i = 1 To UBound(aryResult) 10 n = aryResult(i, 4) 11 aryMaster(n, 1) = aryResult(i, 1) 12 aryMaster(n, 2) = aryResult(i, 2) 13 Next 14 15 Workbooks("マスタ.xlsx").Worksheets(1).Range("C2").Resize(UBound(aryMaster), 2).Value = aryMaster 16End Sub

40万行とデータ数が多いので配列を使って高速化してます。
大量のデータを高速に処理するには配列の利用は必須ですので、 「VBA 配列 高速化」などのキーワードで検索すると解説ページが多数見つかりますので、それで研究してください。

追記

棚卸結果の項番がマスターの方に存在しない場合のエラー対策を追加

vba

1Public Sub Sample2() 2 Dim aryResult(), aryMaster() 3 With Workbooks("棚卸結果.xlsx").Worksheets(1) 4 aryResult = .Range(.Cells(2, 1), .Cells(Rows.Count, "D").End(xlUp)).Value 5 End With 6 ReDim aryMaster(1 To UBound(aryResult), 1 To 2) 7 8 Dim i As Long, n As Long 9 For i = 1 To UBound(aryResult) 10 n = aryResult(i, 4) 11 If n > 0 And n <= UBound(aryMaster) Then 12 aryMaster(n, 1) = aryResult(i, 1) 13 aryMaster(n, 2) = aryResult(i, 2) 14 Else 15 Debug.Print "エラー行番号: " & i + 1 16 End If 17 Next 18 19 Workbooks("マスタ.xlsx").Worksheets(1).Range("C2").Resize(UBound(aryMaster), 2).Value = aryMaster 20End Sub

棚卸結果の項番がマスターの方に存在しない場合は、イミディエイトウィンドウにその行番号を出力するようしました。

投稿2022/11/02 03:02

編集2022/11/07 12:55
hatena19

総合スコア32031

andrehisamoto, okakemetal👍を押しています

良いと思った回答にはグッドを送りましょう。
グッドが多くついた回答ほどページの上位に表示されるので、他の人が素晴らしい回答を見つけやすくなります。

下記のような回答は推奨されていません。

  • 間違っている回答
  • 質問の回答になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

このような回答には修正を依頼しましょう。

回答へのコメント

andrehisamoto

2022/11/07 12:26

n = aryResult(i, 4) aryMaster(n, 1) = aryResult(i, 1) の部分で「実行時エラー:9」インデックスが有効範囲にありません。 と出てきます。 ブック名、シート名は合っていることを確認いたしました。 となると配列の指定?に関わる部分で私が認識を間違えている可能性が大ですかね? ちょっと研究してきます。。。
hatena19

2022/11/07 12:42

原因としては、棚卸結果の棚番が、マスターの方に存在しないと、そのエラーになりますね。 そういう場合があるなら、その対策が必要ですね。 対策を追加したコード例も追記しておきますので、参考にしてください。
andrehisamoto

2022/11/16 03:03

ご返信出来ておらず大変申し訳ございません。 ありがとうございます! 追記いただいたもの含め、参考にさせて頂き 配列を使った改修に励んでいきたいと思います。 (配列難しい…(ヽ´ω`)トホホ・・)

2

ベストアンサー

マスタの項番が1からの連番であることが保証されている場合のコードです。

VBA

1Option Explicit 2 3Public Sub ブック間転記() 4'ループ用の変数 5 Dim m As Long 6 Dim row1 As Long 'マスタ C,D列へ書き込む行番号 7 Dim s1 As Worksheet 8 Dim s2 As Worksheet 9 Dim kouban As Variant 10 Dim flag As Boolean 11 Dim err_count As Long: err_count = 0 12 Dim nrm_count As Long: nrm_count = 0 13 Set s1 = Workbooks("マスタ.xlsx").Worksheets(1) 14 Set s2 = Workbooks("棚卸結果.xlsx").Worksheets(1) 15 row1 = 2 16 '棚卸のシートの比較行を最終行までループ 17 For m = 2 To s2.Cells(Rows.Count, 1).End(xlUp).Row 18 flag = False 19 kouban = s2.Cells(m, 4).Value 20 If kouban <> "" Then 21 If IsNumeric(kouban) = True Then 22 row1 = CLng(kouban) + 1 23 If row1 <= Rows.Count Then 24 If s1.Cells(row1, 1).Value = kouban Then 25 s1.Cells(row1, 3).Value = s2.Cells(m, 1).Value '棚卸結果 26 s1.Cells(row1, 4).Value = s2.Cells(m, 2).Value '備考 27 flag = True 28 End If 29 End If 30 End If 31 End If 32 If flag = True Then 33 nrm_count = nrm_count + 1 34 Else 35 err_count = err_count + 1 36 End If 37 Next 38 MsgBox ("正常処理件数=" & nrm_count & " 未処理件数=" & err_count) 39End Sub 40

投稿2022/11/02 02:51

tatsu99

総合スコア4904

andrehisamoto, okakemetal👍を押しています

良いと思った回答にはグッドを送りましょう。
グッドが多くついた回答ほどページの上位に表示されるので、他の人が素晴らしい回答を見つけやすくなります。

下記のような回答は推奨されていません。

  • 間違っている回答
  • 質問の回答になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

このような回答には修正を依頼しましょう。

回答へのコメント

andrehisamoto

2022/11/07 12:39

ネット環境が使えないような職場でして、まだ本番で試せておりませんが、似たようなものを作成し、 マクロ回してみたところ上手く転記できず... サンプルでは全くもって問題なかったございませんでした。 原因としては、ひじょーーーーに初歩的なところでして、セルの指定の認識が間違っているなと...;;; ここで聞いてしまって申し訳ないのと大変お恥ずかしいのですが、認識合わせをお願いしたく。 Option Explicit Public Sub ブック間転記() 'ループ用の変数 Dim m As Long Dim row1 As Long 'マスタ C,D列へ書き込む行番号 Dim s1 As Worksheet Dim s2 As Worksheet Dim kouban As Variant Dim flag As Boolean Dim err_count As Long: err_count = 0 Dim nrm_count As Long: nrm_count = 0 Set s1 = Workbooks("マスタ.xlsx").Worksheets(1) Set s2 = Workbooks("棚卸結果.xlsx").Worksheets(1)      ’↓転記する行番号 row1 = 2 '棚卸のシートの比較行を最終行までループ      ’↓検索開始行       ↓ここの数字はどこをさしているのでしょうか...? For m = 2 To s2.Cells(Rows.Count, 1).End(xlUp).Row flag = False              ’↓棚卸結果の項番列 kouban = s2.Cells(m, 4).Value If kouban <> "" Then If IsNumeric(kouban) = True Then row1 = CLng(kouban) + 1 If row1 <= Rows.Count Then                ’↓マスタ項番列 If s1.Cells(row1, 1).Value = kouban Then                ’↓転記先列      ↓転記元列 s1.Cells(row1, 3).Value = s2.Cells(m, 1).Value '棚卸結果 s1.Cells(row1, 4).Value = s2.Cells(m, 2).Value '備考 flag = True End If End If End If End If If flag = True Then nrm_count = nrm_count + 1 Else err_count = err_count + 1 End If Next MsgBox ("正常処理件数=" & nrm_count & " 未処理件数=" & err_count) End Sub 以上、何卒よろしくお願いいたします。。。
tatsu99

2022/11/07 13:04 編集

     ’↓転記する行番号 row1 = 2・・・・・・これは、不要ですね。(あなたのマクロをコピーして作成した為、それが残ってました) (但し、これがあっても動作上は特に問題はないです)      ’↓検索開始行       ↓ここの数字はどこをさしているのでしょうか...? For m = 2 To s2.Cells(Rows.Count, 1).End(xlUp).Row 棚卸結果の1列(A列)を指していますが、あなたのマクロでは、4列目になっています。 もし、A列とD列で最終行の判定がかわることがあるなら、D列を採用してください。 (A列に空白のセルがある場合は、最終行の判定がおかしくなります) For m = 2 To s2.Cells(Rows.Count, 4).End(xlUp).Row にしてください。 他の箇所はあっています。
andrehisamoto

2022/11/08 04:46

先程、セル指定を修正し実行してみましたが、全て未処理件数に振り分けられてしまいました。 どこの認識を間違えているのかわからず… 何度もお手数をお掛けして恐縮ですが、見てもらえないでしょうか…? 補足: マスタ項番セル: B5~B400000 棚卸し項番セル: BJ5~BJ400000 マスタ転記先: BI5~ , BJ5~ 棚卸し転記元: C5~ , D5~ Option Explicit Public Sub ブック間転記() 'ループ用の変数 Dim m As Long Dim row1 As Long 'マスタ C,D列へ書き込む行番号 Dim s1 As Worksheet Dim s2 As Worksheet Dim kouban As Variant Dim flag As Boolean Dim err_count As Long: err_count = 0 Dim nrm_count As Long: nrm_count = 0 Set s1 = Workbooks("マスタ.xlsx").Worksheets(1) Set s2 = Workbooks("棚卸結果.xlsx").Worksheets(1) row1 = 5 '棚卸のシートの比較行を最終行までループ For m = 5 To s2.Cells(Rows.Count, 62).End(xlUp).Row flag = False kouban = s2.Cells(m, 62).Value If kouban <> "" Then If IsNumeric(kouban) = True Then row1 = CLng(kouban) + 1 If row1 <= Rows.Count Then If s1.Cells(row1, 2).Value = kouban Then s1.Cells(row1, 61).Value = s2.Cells(m, 3).Value '棚卸結果 s1.Cells(row1, 62).Value = s2.Cells(m, 4).Value '備考 flag = True End If End If End If End If If flag = True Then nrm_count = nrm_count + 1 Else err_count = err_count + 1 End If Next MsgBox ("正常処理件数=" & nrm_count & " 未処理件数=" & err_count) End Sub
tatsu99

2022/11/08 06:12

row1 = CLng(kouban) + 1 を row1 = CLng(kouban) + 4 に変えてください。
andrehisamoto

2022/11/16 01:32

ご返信出来ておらず大変申し訳ございません。 お陰様で問題なく転記出来ました。 この度は誠にありがとうございましたっっ!!

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
86.02%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。