挿入マクロの難問について

質問

• 難しい挿入マクロを教えて下さい。あるフォルダに、『商品コード.xls 』というブックと、『データ【１】.xls 』という名前のブックが【１】～【７６】まであります。『商品コード.xls 』のコードを基準にして、『データ【１】.xls 』 ～ 『データ【７６】.xls 』 の全てのデータのＤ列を検索し、Ｅ列を挿入して、商品を記入したいのです。挿入する際には、半角大文字に揃えてから検索を行いたいのですが、それは可能でしょうか？
• また、『データ【１】.xls 』 ～ 『データ【７６】.xls 』 にＥ列を挿入し、商品名がついていないコードを新たに作り、『商品コード.xls 』に追加して更新した後、再び全てのブックを検索したいと考えています。これはマクロの一部を変更することで対応できるでしょうか？
• 質問がわがままで勝手なものであることをお詫び申し上げますが、この挿入マクロについてご指導いただけないでしょうか？お手数をおかけしますが、よろしくお願いいたします！

回答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関数をマクロで表示させるという方法が、想像もしなかったことで
大変参考になりました。
難しいコードを難しく考えすぎず、今の自分にできる範囲でのコードを考えてみることを学びました。

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