|
|
| Excel VBA Macro ・ 値の操作 ・ FAQ |
|
日付型セルの日にちをMatch関数で検索するには？
|
|
|
|
Question 019p |
|
 |
 |
|
|
先日は回答いただきまして有り難うございました。
指定された日にちで検索した結果を利用してセル範囲をコピーするには？ として教えていただいたように書いて動かしてみましたが、megboxで [見つかりませんでした。] と表示されてしまいます。
何度やっても、日にち
の言葉を変えても
ダメでした。どうしてでしょうか？ 教えて下さい！
なお、ご指示をいただきましたので、ダメだったファイルを添付でお送りします。よろしくお願いします。
|
|
Answer |
Copyright (C) 2008.5.30 永井善王 |
|
|
お送りいただいたファイルを拝見しました。
結論から申し上げますと、あなたのデータは、そのままでは Match関数での検索に適さないからと言えましょう。
Match関数の仕様は Excelのヘルプまたは拙著「Excel VBAマクロ組み方講座 プロの定番・裏技・合わせ技編 」のP.385を読んで理解していただきたいですが、Match関数の検査値として使用できる値は数値、文字列(長さゼロの文字列不可)、論理値、これらの値に対するセル参照に限られます。
以下、具体的に解説します。
【第1図】 「指定表」シート
データ型の問題
「指定表」シートの 6行目のB列以後のセルに入っている値は、1日、2日、3日・・ であると聞いていました。
ところが、送っていただいたファイルを見ると
第1図のように 1、2、3 ・・ と、年月日の中の「日」だけを表示するようになっています。(注1)
そこで、B6セルを見てみると、「大元」シートの A6セルを参照する数式が入っています。
【第2図】 「大元」シート
続いて、「大元」シートの A6セルを見てみると、【第2図】のように表示は 「5月1日」ですが、実際には 「2008/5/1」 という年月日が入っています。(注2)
(注1) [セルの書式設定]-[表示形式]の[分類(C)]が「ユーザー定義」、[種類(T)]が「d」
(注2) [セルの書式設定]-[表示形式]の[分類(C)]が「日付」、[種類(T)]が「3月14日」
【第3図】 「印刷」シート
先日、「印刷」シートのA1セルの値は、例えば「1日」とご説明いただきました。
が、今回のファイルでは【第3図】の 「5月2日」のようになっています。(注2)
ここまでの解説で察しがついたかと思いますが、3つの値は、どれも、日付型です。
解決策
いろいろな方法があるかと思いますが、あなたのデータを一時的に Match関数に適合するデータ型に変換してから、Match関数を実行させると良いかと思います。
ちなみに、検索値である 「印刷」シートの A1セルの日付は、下記のコードで変換できます。
年月日 = Worksheets("印刷").Range("A1").Value
検索値 = Year(年月日) * 10000 + Month(年月日) * 100 + Day(年月日)
検索範囲となる 「指定表」シートの 6行目セル範囲 (B6〜) の日付は、下記のコードで変換しながら、仮シートの 6行目 (B6〜) へセットします。
右端列 = 7 'ここでは G列にしている
For 列 = 2 To 右端列
Worksheets("指定表").Activate
YMD = Year(Cells(6, 列)) * 10000 + Month(Cells(6, 列)) * 100 + Day(Cells(6, 列))
Worksheets("大元").Activate 'ここでは「大元」シートを仮シートにしている
Cells(6, 列).Value = YMD
Next
【第4図】 「大元」シート
この【第4図】は、「指定表」シートの日付が変換されて、6行目セル範囲 (B6〜) にセットされた状態を表しています。
Match関数で必要な 「検査値」 と 「検査範囲」 が以上で整いましたから、下記のようにコーディングして実行してみましょう。 ただし、先回は本の裏技サンプルに倣って Evaluateメソッドを利用しましたが、今回は本の定番サンプル(P.167)に倣って WorksheetFunctionプロパティで取得します。
Set 検査範囲 = Worksheets("大元").Range("B6:G6")
照合の型 = 0
見つかった位置 = Application.WorksheetFunction.Match(検索値, 検査範囲, 照合の型)
MsgBox "見つかった位置は " & 見つかった位置, , "すぐマク"
すると、右図のように表示されて、
5月2日は「大元」シートの6行目のB列から数えて「2」番目の位置、つまり、C6セルにあることが分かります。
仮シートである「大元」シートは、本来のシートである「指定表」シートと同じ位置のセルに変換後の日付を入れてありますから、
5月2日は「指定表」シートの C6セルにあることになります。 わかりますね。
この変数「見つかった位置」の値を利用してコピーする範囲を選択してクリップボードへコピーしてしまいます。コードは下記マクロの中の青字て示した2行です。
こうしておけば、あとは任意のセルへ貼り付けることが可能になります。
Sub 日付型セルの日にちをMatch関数で検索して対応データをコピーする()
年月日 = Worksheets("印刷").Range("A1").Value
検索値 = Year(年月日) * 10000 + Month(年月日) * 100 + Day(年月日)
照合の型 = 0
Worksheets("大元").Activate
Range("B6:G6").ClearContents
右端列 = 7 '※1 ここでは G列にしている
For 列 = 2 To 右端列
Worksheets("指定表").Activate
YMD = Year(Cells(6, 列)) * 10000 + Month(Cells(6, 列)) * 100 + Day(Cells(6, 列))
Worksheets("大元").Activate '※2 ここでは「大元」シートを仮シートにしている
Cells(6, 列).Value = YMD
Next
Set 検査範囲 = Worksheets("大元").Range("B6:G6")
On Error GoTo 見つからない場合
見つかった位置 = Application.WorksheetFunction.Match(検索値, 検査範囲, 照合の型)
'
Worksheets("指定表").Activate
Range(Cells(6, 見つかった位置 + 1), Cells(9, 見つかった位置 + 1)).Copy
Exit Sub
'
見つからない場合:
MsgBox 検索値 & " は見つかりません"
End Sub
【第5図】 「指定表」シート
上記マクロを実行すると、「指定表」シートは【第5図】のように、コピーされた範囲が枠線で囲まれて明示された状態になります。
先回ご提示いただいた各シートは、質問用として貴方なりに考えて整理されたのでしょうね。
しかし、本番シートとはデータ型が違っていて望ましい回答にならず、結果として二重手間になってしまいました。
通常、本番シートは、ここで示した図よりも複雑ですから、今回の回答を適用するためには更に一段の努力が必要になるでしょう。 頑張って成し遂げてください。 朗報をお待ちしています。
|
|
|
おかげさまで、完成しました。有難うございました。 とても便利になりました、助かりました！！！
また、何かありましたらお力になっていただけたら幸いです。
|
|
|
|
| |
|
|
