ＥXcelオートフィルタのオプション設定で条件として変数をマクロで組むには？

質問

オートフィルタ機能を使い、指定期間内のデータを抽出するようにマクロを組みたい。指定期間が一定ではないので変数を設定したいのですがうまくいきません。下記はマニュアルで入力したものをマクロに記録したものです。指定期間は他のBookのセルに入力画面として設けてあります。例えばBook2／B1(開始)～B2(終了)。

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=2005/8/21", Operator:=xlAnd _
, Criteria2:="<=2005/9/20"
Range("C1").Select
End Sub

回答No.6

KenKen_SP

#4 ご指摘のとおり、NumberFormat は NumberFormatLocal の方がベター
というより、NumberFormatLocal にすべきでした。

Wendy02 さん、ありがとうございます。

AutoFilter で検索値に日付を設定するときには、検索値と同一の書式の
日付を Criteria に渡してやらないとうまく抽出できません。

この辺で私も悩んだ経験があります。やっかいですね。

お礼

ありがとうございます。出来ることを確認致しました。

回答No.5

imogasi

私の下記例では
Book2のSheet2のB1に2005/5/1
B2に2005/5/20 が入っています。
Book5のSheet3のA1:B10に
日付 分類
2005/1/2 a
2005/3/1 s
2005/5/1 d
2005/7/12 f
2005/5/12 g
2005/8/23 h
2005/5/25 j
2005/5/19 fg
2005/6/29 t
があります。
Book5の標準モジュールに下記があります。
Sub test02()
Workbooks.Open "Book2.xls"
x = Workbooks("Book2.xls").Worksheets("sheet2").Range("B1").Value
MsgBox x
y = Workbooks("Book2.xls").Worksheets("sheet2").Range("B2").Value
MsgBox y
Workbooks("Book5.xls").Worksheets("Sheet3").Activate
ActiveSheet.Range("a1:B7").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=" & x, Operator:=xlAnd, _
Criteria2:="<=" & y
End Sub
を実行すると
日付 分類
2005/5/1 d
2005/5/12 g
2005/5/19 fg
となりました。
ブック名、シート名の対応を誤らずに自分の場合に置き換え、実行してみてください。

お礼

できました。ありがとうございます。

回答No.4

Wendy02

こんばんは。

rcn4132さん、KenKen_SPさんへ

おせっかいのようですが、
KenKen_SPさんのコードの中の
＞strFormat = .Range("A2").NumberFormat
　　　　'オートフィルター設定

そこは、NumberFormtLocal のほうが良いようですよ。
一度、比較してみてください。

後は、KenKen_SPさんとは変わらないので、コードは書きませんが、日付の検索は、結構、むつかしいです。

AutoFilterのデータどおりの検索値にするためには、Format関数で、Selectionでしたら、Selection.Cells(2,1).NumberFormatLocal にあわせないといけないわけです。

(Selectio.Cells(2,1)というよりは、Range("A1").CurrentRegion.Cells(2,1)... ですが。）

どこかで、私も書いたけれども、AutoFilterは、まだ、バグのような、おかしな部分があるので、記録マクロ≠VBAコードなのです。

お礼

できました。ありがとうございます。

回答No.3

papayuka

どう上手く行かないのでしょう？
条件指定部分を置き換えてあげればOKかと思いますが、、、

Sub Test()
Dim st As String, ed As String
　ThisWorkbook.Activate
　With Application
　　　st = Application.InputBox("yyyy/m/dで入力", "開始日", Type:=2)
　　　ed = Application.InputBox("yyyy/m/dで入力", "終了日", Type:=2)
　End With
　ActiveSheet.Range("A1").AutoFilter Field:=1, _
　　　　　　Criteria1:=">=" & st, Operator:=xlAnd, _
　　　　　　Criteria2:="<=" & ed
End Sub

上記が上手くいくなら、条件を書き換えればOK

st = Workbooks("Book2.xls").Worksheets(1).Range("B1").text
ed = Workbooks("Book2.xls").Worksheets(1).Range("B2").text

お礼

できました。ありがとうございます。

回答No.2

KenKen_SP

こんにちは。KenKen_SP です。

こんな感じでしょうか？

Sub Sample()

　　Dim Sh1 As Worksheet
　　Dim Sh2 As Worksheet
　　Dim strFormat as String

　　'フィルターをかけるブック&シート
　　Set Sh1 = Workbooks("Book1.xls").Sheets("Sheet1")
　　 'フィルターの条件のあるブック&シート
　　Set Sh2 = Workbooks("Book2.xls").Sheets("Sheet1")

　　With Sh1
　　　　'オートフィルターを初期化するなら次行のコメント解除
　　　　'If .FilterMode Then .Range("A1").AutoFilter
　　　　'フィルターをかけるセルの書式を取得
　　　　strFormat = .Range("A2").NumberFormat
　　　　'オートフィルター設定
　　　　.Range("A1").CurrentRegion.AutoFilter _
　　　　　　Field:=1, _
　　　　　　Criteria1:=">=" & Format$(Sh2.Range("B1").Value, strFormat), _
　　　　　　Operator:=xlAnd, _
　　　　　　Criteria2:="<=" & Format$(Sh2.Range("B2").Value, strFormat)
　　End With

　　Set Sh1 = Nothing
　　Set Sh2 = Nothing

End Sub

お礼

できました。ありがとうございます。

回答No.1

masa_019

Book2のSheet1のB1に >=2005/8/21
B2に <=2005/9/20 として、

Sub MACRO1()
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, _
Criteria1:=Workbooks("Book2.xls").Sheets("Sheet1") _
.Range("B1").Value, Operator:=xlAnd, _
Criteria2:=Workbooks("Book2.xls").Sheets("Sheet1") _
.Range("B2").Value
Range("C1").Select
End Sub

これで、出来ませんか？

お礼

出来ました。ありがとうございます。