excel VBAを使って、データを自動的に表示させたい！

質問

excelのVBA・マクロの書き方について教えてください！！

たとえば、商品データ(商品名、数量、産地、担当者、商品コード)を入力したシートがあります。
別のシートに、商品名と数量を入力するだけで、商品データを参照して、産地・担当者・商品コードが自動で入力されるマクロを作成したいです。
「商品名」だけを入力して、表示させることはできたのですが、
商品名が同じで、数量が違うものがあると、片方のものしか表示されません。
A・Ｂ列に「商品名」・「数量」を入力して、自動でデータが表示される方法はありますか？？

回答No.3

death_note · ベストアンサー

No.1です。

本当は関数を使うのがいいんですが、せっかくマクロを作ってみたようなので流用してみると、こんな感じでどうでしょうか？

Private Sub Worksheet_Change(ByVal Target As Range)
'商品名、数量が変わった時以外は終了
If Target.Column <> 1 Or Target.Column <> 2 Then
　　Exit Sub
End If
'検索用の「商品名」「数量」を取得
RefName = Target.Value
RefNum = Target.Offset(0, 1).Value
'「元データ」シートの上から最後まで探索
For iRow = 2 To 65535
　　'「商品名」と「数量」が一致した場合
　　If Sheets("元データ").Cells(iRow,1).Value = RefName And Sheets("元データ").Cells(iRow,2).Value = RefNum Then
　　　　'自動入力
　　　　Target.Offset(0, 2).Value = Sheets("元データ").Cells(iRow,3).Value
　　　　Target.Offset(0, 3).Value = Sheets("元データ").Cells(iRow,4).Value
　　　　Target.Offset(0, 4).Value = Sheets("元データ").Cells(iRow,5).Value
　　End If
Next
End Sub

回答No.6

imogasi

エクセルVBA程度では多列（多段）式検索は難しい。キー（列）が少ない場合は、関係列を定桁式（注）に結合して、その結合列で検索すると出来る。（注）右の列データが左へつめてこないようにする
ーーー
普通は第１列で抜き出したもの（行）を、ワークに取っておいて、そのワークのデータを対象に第２の検索をかける。これを最後の条件まで続ける。
ーー
データベース（ACCESSをはじめ）ではSQL文では簡単に複数条件を指定出来る。
ーー
エクセルでは苦肉の策として
マクロの記録で、データーフィルターフィルタオプションの設定
の操作を行う。
例データA１：D２　　条件セル
コード１ コード２ コード３
a z
ーーー
データセル
A３：D１１　　
コード１ コード２ コード３ 計数
a x aa 1
b z bb 2
c y cc 3
a z dd 4
b y ee 5
c x ff 6
b y ee 1
a z ss 2
ーー
コード
Sub Macro1()
Range("A3:D11").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"A1:C2"), CopyToRange:=Range("F1:I11"), Unique:=False
End Sub
A3:D11の１１ほか最下行やCopyToRange:=のセル範囲は適宜修正する。
ーー
結果
F1:I3
コード１ コード２ コード３ 計数
a z dd 4
a z ss 2
ーーーー
条件を１つ２つ３つでテストしてください。
３つでも２つでもうまく行くことテスト済み。
ーー
この方法に賛否、良い点・欠点はあろうが、エクセルの機能に沿った回答で、
こういう回答はVBのエキスパートでも、エクセルを知らないと思いつけないものだ。
そういう人は、該当データなどを配列に入れて選び出すようなことを考えるだろう。
ほかにMSクエリと言うのも有る。

お礼

ご回答ありがとうございます。
参考にします。

回答No.5

hallo-2007

No2です。補足がありましたね、以下参考にしてください
Private Sub Worksheet_Change(ByVal Target As Range)
For i = 2 To 100
If Sheets("元データ").Cells(i, 1).Value = Cells(Target.Row, 1).Value And Sheets("元データ").Cells(i, 2).Value = Cells(Target.Row, 2).Value Then Exit For
Next
Cells(Target.Row, 3).Value = Sheets("元データ").Cells(i, 3).Value
Cells(Target.Row, 4).Value = Sheets("元データ").Cells(i, 4).Value
Cells(Target.Row, 5).Value = Sheets("元データ").Cells(i, 5).Value
Cells(Target.Row, 6).Value = Sheets("元データ").Cells(i, 6).Value
End Sub
考えている間に既に回答がでていましたね。おまけです。

回答No.4

death_note

No.3の回答ですが、変なところがいくつかありましたねｗｗ
最初の判定と、検索文字列を作る所は適当に修正してください。

補足

最初の判定は修正できました。
しかし、検索文字列の部分が・・・
どんな風に修正すればいいですか？？

回答No.2

hallo-2007

商品データを商品名、数量で並び替えてあるか、ランダムなのかで処理が違ってきます。
データがランダムに並んでいるのであれば、データを最初から最後まで順に確認することになるので処理に時間がかかりますよね。
データの件数にもよりますが、こういった場合はデータを昇順に並び替えておいてください。
仮に関数案ですが
商品データ
　A　　　　B　　　C　　D　　　E
商品名　数量　産地　担当者　商品コード
商品あ　　１０
商品あ　　２０
商品い　　１０
・・・
とあったとして、別シート　A1セルに検索する商品名　を入力したとします。
A2セルに　=MATCH(A1,商品データ!A:A,FALSE)
A3セルに　=MATCH(A1,商品データ!A:A,TRUE)
と式を入れる表示したい商品名のある最初の行番号と最後の行番号がでます。
B1セルに　検索する　数量　を入れたとします。
=VLOOKUP(B1,INDEX(商品データ!B:B,A2):INDEX(商品データ!C:C,A3),2,FALSE)
産地が表示されるはずです。
マクロで行うにしても商品名で範囲を区切って　数量で今出来ているマクロを実行すればよいでしょう。

回答No.1

death_note

＞商品名と数量を入力するだけで、商品データを参照して、産地・担当者・商品コードが自動で入力されるマクロを作成したいです。
この（商品名と数量）に対して（産地・担当者・商品コード）は１対１の関係でしょうか？
１対１ならマクロでなくてもVLOOKUPなどの関数で実現できます。
１対多となると、出力が複数行になるのでマクロしかないと思います。

補足

(商品名と数量)に対して(産地・担当者・商品コード)は１対１の関係です。できたところまで表示します。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Offset(0, 1).Value = WorksheetFunction.VLookup(Target.Value, Sheets("元データ").Range("A2:F100"), 2, False)
Target.Offset(0, 2).Value = WorksheetFunction.VLookup(Target.Value, Sheets("元データ").Range("A2:F100"), 3, False)
Target.Offset(0, 3).Value = WorksheetFunction.VLookup(Target.Value, Sheets("元データ").Range("A2:F100"), 4, False)
Target.Offset(0, 4).Value = WorksheetFunction.VLookup(Target.Value, Sheets("元データ").Range("A2:F100"), 5, False)
End If
End Sub