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

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

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

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

Q&A

4回答

2251閲覧

Excel VBAで照合確認をしたい。

karubo0920

karubo0920

総合スコア12

VBA

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

0グッド

1クリップ

投稿2018/12/06 13:44

編集2022/01/12 10:55

前提・実現したいこと

Excel VBAで照合確認をしたい。

ここに質問の内容を詳しく書いてください。
照合確認をFor Nxstを使って、二つのシートのセルに同一のものがあれば、OKを出す。

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

照合データ増減を考え、最終行を取得するコードを書いたが、自宅PCではデータが同一ならば、OKが問題なく表示。会社PCではデータが同一であるが、OKが表示しないものがある。

Sub 照合パッチシート基準()

Worksheets(1).Name = "パッチシート" Worksheets(2).Name = "イメージ貼り付け" Worksheets(3).Name = "イメージ整理" Sheets("イメージ貼り付け").Select Range("E:F").Select Selection.Copy Sheets("イメージ整理").Select Range("A1").Select ActiveSheet.Paste Worksheet("パッチシート").Range("H1").Value = "パッチシート" Worksheets("パッチシート").Range("I1").Value = "イメージシステム" Worksheets("パッチシート").Select Dim patch As Long Dim img As Long Dim ws1 As Worksheet Dim ws3 As Worksheet Dim cmax As Long cmax = Cells(Rows.Count, 1).End(xlUp).Row Set ws1 = Worksheets("パッチシート") Set ws3 = Worksheets("イメージ整理") For img = 2 To cmax For patch = 2 To cmax If ws3.Range("A" & img).Value = ws1.Range("E" & patch).Value Then ws1.Range("H" & patch).Value = "OK" Exit For End If Next Next For patch = 2 To cmax If ws1.Range("h" & patch).Value = "" Then ws1.Range("h" & patch).Value = "エラーもしくはパッチシートがあまってないですか?" ws1.Range("I" & patch).Value = "イメージ化されてないです。" Exit For End If Next Columns("H:H").ColumnWidth = 40

End Sub

試したこと

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

家と自宅のExcelバージョンは2016です。

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

seastar3

2018/12/06 20:00

ここで日本語メッセージ等がやっと見えましたが、まだ余計な記述が残っていますよ。また、家のExcelのバージョンと会社のExcelのバージョンを示しましょう。
karubo0920

karubo0920

2018/12/07 14:26

たびたび、申し訳ございません。質問の不備を改善しました。
guest

回答4

0

照合データ増減を考え、最終行を取得するコードを書いたが、自宅PCではデータが同一ならば、OKが問題なく表示。会社PCではデータが同一であるが、OKが表示しないものがある。

こういう場合、原因は大体以下の2つです。
1)セルの指定が意図したセルと違う。(最終行の取得も含む)
2) 画面では同一に見えるが、実際の値は同一ではない。

なので、本当は、デバッグの方法を習うほうが有意義だと思いますが、
面倒なのでここでは、割愛します。
(別途、デバッグをテーマにして質問することをお勧めします)

で、とりあえず提示のコードを見た感想を書きます。

1)変数名は役割を想像しやすいものにする。
(個人的にわかればいいですが、ぼくにはわかりにくいです)
2)ループカウンターはシンプルな変数名に
3)最終的に操作するのは「セル範囲」なので、
セル範囲を変数に代入するように考える(セル範囲にはシートの情報も含まれている)
4)蛇足かもしれませんが、ここの掲示板では、
「変数の宣言は使う直前」的な流れがあるようですが、
デバッグ中読んでいて思考が止められちゃうので、
僕的には好みではありません。
5)本題でないものは、プロシージャの外に追い出す。

この辺に注意して、
練習なので何度でもコードを書いてみてはいかがでしょうか?

あとで、サンプルコード書けたら追記します。

VBA

1Option Explicit 2 3Sub test() 4 Dim i As Long 5 Dim j As Long 6 Dim c As Range 7 Dim r As Range 8 Dim flg As Boolean 9 10 '結果書き込みシートの初期化 11 With Worksheets(2) 12 .Cells.Clear 13 Worksheets(3).Range("E;F").Copy .Range("A1") 14 .UsedRange.Columns(3).Value = "NG" 15 End With 16 17 '同一データの存在確認をして結果を記録 18 With Worksheets 19 For i = 2 To .Item(1).Cells(.Item(1).Rows.Count, "A").End(xlUp).Row 20 Set c = .Item(1).Cells(i, "A") 21 If IsEmpty(c.Value) = False Then 22 flg = False 23 For j = 2 To .Item(2).Cells(.Item(2).Rows.Count, "E").End(xlUp).Row 24 Set r = .Item(2).Cells(j, "E") 25 If c.Value = r.Value Then 26 r.Offset(, 2).Value = "OK" 27 flg = True 28 Exit For 29 End If 30 Next 31 If flg = False Then 32 With .Item(2).Cells 33 .Item(j, "E").Value = c.Value 34 .Item(j, "G").Value = "Nothing" 35 End With 36 End If 37 End If 38 Next 39 End With 40End Sub

