VBA Vlookup関数で日毎に入力セルを可変で取得

お世話になっております。
VLOOKUP関数によって取得される値のセル番地を毎日変更したいと考えています。

左側画像はエクセルのSheet1で行に日付、列に品番が入っております。

これに右側画像のSheet2(画像添付が1枚しかできない為、
Sheet1に貼り付けたスクリーンショットですが、実際はSheet2のA:B列が参照先になります)
は前日に売れた商品(品番)と販売数がそれぞれ入っており
(Sheet2は毎日システムよりエクスポートしたデータを貼り付けます)
前日に売れた商品を翌日朝にマクロを実行して、Sheet1の1行目品番に対して参照先の
Sheet2のA:B列から一致する販売数をVLOOKUPで取得します。

VBAでは下記のようになると思います(マクロの記録で行っています)
※12日の販売数を取得するマクロです。

Sub test()
'
' test Macro
'

'
Range("B13").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R1C,Sheet2!C1:C2,2,FALSE)"
Range("B13").Select
Selection.AutoFill Destination:=Range("B13:G13"), Type:=xlFillDefault
Range("B13:G13").Select
End Sub

毎日の作業となる為、マクロを実行した際に常に前日の日付に対して
販売数の値を取得したいのですが、色々調べて見ましたが行き詰っています。
また、作業を休みの兼ね合いで毎日行わない可能性がある為、
可能であれば空白になっている任意の行に対して実行できればベストと考えています。

例：2月15日は日曜の為、16日(月)に14日･15日の集計を行う
　　※その際にSheer2のデータは14日･15日分をそれぞれ貼り付けてからマクロを実行します。

また、Sheet1のG列に関して#N/Aのエラーが出ていますが、これはSheet2のエクスポートデータに
該当の品番が含まれていない為(購入されていないという事になります)、#N/Aではなく
一致しない場合は0と表示させたいのですが可能でしょうか。

Excelのバージョンは2007になります。

どうぞよろしくお願い致します。

No.1 ベストアンサー 回答者： High_Score 回答日時： 2015/02/13 21:14

前提条件

・シート２の前日売上は、A列に品名、B列に数とする
・シート２に日付が見当たらないので、作業者が考慮する
使用法
１．望みの日付の売上をシート２に用意
２．望みの日付行のB列をアクティブにしてマクロ起動する
ctrol+nとかキー設定しておけば使い易いです。

Sub Macro1()
ActiveCell.Resize(1, 6).Formula = "=SUMIF(Sheet2!$A$2:$A$7,B$1,Sheet2!$B$2:$B$7)"
End Sub

説明
ActiveCell.Resize(1, 6)はアクティブにしたセルを起点に右に６セル選択することを意味します。
Formulaは関数式の意味。
つまりアクティブセルに関数式を代入し、右方向に６セル分ぐにーっと引っ張ることを意味ましす。
式の中身はご存じだと思います。該当なしの場合にゼロ表示するようSUMIFを使いました。

この回答へのお礼

ご回答ありがとうございます。
教えて頂きました方法で無事解決致しました。
こんなにシンプルな構文で実現できるとは思わず驚きました。
難しく考えすぎていましたが、なるほど！と思える簡潔な内容で大変勉強になりました。
この度はありがとうございました。

お礼日時：2015/02/14 12:49

No.3 回答者： High_Score 回答日時： 2015/02/14 09:26

No1です。

すみません、コピー＆値のみペーストを忘れてました。
End Subの上に下記を追加してください。

activecell.resize(1,6).copy
activecell.PasteSpecial Paste:=xlPasteValues

これをしないと翌日の処理で値が変わってしまいます。

No.2 回答者： WindFaller 回答日時： 2015/02/13 23:57

こんばんは。

もう少し、添付データが明確ならよいのですが……・
300%にしても、ボケてみえませんでした。
今のところ、分かる範囲内でしか回答ができませんので、的外れなら、レス不要です。

>VLOOKUP関数によって取得される値のセル番地を毎日変更したいと考えています。
ActiveCell.FormulaR1C1 = "=VLOOKUP(R1C,Sheet2!C1:C2,2,FALSE)"
（記録マクロだからでしょうけれど、R1C1方式にする必要はなく、Formula または、FormulaLocalのブロパティを使えばよいです。数式を置きに行くだけでは、マクロとしては、ほとんど意味がないような気がしますね。）しかし、おそらく、これは最後、定数化させてしまうような感じがします。

・Sheet2は、日付と販売数は、縦に使っているのですか？
・もしかして、Sheet2のデータは、A列の品番は、規則的に並んでいないのですか？
　
>※その際にSheet2のデータは14日･15日分をそれぞれ貼り付けてからマクロを実行します。【一部訂正】
・その日ずつに実行していくというのでしょうか？
Sheet2 に、その都度データ転記だけのために貼り付けておくとしたら、それは、うまくないなって思います。

どのぐらいのデータ量か分かりませんが、横に使えるものなら、使ったほうがよいし、日付を入れて残したほうがよいですね。ただし、順番などが統一されていないと、マクロによる貼り付けになって、本質問とはかけ離れていく話になります。

うまく並んでいれば、Sheet2自体が、データソースになるし、ピボットテーブルで表も作れるだろうし、マクロは不要になるのかもしれません。また、ピボットテーブルは、作りっぱなしではなく、データソースを書き狩れば、更新も可能です。

>#N/Aではなく一致しない場合は0と表示させたいのですが可能でしょうか。
　今の時点では、数式で処理するしかないと思いますが、数式ではなく、本格的なマクロのはじめの1歩はこのような感じです。あくまでも、サンプルです。

'//
Sub TestSample1()
　Dim i As Long
　Dim r As Range
　Dim v As Variant
　
　With ActiveCell.EntireRow
　If .Cells(1).Value >= Date Then MsgBox "Out of Date", 36: Exit Sub
　　For i = 2 To 6 'G列まで
　　　Set r = Worksheets("Sheet2").Columns(1).Cells _
　　　.Find(Cells(1, i).Value, _
　　　LookIn:=xlValues, _
　　　LookAt:=xlWhole)
　　　If Not r Is Nothing Then
　　　　v = r.Offset(, 1).Value
　　　　If v <> "" Then
　　　　　.Cells(i).Value = v
　　　　Else
　　　　　.Cells(i).Value = 0
　　　　End If
　　　End If
　　　Next i
　　End With
End Sub
'//

この回答へのお礼

ご回答ありがとうございます。
添付画像が分かりづらく申し訳ありませんでした。
画像添付サイズについて今後気をつけます。
記載のテストサンプルにて実行･結果が正しく求められました。

>・Sheet2は、日付と販売数は、縦に使っているのですか？
>・もしかして、Sheet2のデータは、A列の品番は、規則的に並んでいないのですか？
Sheet2のデータは受注システムよりエクスポートしたデータとなっており、
エクスポート後の形式が縦になっております。またA列の品番は規則的に並んでおりません。

>・その日ずつに実行していくというのでしょうか？
受注システムより前日の売れた商品･個数を抽出してデータを
エクスポートしておりますので、その日ずつに実行していく形となります。

NO.1の回答者様と甲乙つけがたいのですが、先にご回答を頂きました為
NO.1様にベストアンサーを差し上げたいと思います。

お礼日時：2015/02/14 13:02