[[20150706134038]]　『VBA 複数条件で検索し、合計を表示する』（みや）　ページの最後に飛ぶ

『VBA 複数条件で検索し、合計を表示する』（みや）

シート構成は
シート１・・・・検索シート（１行目空白・２行目検索項目・３行目検索キー・４～５行目空白・６行目項目・７行目以降は３行目の検索キーで検索されたデータを表示）

シート２・・・・データシート

検索キーは３行目のA3～M3、P3～S3に入力する（A3～M3、P3～S3で検索キーが空白の場合は検索キーとしてみない）

検索キーが１つの場合は下記のマクロで動作するのですが、検索キーを複数作りたいのですが、どうしたら良いのでしょうか？

また、抽出された検索シートの検索結果のデータの7列の7行目以降を合計し、N3に合計表示する
あわせて、O列の7行目以降を合計し、O3に合計表示をする

例）検索シートA3の条件で検索した場合

Sub フィルター()

    Dim WS1, WS2, WS3 As Worksheet

    Set WS1 = Sheets("検索シート")
    Set WS2 = Sheets("データシート")
    WS2.Select
    Range("A1").AutoFilter FIELD:=1, Criteria1:=WS2.Cells(3, 1)
    Range(Cells(2, 1), Cells(c, 19)).Select
    Selection.Copy
    WS1.Select
    Cells(7, 1).Select
    ActiveSheet.Paste
    Cells.Select

End Sub

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 データシートの構成も書いてくれるとわかりやすいと思います。
 あと
 ＞Range(Cells(2, 1), Cells(c, 19)).Select
 このcは変数でなにか指定してあるのでしょうか？
（KUKI） 2015/07/06(月) 14:47

 質問。

 １．検索シートですが、２行目が項目とありますので、A2～M2、P2～S2 （なぜ間が開いているのかわかりませんが）に、都合、17項目のタイトルがある？
 ２．で、A3～M3、P3～S3に、それぞれの項目に対する検索文字列を、これも最大 17項目指定？
 ３．６行目はデータシートと同じ項目名が同じ並びで入っている？
 ４．７行目以降に検索された結果の　データシートの行を転記？

 これであってますか？　N～O　が空いている理由は？

 ５．【検索結果のデータの7列の7行目以降を合計し、N3に合計表示する 】

   7列　とは？

 あと、P～S に対する回答にもよりますが、レイアウトを処理しやすいようにかえるのはＯＫですか？
 それとも、このまま変えちゃいけないのでしょうか？

 ついでに、変数記述、誤解をして、こういう書き方をする人が少なからずいますけど

 Dim WS1, WS2, WS3 As Worksheet

 これは間違いです。（エラーにはなりませんけど）
 たぶん、WS1もWS2もWS3も As WorkSheet と認識しておられるかと思いますが、As は、この場合 WS3にのみかかります。
 WS1,WS2 は As がない。したがって、Variant型として扱われます。

 Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet

 このように記述することが必要です。

（β） 2015/07/06(月) 15:03

（β）さん
質問に回答致します。
１、N2,O2は手配数量と在庫数量が入っているので検索キーとして使わないため除外しました。そのためN2,O2を除いた１７項目のタイトルがあります。
２、記載頂いた様に最大17項目で検索したいです。
３、検索シートの６行目は２行目と同じ項目が同じ並びで入っています。
４、7行目以降に２、で検索されたデータ（N列、O列）は検索項目としてありませんが、データの転記は行全体（A～Sまで)を転記したいです。
５、記載間違いです。検索シートに転記されたデータでN3のセルにN7以降のN列の合計を表記。同様にO3セルにO7以降のO列の合計を表記です。