まずは、意図したセルを、意図したタイミングで指定できているかを、
コツコツ確認してみることをお勧めします。

参考URL>>
http://www.ken3.org/vba/excel-help.html

投稿2019/01/05 01:27

編集2019/01/05 02:30
mattuwan

総合スコア2136

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

リランした時に、前の残骸が残りますが、それをクリアしていません。
パッチシートのH、I列をクリアするようにしました。これで確認してみてください。

VBA

1Option Explicit 2 3Sub 照合パッチシート基準() 4 5 Worksheets(1).Name = "パッチシート" 6 Worksheets(2).Name = "イメージ貼り付け" 7 Worksheets(3).Name = "イメージ整理" 8 9 Sheets("イメージ貼り付け").Select 10 Range("E:F").Select 11 Selection.Copy 12 Sheets("イメージ整理").Select 13 Range("A1").Select 14 ActiveSheet.Paste 15 16 Worksheets("パッチシート").Range("H1").Value = "パッチシート" 17 Worksheets("パッチシート").Range("I1").Value = "イメージシステム" 18 19 Worksheets("パッチシート").Select 20 21 Dim patch As Long 22 Dim img As Long 23 Dim ws1 As Worksheet 24 Dim ws3 As Worksheet 25 Dim cmax As Long 26 27 cmax = Cells(Rows.Count, 1).End(xlUp).Row 28 29 Set ws1 = Worksheets("パッチシート") 30 Set ws3 = Worksheets("イメージ整理") 31'追加開始 32 For patch = 2 To cmax 33 ws1.Range("H" & patch).Value = "" 34 ws1.Range("I" & patch).Value = "" 35 Next 36'追加終了 37 For img = 2 To cmax 38 For patch = 2 To cmax 39 If ws3.Range("A" & img).Value = ws1.Range("E" & patch).Value Then 40 ws1.Range("H" & patch).Value = "OK" 41 Exit For 42 End If 43 Next 44 Next 45 46 For patch = 2 To cmax 47 If ws1.Range("h" & patch).Value = "" Then 48 ws1.Range("h" & patch).Value = "エラーもしくはパッチシートがあまってないですか?" 49 ws1.Range("I" & patch).Value = "イメージ化されてないです。" 50 Exit For 51 End If 52 Next 53 54 Columns("H:H").ColumnWidth = 40 55 56End Sub 57

投稿2019/01/08 01:33

tatsu99

総合スコア5424

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

ご回答、ありがとうございます。
問題確認コードで、空欄になるセルを画像で添付しました。イメージ説明

件数を減らして、セルG6、I9、K10に空欄になる。名前は、テスト名です。

デバックは詳しくはありませんが、ステップ実行とローカルウインドウを試してみたいと
思います。ローカルウインドウでどのようなエラーがたつと空欄になるのかご教示お願い致します。

※今回の処理ではパッチシートのE列に重複しているデータがあると、先に見つかる(上にある)行にのみOKが出力されますが、これが原因ではないですよね?

原因ではないと思います。

投稿2018/12/25 14:49

編集2018/12/25 15:05
karubo0920

karubo0920

総合スコア12

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

同じデータで違う動作をするということですが、おとなしく動かしていれば問題なさそうに見えます。

・処理中にシートやブックを切り替えたりしていませんか?
⇒シートを明示せずにセル参照している部分もありますので、タイミングによっては影響を受ける可能性があります。

・処理後に保存等せず、全く同じ状態のブックを双方の環境で動かしても結果に相違がでるのでしょうか?
⇒そうであれば環境依存の問題(Excelが壊れている等)の可能性もあります。


処理に不審な点がないか、解析してみました。

まず、序盤でSheet2⇒Sheet3にコピーを行っています。

その後、Sheet1のA列を対象に最終セルの行番号を取得し、ループ回数として変数に格納しています。

ループ処理①では、Sheet3を先ほどのループ回数だけ読み込み、Sheet1の一致する行にOK出力しています。

ループ処理②では、Sheet1のH列で最初に見つけた空欄に対しエラーメッセージを出力してループ終了しています。


