指定された日にちで検索した結果を利用してセル範囲をコピーするには?

Question 018p Previous Next
168ページ 3章3-2 裏技[1] ◆ 環境・・ Excel 2003 & Windows XP
詳細は こちら
こんな感じで指定表があります。
これを、例えば1日を探して、その下のA・B・Cの三人の勤務をコピーして、違うシートに貼り付けたいと思います。
裏技の中から、これが最適のVBAかと思って色々やってみましたが出来ませんでした。
    Worksheets("印刷").Activate
    検索値 = Range("a1").Value
    検査範囲 = Sheets("指定表").Range("b6:b10")
    照合の型 = 0
    On Error GoTo 見つからない場合
    ワークシート関数式 = "Match(" & 検索値 & "," & 検査範囲 & "," & 照合の型 & ")"
    見つかった位置 = Evaluate(ワークシート関数式)
の、ような感じでやってみましたが。日にちのセルを示してくれませんでした。どうすれば良いでしょうか?
また、matchでは複数のセルをアクティブに出来ないとすると、どの関数が最適なのでしょうか?
これまで、先生の本には助けていただきましたが、今回は無理でした。お願いします、教えて下さい。
Answer   Copyright (C) 2008.5.22 永井善王
拙著「Excel VBA マクロ組み方講座 プロの定番・裏技・合わせ技[編]」をご購読、ありがとうございます

あなたが参考にされた Match関数のサンプルで良いと思いますよ。 ただし、もう一つ、範囲を選択してコピーするコードが必要になります。 さっそく、ポイントごとに解説します。

1. 検索値を取得するコード
あなたの場合、Evaluateメソッドを介して実行する Match関数の検索する値として
「印刷」シートのA1セルの値を指定したいのですから、
コードを下記のように修正する必要があります。
    Worksheets("印刷").Activate
    セル値 = Range("a1").Value
    検索値 = """" & セル値 & """"
Evaluateメソッドの引数にするためには、必ず文字列にしなければなりません。 詳細はヘルプで見ていただけます。

2. 検索範囲を指定するコード
検索する範囲は 「指定表」シートのセル範囲 A6:G6 ですから、コードを下記のように修正します。
そして、EvaluateメソッドでMatch関数を実行させて、A6セルを 1 とした位置を取得します。
    検査範囲 = "指定表!a6:指定表!g6"
    照合の型 = 0
    On Error GoTo 見つからない場合
    ワークシート関数式 = "Match(" & 検索値 & "," & 検査範囲 & "," & 照合の型 & ")"
    見つかった位置 = Evaluate(ワークシート関数式)
    MsgBox "見つかった位置は " & 見つかった位置    

3. 見つかった列のセル範囲をコピーするコード
この例では、見つかった位置として 3 が返ります。
3(列目)とはC列に相当しますから、Cellsプロパティを利用して
Cells(6,3) のように記述すれば Range("C6") と同じことになります。
よって、コピーするコードを下記のように追加します。
    Worksheets("指定表").Activate
    Range(Cells(6, 見つかった位置), Cells(9, 見つかった位置)).Copy
    Exit Sub

見つからない場合:
    MsgBox 検索値 & " は見つかりません"
以上で解決できるかと思いますが、結果をご連絡ください。


 

Excel VBA Macro