[[20040611154324]]　『マクロにてINDEX+MATCH関数を使うには』（ガッツ）　ページの最後に飛ぶ

『マクロにてINDEX+MATCH関数を使うには』（ガッツ）

 　 　　A　　B　　C　　D　　E
 1　　　　　A級　B級　C級　D級
 2　りんご　50　　35　20　　15
 3　バナナ　30　　20　15
 4　みかん　　　　45　30　　20
 5　メロン　90　　65　50　　45
 ・
 ・
 ・
上記のような表があります。
マクロにてINDEX+MATCH関数を使って別のシートに結果を表示したいのですが、
ご教授いただけたら幸いです。
別のシートにも種類の違いはありますが、同じような表があり、結果は一つのシートに
出すようにしたいのです。
EXCELで作りましたが、一つのシートの結果は出せたのですが、シートが複数あるのでだめでした。
結果は足したり引いたりはしないで、ただ表示させるだけです。
Ver.EXCEL2002です。

 VBAでワークシート関数を使うときは、 WorksheetFunctionプロパティを使うと
 使用することが出来ます。

 使える関数には制限があるので、詳しくはVBAのヘルプを読んで見て下さい。

  (INA)

早速のご教授ありがとうございます。

    Dim myrange As Range
    Dim hinmei As Range
    na = ActiveSheet.Name
    Set myrange = Worksheets(na).Range("b2:e53")
    Set hinmei = Worksheets(na).Range("a2:a5")
    n = Inputbox("状態は？")
    d = InputBox("品名は？")
    answer = Application.WorksheetFunction.Index(myrange, Match(n, myrange, 0), Match(d, hinmei, 0))
　　sheet("結果").select    
    Range("a1") = d    
　　Range("a2") = answer

上記のように自分なりに作ってみましたが、やはりエラーとなってしまいます。
いろいろなところを見ましたが、どうしても解かりませんでした。

 手直しするとこんな感じでしょうか。
Index関数の行、列の配置が逆でした。
Match関数にもWorkSheetFunctionオブジェクトが必要です。
列の特定の部分で変数MyrangeはNGですね。等級見出しtoukyuの変数も追加。
    Dim myrange As Range
    Dim hinmei As Range
    Dim toukyu As Range
    Dim n As String, d As String
    Dim answer As Double
    na = ActiveSheet.Name
    Set myrange = Worksheets(na).Range("b2:e53")
    Set hinmei = Worksheets(na).Range("a2:a5")
    Set toukyu = Worksheets(na).Range("B1:E1")
    n = InputBox("状態は？")
    d = InputBox("品名は？")
    With WorksheetFunction
    answer = .Index(myrange, .Match(d, hinmei, 0), .Match(n, toukyu, 0))
    End With
    Sheets("結果").Select
    Range("a1") = d
    Range("a2") = answer
　
いかがでしょう。
(KAMIYA)

 INPUTBOXで入力後エラーが出てしまいます。
worksheetfunctionクラスのMATCHプロパティを取得できません

となります。

 個人的にはこの手のマクロでInputBoxを使用するのは感心しません。
単に数値入力ならあまり問題は発生しませんが、文字列の場合、
単語の入力ミス、全角半角の違い、半角全角スペースの有無など、
ちょっと見ただけではわからない入力ミスですぐ期待通りの動作を
しなくなります。今回のエラーもＭａｔｃｈ関数中で#N/A!（該当なし）
が出ているものと思われますので、入力値とリストのミスマッチが原因
だと推測します。
こちらの検証では正確に入力した場合には動作しますので、前述の
「入力ミス」
「リスト側の全角半角の区別」
「スペースの有無」
をご確認ください。
また、ここまでやっていまさらこう書くのも気が引けなくはありませんが、
このようにInputボックスでは入力値のミスマッチの可能性が非常に
高くなるので、上記以外の方法もご検討されてもよいかと思います。
（シート上でリストボックスからのシート選択、項目選択によって値を検索など）
(KAMIYA)

 ご指導ありがとうございます。
確かにそうだと思います。
ついでにで申し訳ないのですが、マクロでリストボックスは可能でしょうか？

 "結果"シートに配置したコントロールで制御する場合のサンプルです。
"結果"シートに「コントロールツールボックス」より
リストボックスをみっつ、コマンドボタンをひとつ、適当な位置へ配置してください。
この際、コントロールの名前がListBox1、ListBox2、ListBox3、CommandButton1
であることを確認してください。
ThisWorkbookオブジェクトへ下記コード
　
 Private Sub Workbook_Open()
 Dim sh As Worksheet
     If Worksheets("結果").ListBox1.ListCount = 0 Then
         For Each sh In ThisWorkbook.Worksheets
             If sh.Name <> "結果" Then
                 Worksheets("結果").ListBox1.AddItem sh.Name
             End If
         Next sh
     End If
 End Sub
　