以上の処理で、誤動作のキーとなりそうな部分としては
・Sheet1よりSheet3のデータ行が多い場合、全て処理されない
・Sheet1のA列にゴミセル(値を一度入力して消したなど)があると、最終行が変わる場合がある
※これは保存後、シート再表示すればゴミでなくなる
・最終行の取得の際、対象シートを明記していないので明記したほうが安全。
⇒最終行取得と最後の列幅設定をシート明示すればSheet1をSelectする必要はなくなり動作の安定につながります。

今のところ気になった点はこれくらいです。

意図していないところがないか、ご確認ください。

投稿2018/12/11 09:00

jawa

総合スコア3013

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

karubo0920

karubo0920

2018/12/14 13:45

ご回答、ありがとうございます。 下記について、確認しました。 ①処理中にシートやブックを切り替えたりしていませんか?  していません。 ②処理後に保存等せず、全く同じ状態のブックを双方の環境で動かしても結果に相違がでるのでしょうか?  結果に相違が出ます。 ③Sheet1よりSheet3のデータ行が多い場合、全て処理されない  パッチシートのデータ件数が226件、イメージ整理のデータ件数が226件。同一のデータが一致したものにOKが表示されるが、3件だけ空欄が表示される。  ④Sheet1のA列にゴミセル(値を一度入力して消したなど)があると、最終行が変わる場合がある  パッチシートのA列にゴミセルはないです。 ⑤最終行の取得の際、対象シートを明記していないので明記したほうが安全。     cmax = Worksheets("パッチシート").Cells(Rows.Count, 1).End(xlUp).Row    Worksheets("パッチシート").Columns("H:H").ColumnWidth = 40 対象シートを明記したが、結果 は3件が空欄表示。 勉強不足で申し訳ないのですが、ご教授いただけますでしょうか?
jawa

2018/12/17 05:20 編集

>同一のデータが一致したものにOKが表示されるが、3件だけ空欄が表示される。 今回の処理ではパッチシートのE列に重複しているデータがあると、先に見つかる(上にある)行にのみOKが出力されますが、これが原因ではないですよね? ※同じデータで処理をされているのなら正常端末でも同じ結果となるはずですので、念のための確認です。 >>全く同じ状態のブックを双方の環境で動かしても結果に相違がでるのでしょうか? >結果に相違が出ます。 全く同じ状態のブックに対し同じマクロを動かせば、同じ結果となって然るべきです。 ※細かいことを言えば同時に開いている別ブックのマクロや、組み込みマクロ(アドインマクロ)などの影響がなければ、の話ですが。 前述のとおり、コードにはいくつか曖昧な表記がみられるものの、基本的には期待する動作になりそうなコードです。 これで正常に動作しないのであれば、端末の環境に依存した問題があるように思えます。 当方の環境でも簡単なデータ(300件程度)を作成し実行してみましたが現象が再現しないため、的確なアドバイスができず申し訳ありません。 問題の発生する端末でどのような判定が行われた結果、OKが出力されなかったのか。 もしくはコード上ではOKを出力しているが、最終的にはセルが空欄となっているのか。 ここらへんを把握する必要があります。 ・処理件数を減らしてみても動作は変わらないでしょうか? ・コードをデバッグ実行して、OKと判断されるべきセルでどのような判定が行われ、どのように処理されているのか確認してみては如何でしょうか? デバッグが苦手でしたら、処理は重くなりますがコードの一部を以下のように置き換えて実行してみるというのもひとつの手です。 ``` For img = 2 To cmax For patch = 2 To cmax 'パッチシートの値をイメージ整理シートに出力する(D列以降に出力) ws3.Cells(img, patch + 2).Value = ws1.Range("E" & patch).Value If ws3.Range("A" & img).Value = ws1.Range("E" & patch).Value Then '一致する場合、OKを出力してpatchループを抜ける ws1.Range("H" & patch).Value = "OK" 'イメージ整理シートC列にもOK出力 ws3.Cells(img, "C").Value = "OK" Exit For End If Next Next ``` 上記コードの場合、イメージ整理シートを上から順に処理していく際、一致する項目を見つけるまでに比較した値をD列以降に列記していきます。 ※一致するものが見つからなければパッチシートE列のすべての値が列挙されます。 参考までにご確認ください。
jawa

2018/12/26 01:18 編集

