EXCEL VBA VLOOKUPの様な検索したい

質問

お世話になります。

質問させていただきます。

添付図の表3を作成したいのです。
やりたい事は・・・

[販売金額]シートに[集計]というボタンを作成して、このボタンがクリックされたら[商品名]と[年月]
(2012年4月とか)をKEYに隣の[平均単価]シート参照して、合致する平均単価を該当商品の販売金額の下段に1行挿入して平均単価を表示させたいのです。(図中 表3の緑セル部分)

まとめますと・・・
販売金額シート表1の全データをREADして商品名と年月をKEYに表2に合致した平均単価を探して、見つかれば平均単価を1行挿入して表示して最終的には表1を表3に変化させたい、のです。

補足
商品数は可変ですが1000個程度です。
もし合致する平均単価がなくても該当商品に平均単価フィールドを1行挿入して0円またはブランク表示させたいです。

何度も似たような質問ばかりしてまして申し訳ありませんが、どなたかご教授いただけませんでしょうか？
よろしくお願い致します。

環境
Windows XP SP3
Excel2003

回答No.1

mt2008 · ベストアンサー

こんな感じかな？
年月は「販売金額」「平均単価」両シートで同じという前提です。

Sub Sample()
　　With Sheets("販売金額")
　　　　nMaxRow = .Cells(Rows.Count, 1).End(xlUp).Row
　　　　For i = nMaxRow To 2 Step -1
　　　　　　sName = .Cells(i, 1)

　　　　　　.Rows(i + 1).Insert
　　　　　　.Cells(i + 1, 2) = "平均単価"

　　　　　　nChk = WorksheetFunction.CountIf(Sheets("平均単価").Range("A:A"), sName)
　　　　　　If nChk > 0 Then
　　　　　　　　nUnitPriceRow = WorksheetFunction.Match(sName, Sheets("平均単価").Range("A:A"))
　　　　　　　　Sheets("平均単価").Rows(nUnitPriceRow).Copy
　　　　　　　　.Rows(i + 1).PasteSpecial Paste:=xlPasteValues
　　　　　　　　.Cells(i + 1, 1).ClearContents
　　　　　　End If
　　　　Next i
　　End With
End Sub

お礼

mt2008さん、早速のご連絡ありがとうざいました！
いつありがとうございます！！

ご教授いただきました方法で実現できました！！
助かりました。
今回もまた勉強させていただきました。

ご回答に貴重なお時間を割いていただきまして誠に恐縮です。
この度は本当にありがとうございました。m(_ _)m

回答No.2

tom04

こんばんは！
今回はA列の結合はなし！というコトで良い訳ですね？

今回も「販売金額」Sheetにコマンドボタンを配置したいみたいなので・・・
一例です。

Alt+F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
一旦↓のコードをコピー＆ペーストしておいてください。

Sub 平均挿入()
Dim i As Long, j As Long, k As Long, c As Range, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("販売金額")
Set wS2 = Worksheets("平均単価")
Application.ScreenUpdating = False
j = wS2.Cells(1, Columns.Count).End(xlToLeft).Column
For i = wS1.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
Set c = wS2.Range("A:A").Find(what:=wS1.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
k = c.Row
wS1.Rows(i + 1).Insert
Range(wS2.Cells(k, "B"), wS2.Cells(k, j)).Copy wS1.Cells(i + 1, "B")
End If
Next i
Application.ScreenUpdating = True
MsgBox "処理完了"
End Sub

この準備ができた上で、コマンドボタンのコードを

Private Sub CommandButton1_Click()
Call 平均挿入
End Sub

と3行だけにします。

※　当然のコトながら一度だけの操作です。繰り返し操作してしまうとめちゃくちゃな表示になります。
すなわちわざわざコマンドボタンで行うより、
単にマクロを一度実行した方が間違いないような気がするのですが・・・

ご希望通りになりますかね？m(_ _)m

お礼

tom04さん、早速のご連絡ありがとうざいました！
何度もご連絡いただきまして誠にありがとうございます！！

ご教授いただきました方法で実現できました。

毎回ご丁寧な分かり易いご説明を添えていただきまして本当に恐縮です。

素人の私がひょんな事から業務でVBAでの業務効率化を依頼されてしまい、テンパっておいる次第であります・・・

早く学習して色々と覚えたいのですが、とりあえず今は言われた課題をクリアすることが第一となってしまっている状況です。

また今後も同じような質問を繰り返すことになってしまうと思うのですが、今後すこしづつでも学習して理解を深めていきたいと思います。

この度は本当にありがとうございました。m(_ _)m