"結果"WorkSheetオブジェクトへ同じく下記コード
　
 Dim sh As Worksheet
 Dim MyRow As Long
 Dim MyCol As Long
　
 Private Sub CommandButton1_Click()
 Dim MyRange As Range, hinmei As Range, toukyu As Range
 Dim n As String, d As String
 Dim answer As Double
　
 If Me.ListBox2.ListIndex < 0 Or Me.ListBox3.ListIndex < 0 Then
     MsgBox "リスト未選択です。"
     Exit Sub
 End If
     na = Me.ListBox1.Value
     Set sh = Worksheets(na)
     Set MyRange = sh.Range(sh.Cells(2, 2), sh.Cells(MyRow, MyCol))
     Set hinmei = sh.Range(sh.Cells(2, 1), sh.Cells(MyRow, 1))
     Set toukyu = sh.Range(sh.Cells(1, 2), sh.Cells(1, MyCol))
     d = Me.ListBox2.Value
     n = Me.ListBox3.Value
         With WorksheetFunction
         answer = .Index(MyRange, .Match(d, hinmei, 0), .Match(n, toukyu, 0))
         End With
　
     Range("a1") = d
     Range("a2") = answer
 End Sub
　
 Private Sub ListBox1_Change()
 Dim i As Long
 Dim C As Range
　
     For i = ListBox2.ListCount - 1 To 0 Step -1
         Me.ListBox2.RemoveItem (i)
     Next i
　   
     For i = ListBox3.ListCount - 1 To 0 Step -1
         Me.ListBox3.RemoveItem (i)
     Next i
　   
     Set sh = Worksheets(ListBox1.Value)
     MyRow = sh.Range("A2").End(xlDown).Row
     For Each C In sh.Range("A2:A" & MyRow)
         Me.ListBox2.AddItem C.Value
     Next C

     MyCol = sh.Range("B1").End(xlToRight).Column
     For Each C In sh.Range("B1").Resize(1, MyCol - 1)
         Me.ListBox3.AddItem C.Value
     Next C
　   
 End Sub
　
コード登録後、いったん保存して再度ブックを開くと有効になります。
(KAMIYA)

 KAMIYA様、大変有難う御座いました。
思うようになりました。
まだまだ勉強不足の自分がかなしいです。
これからもっと勉強していきます。

これを他のもので使いたいと思ってるのですがうまくいきません、おそらく

 Set MyRange = sh.Range(sh.Cells(2, 2), sh.Cells(MyRow, MyCol))
      Set hinmei = sh.Range(sh.Cells(2, 1), sh.Cells(MyRow, 1))
      Set toukyu = sh.Range(sh.Cells(1, 2), sh.Cells(1, MyCol))
の部分の数値を変えなければいけないと思うのですが、
このコードの意味を教えて頂けないでしょうか。
(yasu)

 その３ステートメントは次の書式で共通している。
　
Set Object変数 = Object取得式
Object取得式で取得したオブジェクトへの参照をオブジェクト変数へ格納する。
　
変数shはモジュールレベルの変数でListBox1_Changeイベントプロシージャでその内容が
取得されている。取得されるのはListBox1の値のシート名を持つシートである。
（なお、Workbook_Openで宣言されているプロシージャレベルの変数shとは直接関係がないが、
双方で変数名が同じであるため、紛らわしい使い方と言える）
　
変数myRow、myColも同じくモジュールレベルの変数でListBox1_Changeイベントプロシージャで
その内容が取得されている。
変数myRowは変数shで取得されているシートのセルA2からEnd(xlDown)で取得できるセルの
行番号が格納される。
変数myColは変数shで取得されているシートのセルB1からEnd(xlToRight)で取得できるセルの
列番号が格納される。
　
Cells(行番号, 列番号)　でそれぞれ行番号、列番号を指定することでRangeオブジェクトを
取得する。
Range(Range1, Range2)　でRange1とRange2を対角にもつ矩形範囲を取得する。
　
モジュールレベル変数はこのケースではとくに必須ではないため、
プロシージャごとに取得すべきものと判断する。
　
なお、回答中のKAMIYAは2004年時点での私の旧HNである。
であるからして、コードの解説は責任を取らねば（笑
上記説明で分からない場合は、新規に質問してみてもよいかと。
（みやほりん）

上のりんごのリストボックスを使うマクロなら出来たのです。
今回僕が使用したい表と行と列の位置が違うだけなので上記質問の部分が
わかれば出来ると思ったのですが・・・。
初心者という言い訳はしたくないですが、理解できませんでした。すいません。
詳細を説明するのでもう一度だけ回答お願いできればと思います。
今回、品名にあたる部分がB列の5、6行目から（2行づつセルを結合してあります）
状態にあたる部分が4行のC列から、になっております。
よろしくお願いします。
（yasu）

 ＞表と行と列の位置が違うだけ
　　　　　　　　　　　　 ^^^^ とありますが、
　
＞2行づつセルを結合してあります
ともありますので、仕様的には「まったく違うレイアウト」です。
適当に数字をいじれば適用できる、という考えでマクロに取り組むなら、
甘いと言わざるを得ません。
　
もし書いておられるような表の位置関係なら、数箇所の書き換えでは
済みませんし、むしろ違った考え方で作る必要があるように思えます。
　
私自身は最近はまるごと作ることが時間的にも能力的にも
出来なくなってきていますので、新規質問がいいと思いますよ。
　
（その場合は、元データの状態を詳しくて維持していただくのがよいと思われます）
（みやほりん）

了解しました、ありがとうございます。
（yasu）

コメント返信：

[ 一覧(最新更新順) ]

[[20040611154324]] 『マクロにてINDEX+MATCH関数を使うには』（ガッツ ） ページの最後に飛ぶ

[[20040611154324]]　『マクロにてINDEX+MATCH関数を使うには』（ガッツ）　ページの最後に飛ぶ