まず余談なのですが、この回答への返答を別コメントとして記述いただいたようですが、スレッドも別れてしまうのでこういう場合は質問本文に追記としていただいた方がよかったですね。 --- 本題ですが、添付いただいた画像は「イメージ整理シート」に出力されたデバッグ結果ですよね? 本来OKが出力されないと言われていた「パッチシート」の方はどうだったでしょうか? 実行していただいたデバッグプログラムは、一致するものをスルーして(つまり一致していても"OK"を出さずに)ループが続行していないかを確認するためのものでした。 確かにデバッグ結果に3か所空欄が見えますが、チェック自体はきれいな階段状になっています。 つまり一致するものを見つけて"OK"を出力できており、そのため「イメージ整理シート」のC列にはすべてOKが出力されています。 ※階段状になったのは「パッチシート」と「イメージ整理シート」のデータが同じ順番で並んでいたからだと思います。 コードを見ればわかる通り、「イメージ整理シート」のC列は「パッチシート」に"OK"を出力した場合にのみ"OK"を出力していますので、「パッチシート」も結果はすべてOKだったのではないでしょうか? --- わからない点は、デバッグ結果に3か所空欄ができたことです。 前回コメントのコードをそのまま動かしたのなら、比較内容はpatchループ内で無条件に出力していますので間が抜けるなどありえません。 考えられるとすれば、 ①そのチェックの時だけ比較相手が空セルとして参照され、空欄を出力した ②ループ時には正しく値が出力されていたが、その後の何らかの処理によって空欄に上書きされた くらいです。 いずれにしてもデバッグしてみれば一目瞭然です。 個人的には②な気がしていますが、そうなるとこのプログラム内の動作によるものではないため原因の特定は難しいかもしれません。 まずはここらへんを意識して調査してみてください。
karubo0920

karubo0920

2018/12/29 17:03 編集

大変失礼しました。 回答にコメントをしたかったのですが、 エクセル画像を添付することができないので、 解決方法のほうにコメントしました。 ご指摘、ありがとうございます。 本題ですが、下記に回答します。 ①件数が少ない場合はパッチシートに空欄なくOKが出ます。(件数はパッチシートとイメージ整理のデータは同一) ②パッチシートのデータ件数が226件、イメージ整理のデータ件数が226件。同一のデータが一致したものにOKが表示されるが、3件だけ空欄が表示される。  ③②について、シートを再度確認したところ、イメージ整理のデータ件数は229件でした。そのうち、同一のデータは226件です。 あとはイメージデータのみにあるデータが3件あります。 ③状況を整理します。件数が多い場合、イメージ整理と照合するとパッチシートの件数226件中、223件はパッチシートにOKが出ます。イメージ整理のデータと同一でOKが出ない3件は最終行から数えて3件が空欄になります。 ④問題確認コードで、イメージ整理で空欄になるのは、③の3件とパッチシートにないデータ分の3件が空欄になります。 ⑤デバッグで、ステップ実行、ローカルウインドウで気づいたこと。変数ws1→オブジェクトCells→プロパティCurrentRegion→プロパティFormulalocalの階層構造を辿ってFormulalocalでOKが出てないものを発見しました。 ⑥問題を発見しましたが、論理エラーを解決する方法がわかりません。 たびたび恐縮ですが、ご教授いただけますでしょうか
shinobu_osaka

2019/01/05 04:49

あまり読んでませんが、 なぜか同一とならない同一であるはずの文字列を一文字ずつに分解し、 文字コードを出力させご自身の目にて比較してみてください、 それで文字として表示されていない差異が出るはずです。 おそらく元データ作成のコピペ時に変なものが紛れ込んだのだと。 バイナリ完全一致を求めないテキスト比較であれば、 比較前に trim 及び ワークシート関数 clean を掛けて比較すれば動くかと。 元データを触って良いのであれば、ワークシート関数 trim と clean を使用し、 元データをクリーンにしておくのが良いかと思います。
jawa

2019/01/07 07:21 編集

