[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『エクセルマクロで複数条件での検索方法』（もみじ坂）
度々お世話になっています
マクロを組み立てるのはホント難しいですね −ｘ−
ネットで調べてよさそうなものを組み立てるだけではなかなか前に進まない。。。
◆やりたいこと
A2に入力してる会社の価格を検索して表示される方法です。
いろんなところを参考しながらやってみましたが、
うまく作動しないのです。
何がいけないのでしょうか？わかりましたら教えてくださいよろしくお願いします。
そんなに違ってはいないと思いますが。。。。。自信ないです ≧ｘ≦
With Worksheets("検索")
For r = 3 To 4
Ap = Application.Match(.Cells(r, 1).Value, Worksheets("list").Range("2:2"), 0)
If IsNumeric(Ap) Then '値があるとき
.Cells(r, 1).Value = Worksheets("list").Cells(3, Ap).Value
Else '値がないとき
MsgBox "この番号は登録していません。"
End If
Next r
End With
※修正しました 【検索】 [A] [B] [C] [D] [2] 028023 「3」 NO 商品名前 企画6 価格 「4」 A01 エンピツ1 1角形 301 「5」 A03 エンピツ3 3角形 303
【list】 [B] [C] [D] [E] [ｆ] [ｇ [ｈ] [i] [j] [k] [l] [m] [n] [o] 「1」 「2」 画面固定 027021 027638 028023 028963 「3」 A01 エンピツ1 1角形 画面固定 101 201 301 401 A02 エンピツ2 2角形 画面固定 102 202 302 402 A03 エンピツ3 3角形 画面固定 103 203 303 403 A04 エンピツ4 4角形 画面固定 104 204 304 404
＜ 使用 Excel：Excel2013、使用 OS：Windows8 ＞
For r = 4 To 5
Ap = Application.Match(.Cells(r, 1), Worksheets("list").Range("2:2"), 0)
If IsNumeric(Ap) Then '値があるとき
.Cells(r, 4).Value = Worksheets("list").Cells(3, Ap).Value
（マナ） 2014/07/09(水) 21:16
For r = 3 To 4 ’検索シートの３行目〜４列目
’検索シートA2とリストシート2行目と同じ番号を探す
Ap = Application.Match(.Range("A2"), Worksheets("list").Range("2:2"), 0)
If IsNumeric(Ap2) Then '番号があるとき
’検索シート3行目の１列目の番号を Apで探しでた行＋同番号の数字を抜き出す
.Cells(r, 4).Value = Worksheets("list").Cells(3, Ap).Value
Else '値がないとき
MsgBox "この番号は登録していません。"
End If
End With
説明は伝わりますか〜
自分のやってるコードがあってるかどうかも分からないので、すみません ≧ｘ≦
でも欲しい結果は間も変わらずなにも変化がないのです
困ってます。教えていただけますと大変助かりますよろしくお願いします。
（もみじ坂） 2014/07/10(木) 09:43
Next r はどこ？
（jjj） 2014/07/10(木) 10:58
すみません。コピーしたときに抜けてたみたいです。
質問
If IsNumeric(Ap2) Then '番号があるとき の解釈であってますか？
なぜか値がありませんをメッセージがでてしまいます。 数字の箇所は文字列で設定しています。
原因がわかりましたら教えていただけますと大変助かります。
（もみじ坂） 2014/07/10(木) 13:46
（もみじ坂） 2014/07/10(木) 20:24
直接の回答ではないですけれど、VLOOKUP ではダメなのでしょうか？
B4 =IFERROR(VLOOKUP(A4,list!$B$3:$O$1000,2, FALSE),"")
のような。
（Mook） 2014/07/10(木) 20:37
回答ありがとうございます (T０T)
いつもすみません。
もちろん関数も試しました。
× VLOOKUP関数 ⇒ 条件が一つなので欲しい結果は得られなかったです
× sumproduct 関数 ⇒ 複数条件設定できますが、値が「0」になりますし。
（これで使えそうと思ったんですが。ガックシでした。）
結果マクロにチャレンジしてみたんですが。。。 セルからセルへの単純コードはようやくわかるようになっても。 主の指令文が分からずなので、立ち止まってしまいます。
※欲しい結果は D列の価格なのです。 【検索】 [A] [B] [C] [D] [2] 028023 「3」 NO 商品名前 企画6 価格 「4」 A01 エンピツ1 1角形 301 「5」 A03 エンピツ3 3角形 303
A2が「027021」 になりますと D列の価格が変わります。
【検索】 [A] [B] [C] [D] [2] 027021 「3」 NO 商品名前 企画6 価格 「4」 A01 エンピツ1 1角形 101 「5」 A03 エンピツ3 3角形 103 （もみじ坂） 2014/07/10(木) 21:11
それでも VLOOKUP でできるような。 D4 =IFERROR(VLOOKUP(A4,list!$B$3:$O$1000,10+MATCH($A$2,list!$L$2:$O$2,0),FALSE),"") （Mook） 2014/07/10(木) 21:21
ありがとうございます ヾ(嬉￫ω￩嬉)ノ
出来ちゃいました〜 ♪
こんなに凄い関数私の頭では考え付かないです。
凄くうれしいです。
ホントに感謝でいっぱいです。
また何かありましたらこりずに教えてください。よろしくお願いします。
（もみじ坂） 2014/07/10(木) 21:50
Sub test()
Dim 価格表 As Range
Dim 会社コード As Range
Dim 商品コード As Range
Dim Ap, Ap2
Set 価格表 = Sheets("List").Range("L3:O6")
Set 会社コード = 価格表.Rows(1).Offset(-1)
Set 商品コード = 価格表.Columns(1).Offset(, -10)
With Worksheets("検索")
For r = 4 To 5
Ap = Application.Match(.Cells(r, 1), 商品コード, 0)
Ap2 = Application.Match(.Cells(2, 1), 会社コード, 0)
If IsNumeric(Ap) And IsNumeric(Ap2) Then
.Cells(r, 4).Value = 価格表.Cells(Ap, Ap2).Value
Else
.Cells(r, 4).Value = "未登録"
End If
Next
End Sub
（マナ） 2014/07/10(木) 22:38
コードをありがとうございます ヾ(嬉￫ω￩嬉)ノ 自分のもとと比べると全然違うですね
>私のPCだとスクロールしないと見えませんでした すみません。数字は後付なのです。 マナ様が1回目見たときは何もないはずです＾＾；；
早速検証してみました。 結果は何も表示されずに 元あった数式が消えているので、何かしらは読み取っているのでしょうか？ ただ、エラーメッセ―もないです。
文字列が問題でしょうか？ 少し質問させてください ＾０＾/
Sub 計算()
Dim 価格表 As Range
Dim 会社コード As Range
Dim 商品コード As Range
Dim Ap, Ap2
Set 価格表 = Worksheets("list").Range("L2:CZ200")
Set 会社コード = 価格表.Rows(1).Offset(-1) ’★ A列の最終行ですが？
Set 商品コード = 価格表.Columns(1).Offset(, -10) ’★ 2行目の最終列ですか？
With Worksheets("検索")
For r = 4 To 5 ’★4行目〜５列目
Ap = Application.Match(.Cells(r, 1), 商品コード, 0)
Ap2 = Application.Match(.Cells(2, 1), 会社コード, 0)
If IsNumeric(Ap) And IsNumeric(Ap2) Then
.Cells(r, 4).Value = 価格表.Cells(Ap, Ap2).Value
Else
.Cells(r, 4).Value = "未登録"
End If
Next
End With
End Sub （もみじ坂） 2014/07/11(金) 21:28
[A] [B] [C] … [L] [M] [N] [O]
「1」
「2」 02702 027638 028023 028963 ←会社コード
「3」 A01 101 201 301 401
「4」 A02 102 202 302 402
「5」 A03 103 203 303 403
「6」 A04 104 204 304 404
↑
商品コード
価格表 ←価格が入力されているセル Range("L3:O6")
価格表.Rows(1).Offset(-1) ←価格表の１行目Range("L3:O3")の１行上＝Range("L2:O2")
価格表.Columns(1).Offset(,-10) ←価格表の１列目Range("L3:L6")の10列左＝Range("B3:B6")
（マナ） 2014/07/11(金) 22:25
ただ、欲しい結果は得られなかったんですが、
原因がわかりますか？
（マナ） 様の方では価格表示されているんでよね
なぜでしょうか？
範囲等を少しいじったぐらいで特に何もしていないのですが（？？）
' 価格表 ←価格が入力されているセル Range("L3:CZ500")
Set リスト = Worksheets("list").Range("L3:CZ500")
' 価格表.Rows(1).Offset(-1) ←価格表の１行目Range("L3:O500")の１行上＝Range("L2:O2")
Set 会社コード = list.Rows(1).Offset(-1)
' 価格表.Columns(1).Offset(,-10) ←価格表の１列目Range("L3:CZ500")の10列左＝Range("B3:B500")
Set 商品コード = list.Columns(1).Offset(, -10)
With Worksheets("検索")
For r = 4 To 4
Ap = Application.Match(.Cells(r, 1), 商品コード, 0)
Ap2 = Application.Match(.Range("A2"), 会社コード, 0)
If IsNumeric(Ap) And IsNumeric(Ap2) Then
.Cells(r, 4).Value = リスト.Cells(Ap, Ap2).Value
Else
.Cells(r, 4).Value = "未登録"
End If
Next
End With
（もみじ坂） 2014/07/11(金) 23:40
特に ＞リスト.Cells(Ap, Ap2).Value の部分は、理解できていますか。
理解できているなら、ステップインで実行し 変数ApやAp2に何が入っているか確認してみてください。
（マナ） 2014/07/12(土) 10:19
＞リスト.Cells(Ap, Ap2).Value .Cells(検索出た商品コード, 検索出た会社コード) という意味であってますか？
ステップインをしたんですが原因が不明です（？？）
Set 商品コード = list.Columns(1).Offset(, -10) でエラー424 「 オブジェクトが必要です。」 とでています。
オブジェクトというのは何のことでしょうか？
Set リスト = Worksheets("list").Range("L3:CZ500")
と
Set 会社コード = list.Rows(1).Offset(-1)
は値なしです。この段階ではそうなると思いますが。
ここでとまっているので、先は進んでないですが、、、 （もみじ坂） 2014/07/12(土) 11:36
＞Set 会社コード = list.Rows(1).Offset(-1) ＞Set 商品コード = list.Columns(1).Offset(, -10) ↓ Set 会社コード = リスト.Rows(1).Offset(-1) Set 商品コード = リスト.Columns(1).Offset(, -10)
"リスト"という範囲のRows(1)のOffset(-1)という意味です。
同様に、リスト.Cells(Ap, Ap2).Valueは、 "リスト"という範囲のCells(Ap, Ap2)のValueです。
Cells(Ap, Ap2)は、相対的な位置を行と列で表現しています。 つまり "リスト"という範囲の縦位置（行）がApで、横位置（列）がAp2のセルのValueとなります。
検索結果（Application.Matchの結果）が、Ap=1、Ap=1の場合、
Range("L3:CZ500").Cells(1, 1).Value
なので、その場合は、セルL3の値となります。
Apは、商品コードの検索結果で、リスト内の縦位置（行） Ap2は、会社コードの検索結果で、リスト内の横位置（列） というのは、わかりますよね。
Ap、Ap2だと変数名がわかりにくかったので、↓のように修正しました。
Option Explicit
Sub 計算()
Dim リスト As Range '価格セル範囲
Dim 会社コード As Range '会社コードセル範囲
Dim 商品コード As Range '商品コードセル範囲
Dim 商品行 '商品コード検索結果（行）
Dim 会社列 '会社コード検索結果（列）
Dim r As Long
Set リスト = Worksheets("list").Range("L3:CZ500")
Set 会社コード = リスト.Rows(1).Offset(-1)
Set 商品コード = リスト.Columns(1).Offset(, -10)
With Worksheets("検索")
For r = 4 To 5
商品行 = Application.Match(.Cells(r, 1), 商品コード, 0)
会社列 = Application.Match(.Cells(2, 1), 会社コード, 0)
If IsNumeric(商品行) And IsNumeric(会社列) Then
.Cells(r, 4).Value = リスト.Cells(商品行, 会社列).Value
Else
.Cells(r, 4).Value = "未登録"
End If
Next
End With
End Sub
（マナ） 2014/07/12(土) 13:03
何度もありがとうございます。
検索している範囲はわかりました、
なぜこのエラーがわからないです。
いろいろためしてみても 原因が見つからない事態です。
For r = 4 To 5
でやった時は
商品行 = Application.Match(.Cells(r, 1), 商品コード, 0)
は検索値が エラー2024
商品行 = Application.Match(.Cells(r, 1), 商品コード, 0)
検索値が「3」 つまり リスト L3に同じ番号を見つけたてことですよね
調べたところ
エラー2024＝#N/A＝検索値に値が入力されていない という意味でした
なので
For r = 4 To 5
を
For r = 10 To 4
とずらしてやってみたところ
ステップインが
For r = 10 To 4 （検索 （Range""A10:D40"）
のつぎが
End With
に飛んでしまいます。
取得値 が Empty です。
理由がわからないと何をしていいのやら..
このコードを使用する際書式設定上 気を付けることはありますか？
（もみじ坂） 2014/07/12(土) 17:30
＞For r = 4 To 5
意味がわかっていますか？ 検索シートの、まずA4、次にA5の内容を条件に検索するということです。
For〜Next構文の使い方に誤解があるかもしれせん。 http://excelvba.pc-users.net/fol6/6_3.html
＞は検索値が エラー2024
一致する商品コードが見つからなかったということです。 ＞検索値が「3」 つまり リスト L3に同じ番号を見つけたてことですよね
違います。（「リスト L3」の意味がよくわかりませんが） 商品コードの３番目、つまりセルB5に同じ番号を見つけたということです。 つまり、価格は、リストの３行目（listシート全体では５行目）にあるということです。 価格がリストの何列目にあるかは、会社列で決まります。
まずは関数で結果が正しく表示されるのと同じデータで試していただけますか？
（マナ） 2014/07/12(土) 18:54
お手数をおかけして申し訳ございませんでした。 <(_ _)>
＞For r = 4 To 5
完全に私の勘違いでした ＾＾；；
４行目〜5列目かと。。。。
行を調節したところ完ぺきに取得値を表示してくれました
欲しい結果が得られてホントにうれしいです ヾ(嬉￫ω￩嬉)ノ
そして大変勉強になりました。ありがとうございました。
また何かありましたらよろしくお願いします ｍ(＿ ＿)ｍ
（もみじ坂） 2014/07/12(土) 19:08
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.