（みや）2015/07/06（月） 15:39

 19:12 合計セット追加。それに関連したコメントも一部訂正。

 本件は、フィルターオプション処理（フィルター詳細設定）にうってつけのレイアウトです。
 ただ、それを活用するために、ちょっと縛りを。

 検索シートの A2～S2まで、N2,O2も含めて、データシートのタイトルと同じものをいれてください。（並び順は任意です）
 また、A6～S6 にも（並び順は任意ですが）データシートのタイトルと同じものをいれてください。
 データシートのタイトル行は１行目、データが２行目からとしています。
 検索語句ですが、たとえば abcd といれると、abcdなんとか　がすべて前方一致で抽出されます。
 （フィルターオプションの仕様）
 完全一致が必要なら、面倒ですが '=abcd と入力してください。

 なお、N3とO3の合計数値セットですが、3行目は処理で参照していますので N4,O4 にセットしました。

 検索シート、データシートのシート名は実際のものに直してください。

 Sub Test()
    Dim shK As Worksheet
    Dim shD As Worksheet

    Set shK = Sheets("Sheet1")  '検索シート
    Set shD = Sheets("Sheet2")  'データシート

    shD.Range("A1").CurrentRegion.Columns("A:S").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=shK.Range("A2:S3"), CopyToRange:=shK.Range("A6:S6"), Unique:=False

    shK.Range("N4").Value = WorksheetFunction.Sum(shK.Range("N7", shK.Range("N" & Rows.Count).End(xlUp)))
    shK.Range("O4").Value = WorksheetFunction.Sum(shK.Range("O7", shK.Range("O" & Rows.Count).End(xlUp)))

    shK.Select

 End Sub

（β） 2015/07/06(月) 19:02

 ↑ 見直してみると、AdvancedFilterメソッドの前に、N3とO3 の値をクリアしておけば、合計結果を N3とO3にセットできますね。

（β） 2015/07/06(月) 22:40

 ↑でコメントした、結果を N3,O3に記入するコードです。

 Sub Test2()
    Dim shK As Worksheet
    Dim shD As Worksheet

    Set shK = Sheets("Sheet1")  '検索シート
    Set shD = Sheets("Sheet2")  'データシート

    shK.Range("N3:O3").ClearContents
    shD.Range("A1").CurrentRegion.Columns("A:S").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=shK.Range("A2:S3"), CopyToRange:=shK.Range("A6:S6"), Unique:=False

    shK.Range("N3").Value = WorksheetFunction.Sum(shK.Range("N7", shK.Range("N" & Rows.Count).End(xlUp)))
    shK.Range("O4").Value = WorksheetFunction.Sum(shK.Range("O7", shK.Range("O" & Rows.Count).End(xlUp)))

    shK.Select

 End Sub

（β） 2015/07/07(火) 06:11

（β）さん

ありがとうございます。
思った様に動作しました。
ちなみに、曖昧検索をする場合は*で挟んで検索すると出来るのですが、ＶＢＡで曖昧検索をさせる場合にどこかに"*"を入力すれば出来ますか？

（みや）　2015/07/07（火） 07:28

 フィルターオプションの場合、検索語句は今までコメントしたように、条件欄に値を記入して
 マクロでは、その条件欄を指定して実行します。

 フィルターオプションの条件設定は、非常に多種多様で、条件として語句だけではなく数式も指定できますし
 また、条件の項目名を空白にして、リストの各行の複数列に対する条件も指定できます。

 たとえば、=AND(A2="aaa",B2>1000) とか。

 ちょっと脱線しました。

 質問の件、上のほうでコメントしましたように、「完全一致」の場合は =なんとか　という指定が必要で面倒ですが
 部分一致指定は非常に楽です。

 ・条件欄に ABC といれておくと ABC に加えて、ABC○○○　が抽出されます。
 ・条件欄に *ABC といれておくと　□□ABC○○○　が抽出されます。もちろん　□□ABC や ABC○○○　も抽出されます。(*ABC* でも、同じ結果になります)

（β） 2015/07/07(火) 08:20

（β）さん

ありがとうございました。
参考になりました。

（みや）　2015/07/07（火） 08:25

コメント返信：

[ 一覧(最新更新順) ]

[[20150706134038]] 『VBA 複数条件で検索し、合計を表示する』（みや） ページの最後に飛ぶ

[[20150706134038]]　『VBA 複数条件で検索し、合計を表示する』（みや）　ページの最後に飛ぶ