難しい挿入マクロを教えて下さい

質問

下の図では　小さくて　分かり難く　すみません。
いろいろ調べてみましたが、とても難しく、限界です。ご指導をお願いしたくて、質問させていただきます。

あるフォルダに、『商品コード.xls 』（Ｂ列とＣ列だけのデータ）というブックと、
『データ【１】.xls 』 という名前のブックが【１】～【７６】まで（様式は全部おなじ）
全部で　７７個のブックがあります。

『商品コード.xls 』のコードを基準にして、『データ【１】.xls 』 ～ 『データ【７６】.xls 』 の
全てのデータのＤ列を検索し、
Ｅ列を挿入して、商品を記入したいのです。

その場合、
『商品コード.xls 』のＢ列（商品コード）と、
『データ【１】.xls 』 ～ 『データ【７６】.xls 』 のＤ列の英字が、全半角・大小文字が混在していますので、
これを、半角大文字に揃えてから検索・・・・そんな　わがままなマクロは可能でしょうか？

そして、もうひとつ、
『データ【１】.xls 』 ～ 『データ【７６】.xls 』 にＥ列を挿入・検索後、
ひとつひとつのブックをみていき、Ｅ列に商品名がついてないコード（『商品コード.xls 』にないもの）を 新たに作り、
基準となる『商品コード.xls 』に追加し（更新）、
更新された『商品コード.xls 』で再度　７６個全てを検索し直します。

一度　Ｅ列を挿入・商品を記入した後、更新した『商品コード.xls 』で再び検索するというマクロは、
コードのどこかの箇所を変更すれば、対応できるでしょうか？
それとも、もう一度　作りなおすことになるのでしょうか？

わがまま過ぎる、勝手なお願いですが、　
ご指導、よろしくお願いします！　

　※『商品コード.xls 』の行は、約 1,000行
　　　『データ【１】.xls 』 ～ 『データ【７６】.xls 』 の行は　それぞれちがいますが、
　　　少ないデータで約200行、多いものは 5万行ほどもあります。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.4

DOUGLAS_ · ベストアンサー

ｅ）但し、『商品コード.xls 』だけは、「商品コード」のある列が他の『データ【＊】.xls 』と異なりますので、この ブック だけは、同様の コード により、別途、単独に行なっておきます。

====================

【３】「Ｄ列を検索し、Ｅ列を挿入して、商品を記入」する操作
ｆ）先ず、「Ｅ列を挿入」します。
　この程度の操作は、[新しいマクロの記録] で コード を採取する習慣を付けましょう。
Columns("E:E").Insert Shift:=xlToRight

ｇ）次に「Ｄ列を検索し、商品を記入」する操作ですが、最も簡単なのは、
・『商品コード.xls 』も開いておいて、
・Ｅ列に、「=VLOOKUP(D2,[商品コード.xls]Sheet1!$B:$C,2,FALSE)」という式を入れる。
ということになろうかと存じます。
'---------------
Sub 第3段階()
　　Columns("E:E").Insert xlToRight
　　With Range(Range("E2"), Cells(Range("D" & Rows.Count).End(xlUp).Row, "E"))
　　　　.FormulaR1C1 = "=VLOOKUP(RC[-1],[商品コード.xls]Sheet1!C2:C3,2,FALSE)"
　　　　.Copy
　　　　Range("E2").Select
　　　　Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
　　　　　　:=False, Transpose:=False
　　　　Application.CutCopyMode = False
　　　　.Replace "#N/A", ""
　　End With
End Sub
'---------------
　「第3段階」を実行すると、「Ｅ列を挿入」し Ｅ列 に上記の計算式を施してから、すべて値に変化し、その中で「#N/A」を表示している セル を、すべてクリアします。

====================

【４】『商品コード.xls 』を更新する作業
ｈ）案外、これが難しい作業になるかも知れません。
　とりあえず、「第3段階」を終えた時点で、Ｅ列 が空白の行の Ｄ列 の「商品コード」を『商品コード.xls 』の Ｂ列 の後ろに コピー する、という作業になろうかと存じますが、「Ｅ列 が空白の行」は、Ｅ列 を選択しておいて、[ジャンプ] メニュー - [空白セル] を選択する操作を [新しいマクロの記録] で記録します。
　これまでにも何度か出てきましたが、[End プロパティ]・[Offset プロパティ]、また、[Resize プロパティ] などについては、ザクッとお勉強しておいてください。

