Hlookup関数で日付を検索するとエラーが出てしまうが?

Question 98.2 Previous Next
Dataシートから Hlookupを使って当日と翌日の数字をそれぞれ取得し、集計シートの当該日付の欄にその Dataをコピーしたいのですが、検索値が日付の場合 Hlookupがうまく動いてくれません。
途中ですが、こんな感じで記述しています。
(Hlookupのところでとまっているので、「別シートの当該日付の欄にそのDataをコピー」 は手付かずですが。)
Sub データ取得()
Dim r As Range
Dim d As Date
   Set r = Worksheets("Data").Range("A1:E3")
   行番号 = 2
   検索値 = Date
   値 = Application.WorksheetFunction.HLookup(検索値, r, 行番号, False)
   Worksheets("集計").Select
   Range("B12").Value = 値
End Sub
Dataシートちなみに、Dataシートは 右図のような感じに数字が並んでいます。
集計シート たとえば今日が 9/17 だとすると、
そこから 9/17 の 800 という数字を抜き出して、

左図のような集計シートの 9/17 の売上欄に、その値をコピーしたいのです。
どのようにしたら良いか教えて頂ければ幸いです。
よろしくお願いします。
Answer   Copyright (C) 2009.9.17 永井善王
エラーメッセージ そうですね。マクロの 7行目のコード(赤字の行)で右図のエラーが出てしまいます。

HLOOKUP関数はワークシート関数ですから、通常はワークシートの中で利用します。 ちょっと試してみましょうか。
Dataシートの A5セルと A6セルは使っていないようですから、A5セルへ 9/17 と入力して、
A6セルへは =HLOOKUP(A5,A1:E3,2,FALSE) と入力してみましょう。 すると A6セルには 300 と表示されるはずです。
この数式は、あなたが作ったマクロの 4~7行目に相当するのに、エラーにはなりません。

次に、マクロの6行目のコードを下記のとおり修正してから実行し、エラーが出ないことを確かめてください。
  検索値 = CLng(Date)
CLng関数はデータ型変換関数の1つで、( )の中で与えられた値を長整数型に変換してくれます。 この場合、 ( )の中の変数「Date」の値は今日の日付、つまり、日付型の 9/17 ですから変換すると 40073 となります。

一方、Dataシートの A1:E1セル範囲は表示形式が日付型に設定されているので、入っている値が見掛け上は月日に見えます。 しかし、実体は A1セルには「40071」、B1セルには「40072」、C1セルには「40073」、D1セルには「40074」、E1セルには「40075」という「日付シリアル値」と呼ばれる値が入っています。
HLOOKUP関数は、この日付シリアル値で検索しますから、マクロの6行目のコードで変数「検索値」に設定する値は日付シリアル値にする必要があります。
ここでは日付シリアル値についての詳細な説明は省略しますが、Windows版Excelでは 1900/1/1を 1として 1日経過するごとに 1づつ増える整数です。 くわしく知りたい場合は「シリアル値とは」で検索してみてください。

さらに、「今日が 9/17 だとすると、そこから 9/17 の 800 という数字を抜き出して・・」とあるので、マクロの5行目のコードを
 行番号 = 3 に修正する必要もあるでしょう。
そして、「集計シートの 9/17 の売上欄に、その値をコピーしたいのです。」とありますが、HLOOKUP関数の問題が解決すれば自力でやれる範囲内でしょうから、頑張ってください。
ただし、今度は VLOOKUP関数を使うことになるでしょう。 きっと、実力が付くと思いますよ。

 

Excel VBA Macro