#④問題確認コードで、イメージ整理で空欄になるのは、③の3件とパッチシートにないデータ分の3件が空欄になります。 ⇒「パッチシートにないデータ分の3件」の行には、「パッチシートのすべてのコードが比較対象として出力されるが、C列にOKは出力されない」というのが意図する動作です。 このような結果になっていますか? ⇒「双方のシートに存在するが意図した結果とならない3件」の行には、「パッチシートで同一コードを見つけるまでのコードが比較対象として出力されており、C列とパッチシートにOKが出力される」というのが意図する動作です。 こちらはどのような結果になっていますか? --- #FormulalocalでOKが出てないものを発見しました。 ⇒デバッグ実行にもチャレンジしていただいたようですね。 1点指摘するとすれば、Formulalocalプロパティは関数式を格納するプロパティです。 セルに式ではなく実値が入っている場合、Formulalocalプロパティにもその値が格納されるので結果は同じかもしれませんが、今回は特に式を出力しているわけではないのでValueプロパティを見ればいいと思いますよ。 さて、ここで問題とされているのは「OKが入っているはずなのに空欄となっているセルを見つけた」ということですよね? そうなると押さえておきたいのは ・状況(例えばループの何週目で検出されるのか) ・再現性(何度処理しても同じ結果となるか) です。 これを突き詰めていけば解決の糸口になるかもしれません。 ご確認ください。 === >shinobu_osakaさん 文字列の相違と判断し、ロジック上で空欄を出力しているのなら有効な調査方法だと思います。 ただ、正常に動く環境とそうでない環境があるということで、同じExcelブックを両環境で試して動作に違いがあるとなると、文字列の相違という線は薄いのかな、と感覚的に思っています。 コード上は同一文字列と判断してOKを出力しているが、その後なんらかの要因によって結果が空欄に上書きされているのではないか、という線を勝手に推測しております。 ただ「同一と判断されているのか否かを確認する」という意味でも有効な調査だと思います。
shinobu_osaka

2019/01/07 07:11

読みました。 下にテストを実行した結果があったんですね…。 空白となってるということはコードのミスでまず間違いないですね、 この処理を行っている全コードと、サンプルデータがあれば、 OKを入れるところにブレイクポイントをつけるだけで一撃解決しそうですね…。 ただ、当方に「目視で同じに見える文字列で環境により一致しない判定になる」経験がありましたので、 まずはバイナリ一致の確認をと、とりあえずコメントしてしまいました。 ちなみにそのときは「なぜそのような動作をするのか」の追求は放置してcleanで解決しました。
jawa

2019/01/07 07:59 編集

例えばテスト結果ではG6セルが空欄になっていますが、H6セルまで値が入っており、C6セルにOKが出力されています。 これはA6セルの値(5644235)に一致する値をパッチシートの上方からチェックしていき、5行目までループしたところで一致するセルも見つけたことを意味しています。 そして4行目の結果(G6セル)には空欄が出力されています。 ここから (a)パッチシートの4行目が空セルになっていた (b)パッチシートの4行目には正常値(124569)が入っていたが、なぜか別の空セルを参照・出力してしまった (c)正常値(124569)を取得したが、出力はなぜか空欄を出力してしまった (d)正常値(124569)を取得し、その値を一度は出力したが、その後何らかの処理で空欄に上書きされた といった状況が推測できます。 (a)はその後の処理(G7セルなど)で正常値を取得・出力しているので考えにくそうです。 (b)はロジック上その時だけ別セルが参照されるようなコードではないので、あるとすれば外的要因かなと。ここは再現性次第でしょうか。 (c)もそのようなロジックではなく、このような動作になる外的要因もパッと思いつきません。メモリ破壊とか。。 といったわけで現時点では(d)が一番濃厚と踏んでいるのですが、そうであれば外的要因としか思えませんので手を焼いております。 もう少し検証情報をいただいて絞っていくしかなさそう、といった次第です。
shinobu_osaka

2019/01/07 19:51

「空欄になるセル」という事から固定箇所で再現性100%であると考えて、 ・データはすべて正常でゴミセル等も皆無である、 ・このコードを邪魔しそうなコードは存在しない、 ・家では完全動作する が前提ですとそれしか考えられませんよね、 だってコードは問題ないんですから…。 でも下記を読む限り「単に適合しないデータが間に入って3件分ズレてるとかそんな落ちでは?」 と思えてしまう。でも家では完全動作…。 ・イメージデータのみにあるデータが3件 ・イメージ整理のデータと同一でOKが出ない3件は最終行から数えて3件 ・イメージ整理で空欄になるのは、③の3件とパッチシートにないデータ分の3件
jawa

2019/01/08 00:16 編集

すみません。 >・イメージ整理のデータと同一でOKが出ない3件は最終行から数えて3件 この部分見落としてました。。これについては原因はわかります。 変数`cmax`は、直前にアクティブにしたパッチシートA列から件数を取得しています。(226件) この変数`cmax`を使ってパッチシートもイメージシートもループしているので、パッチシートの方が件数が少なければイメージシートの最終3行は処理対象となりません。 パッチシートとイメージシートの件数にズレがない、もしくはイメージシートの方が件数が少ないことを前提としたコードになっているのが原因と思われます。 ただ、それでも ・両環境で動作に差異がある ・G6セル等が空欄となる ことについては謎が残りますね。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問