http://www.google.co.jp/webhp?q=#sclient&q=Offset+Resize+End+%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB
'---------------
Sub 第4段階()
　　With columns("E:E") 'Range(Range("E2"), Cells(Range("D" & Rows.Count).End(xlUp).Row, "E"))
　　　　.SpecialCells(xlCellTypeBlanks).Offset(, -1).Copy
　　　　Windows("商品コード.xls").Activate
　　　　Range("B" & Rows.Count).End(xlUp).Offset(1).Select
　　　　ActiveSheet.Paste
　　　　Application.CutCopyMode = False
　　End With
End Sub
'---------------
====================

【５】さて、冒頭の見出しに「？？」と書きましたが、実は、この段階で「手作業」が必要になります。
　と申しますのは、ここで、【６】 の操作に移りたいトコロですが、肝心なコトが抜けています。
　【４】の段階では、
＞基準となる『商品コード.xls 』に追加（更新）
されただけであって、その「商品コード」に対応する「商品」名が記入されていないのではありませんか？

　したがって、ここで、『商品コード.xls 』の C列 に、手作業で「商品」名を記入していきます。
　その前に、各ブック から寄せ集められた「未登録の商品名」がダブっていることも考えられますので、先ず、『商品コード.xls 』の D列 を並べ替えましょうか。
　≪実は、『商品コード.xls 』の D列 には、[VLOOKUP 関数] で、別表から「商品名」を引っ張ってくるようにしてある≫ ということになれば、この段階も マクロ化 できます。

====================

【６】更新された『商品コード.xls 』で再度検索し直す操作
【７】最後のご質問
　では、いよいよ「再検索」ですが、これは、「第3段階」の「Ｅ列を挿入」する操作を外せばよいだけかと存じます。
　換言すれば、【２】～【４】までの操作は、１回行なえば、今後は必要のない操作かと存じます。
　ですから、【２】～【４】までの操作を、１回行なっておけば、今後は、【３】から「Ｅ列を挿入」する操作を外した操作、及び【４】と【５】を１つにまとめておけば、そのまま使えるかと存じます。

====================

　以上、長々とご説明いたしましたが、一つずつ、よくご自身でお考えいただいて健闘されますことをお祈り申します。

お礼

DOUGLAS_さま、ご忠告も含め、このように丁寧なご説明を書いてくださって
ありがとうございます。
わがままな注文に、親切に対応してくださり、感激しております。

【５】のご指摘も、ありがとうございます。おっしゃる通りです。
【３】の検索ですが、Find や xlWhole を使うとばかり思っていましたが、
VLOOKUP関数をマクロで表示させるという方法が、想像もしなかったことで
大変参考になりました。
難しいコードを難しく考えすぎず、今の自分にできる範囲でのコードを考えてみることを学びました。

教えていただいたコードと解説、保存して勉強します。
ありがとうございました。

回答No.3

DOUGLAS_

＃かなりの長文になってしまいましたので、回答を２つに分けさせていただきます。
＃一つずつよくご理解いただきながら、じっくりとお読みください。

　全部のコトをいっぺんに済まそうというのは、p1_1q さんの スキル で対応できますでしょうか？

　せめて、今、ここまで出来ているという VBA の コード を提示されてから、どこをどう直したらよいかみたいなご質問の方がよさそうに感じます。
　それと、最近では、ＷＥＢページ にそれなりの教材がたくさんありますので、ＷＥＢ検索 も一つの技術かと存じます。

＞いろいろ調べてみましたが、とても難しく、限界です。
とのことで、確かに難しい操作かとは存じますが、全部いっぺんに済ますのではなくて、操作の内容をよく整理し、ご自身でよく理解して、一つずつやっつけていくという心構えが大切かと存じます。

＞ご指導をお願いしたくて、質問させていただきます。
とお書きですので、ズバリの コード ではなくて、敢えてバラバラの コード を書きましたので、後は、組み合わせ方を、ご自身で工夫なさってください。

====================

　さて、お説教はこのぐらいにして、p1_1q さんのご要望を、操作の順にまとめると、下記のようなことになろうかと存じます。

【１】あるフォルダに、『商品コード.xls 』（Ｂ列とＣ列だけのデータ）というブックと、『データ【１】.xls 』 という名前のブックが【１】～【７６】まで（様式は全部おなじ）全部で　７７個のブックがあります。

【２】『商品コード.xls 』のＢ列（商品コード）と、『データ【１】.xls 』 ～ 『データ【７６】.xls 』 のＤ列の英字が、全半角・大小文字が混在していますので、これを、半角大文字に揃えてから検索

【３】『商品コード.xls 』のコードを基準にして、『データ【１】.xls 』 ～ 『データ【７６】.xls 』 の全てのデータのＤ列を検索し、Ｅ列を挿入して、商品を記入したいのです。

【４】『データ【１】.xls 』 ～ 『データ【７６】.xls 』 にＥ列を挿入・検索後、ひとつひとつのブックをみていき、Ｅ列に商品名がついてないコード（『商品コード.xls 』にないもの）を 新たに作り、基準となる『商品コード.xls 』に追加し（更新）

【５】？？

【６】更新された『商品コード.xls 』で再度　７６個全てを検索し直します。

【７】一度　Ｅ列を挿入・商品を記入した後、更新した『商品コード.xls 』で再び検索するというマクロは、コードのどこかの箇所を変更すれば、対応できるでしょうか？　それとも、もう一度　作りなおすことになるのでしょうか？

====================

　それでは、ここで、上記の１つずつについて詳しくご説明いたします。

【１】「あるフォルダ」内の「全部・・・のブック」に対する処理
ａ）先ず、すべての ブック を拾い出す コード を書きます。
　[Dir 関数] 関数を用い、[Do...Loop ステートメント] で処理します。
ｂ）すべての ブック に対して、同じ操作を施す コード を (ａ) に挿入します。
　下記の例では、「Debug.Print MyName」の部分です。
'---------------
Sub 第1段階()
　　Dim MyPath As String
　　Dim MyName As String
　　MyPath = "D:\hoge\"
　　MyName = Dir(MyPath & "データ【*】.xls")
　　Do While MyName <> ""
　　　　Debug.Print MyName
　　　　MyName = Dir
　　Loop
End Sub
'---------------
　[Dir 関数] の ヘルプ をご覧くださいね。
　「MyPath」は実際の「あるフォルダ」の フルパス に書き換えてください。
　「第1段階」を実行すると「あるフォルダ」の中にある「データ【??】.xls」という名前の ファイル の名前が、ＶＢＥ（Visual　Basic Editor）の イミディエイト ウィンドウ に吐き出されます。

　今後は、「同じ操作を施す コード」、つまり「Debug.Print MyName」の部分を必要に応じて書き換えていきます。

http://www.google.co.jp/webhp?q=#sclient&q=%E3%83%95%E3%82%A9%E3%83%AB%E3%83%80%E5%86%85%E3%81%AE%E5%85%A8%E3%81%A6%E3%81%AE%E3%83%96%E3%83%83%E3%82%AF
====================

【２】英字を半角大文字に揃える操作
ｃ）「様式は全部おなじ」ブック の「データシート」の「D2 セル から Ｄ列最終行まで」という範囲設定でよろしいでしょうか？
　この場合、
・【１】の操作により ブック を１つずつ開く。
・「データシート」の「D2 セル から Ｄ列最終行まで」を選択する。
・「範囲内のすべての セル」の「英字を半角大文字に揃える」
・ブック を保存終了する
という段取りになろうかと存じます。

　「D2 セル から Ｄ列最終行まで」というのは、
Range(Range("D2"), Range("D2").End(xlDown))
のような感じで取得できます。
　D列が「D2」の 単一セル しかない場合、あるいは D列 に 空のセル があるかも知れませんので、
Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
の方がよいかも知れません。
　前者は、D2 セル から [Shift] + [Ctrl] + [↓] で選択する範囲、後者は D列の下端 から [Ctrl] + [↑] で選択した セル と D2 セル の間にある セル範囲 です。

ｄ）次に、#1 さんのご回答にもありますように、「英字を半角大文字に揃える」操作には、[StrConv 関数] を用います。
　「半角」にし、さらに「大文字」にする訳ですから [StrConv 関数] を繰り返して用いることになります。
　ただ、範囲内のすべての セル に対して同じ操作を行なう訳ですから、これをどうしたらよいか？ ですが、ユーザー定義関数 を作っても、結局、[For(Each)...Next ステートメント] で １セル ずつ 回すコトになります。
'---------------
Sub 第2段階1()
　　Dim i As Long
　　Sheets("データシート").Select
　　For i = 2 To Range("D" & Rows.Count).End(xlUp).Row
　　　　Cells(i, "D").Value = StrConv(StrConv(Cells(i, "D").Value, vbNarrow), vbUpperCase)
　　Next i
End Sub
'---------------
　「第2段階1」を実行すると、「D2 セル から Ｄ列最終行まで」１セル ずつ「英字を半角大文字に揃える」操作が行なわれます。

　ただ、「多いものは 5万行ほどもあります」とのことですので、場合によっては、
・「必ず空いている列」に「=UPPER(ASC(D2))」という式を立てる
・その値を D列 に コピー する
・最後に、補助列を削除する
とした方が速いのかも知れません。
　[新しいマクロの記録] によって、上記の操作を手作業で行なうと、下記のような コード を得ることができます。
'---------------
Sub 第2段階2()
　　With Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
　　　　.Offset(, 5).FormulaR1C1 = "=UPPER(ASC(RC[-5]))"
　　　　.Offset(, 5).Copy
　　　　Range("D2").Select
　　　　Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
　　　　　　:=False, Transpose:=False
　　　　Application.CutCopyMode = False
　　　　.Offset(, 5).EntireColumn.Delete Shift:=xlToLeft
　　End With
End Sub
'---------------
　「第2段階2」を実行すると、D列 より「５列右側」の「Ｉ列」を補助列として作業が行なわれます。
　これについては、マニアック な手法もあるようですが、初心者向けの基本的な操作のみお知らせいたします。

http://www.google.co.jp/webhp?q=#sclient&q=%E7%AF%84%E5%9B%B2%E5%86%85%E3%81%AE%E8%8B%B1%E5%AD%97%E3%82%92%E5%8D%8A%E8%A7%92%E5%A4%A7%E6%96%87%E5%AD%97+%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB

回答No.2

WWolf

こんにちは。
まず、したいことのイメージを整理（シュミレーション）してみてください。
全て書くのはよくないかも知れませんので、すべては書きません。

（イメージ１）商品コードを見つけるとE列を増やすというロジックを組み込むことによりロジックそのものが複雑になる。
（する事１）よって、データシートのブック1-76については事前にE列を増やす事のみを手作業またはマクロで実行。

（イメージ２）商品コードブックに、全半角・大小文字が混在してることを、半角大文字に揃えてから検索するのもなー。
（する事２-１）運用的に必須条件として商品コードに全半角・大小文字が混在しないといけないのか？違えば、商品コードブックに対し全半角・大小文字を半角大文字に一括変換しておく。
（する事２-２）折角、半角大文字に統一したんだからいっその事、今後は商品コードの入力には規制しておく。

などなど・・・

それらをしておいてから、本当にすべきこと（最も面倒なこと）を整理し直しロジックを考えてみてください。

お礼

WWolfさま、そんなふうに順番に書き出してみたら、
するべき事が、とてもよくわかりますね。

頭が固く、ひとつのコードに納めなければならない　と思っていました。
手作業で出来るところ、関数でできるところは先にやっておいて
その後、マクロを分けて実行する方法がある事、非常に勉強になりました。

ありがとうございました。

回答No.1

MARU4812

『VBA 全角 半角』程度のキーワードで WEB検索すれば
StrConv という関数がすぐに見つかるし、大文字小文字も
同じ。
セルの参照式では難しいけど、マクロで多少長くなっても
手順を順に書いていくだけならプログラムとしては並。
平凡な内容なので、ちゃんと勉強してればできる内容です。

お礼

並・・・そうですね、ごもっともです。
私にとっては、とても難しいのですが、
引き続き、ちゃんと勉強をして考えてみます。

ありがとうございました。