[[20150427150145]]　『VBA　インプットボックス』（未知の世界）　ページの最後に飛ぶ

『VBA　インプットボックス』（未知の世界）

VBなかなか完成できず、誰か教えて下さい。

早速ですが、説明に入ります。

下記のような表があります。

簡易表
　　　Ａ　　　Ｂ　　　　Ｃ　　　　　Ｄ　　　　Ｅ　　　　Ｆ　　　

１　　部　　　課　　　　差額　　　値１　　値２　　値３
２　　123　　　12355　　　0　　　　0　　　 10　　　 10
３　　124　　　12466
４　　123　　　12355　　　3　　　　20　　　 0
５　　127　　　12755
６　　128　　　12822
７　　123　　　12355　　　7　　　　20　　　　　　　　0
８　　126　　　12688
９　　127　　　12755
１０　128　　　12822
１１　129　　　12900
１２　123　　　12355　　　6　　　　　　　　　0　　　　0
１３　124　　　12466
１４　123　　　12355　　　8　　　　20　　　 0
１５　123　　　12355　　　2　　　　20　　　　　　　　0
１６　123　　　12355　　　0　　　　　　　　　10　　　10
１７　123　　　12355　　　2　　　　20　　　 10　　　 0
１８　123　　　12355　　　2　　　　20　　　　　　　　10
１９　123　　　12366　　　2　　　　20　　　　　　　　0

毎月、下記記載のＡ列～Ｅ列の内容でフィルタを使用して件数を調べています。
Ａ列の選択（部のコード入力）
Ｂ列の選択（課のコード入力）
Ｃ列の選択（0以上5以下)←
Ｄ列の選択（0と空白のチェックを外す)
Ｅ列とＦ列の選択（0と空白のみチェック入れる)

　　　↓

Ｄ列の件数チェック
（もし、部のコード「123」課のコード「12355」で検索すると「2件」になる）

という内容です。これをVBAで作ることになりました。

今、私ができている内容は、下記内容です。

Sub インプット2つ()

    Dim namae As String
    Dim namae2 As String

    namae = InputBox("部を入力して下さい。" & vbCrLf & _
                    "ただし、半角入力でお願いします。")

    namae2 = InputBox("課を入力して下さい。 " & vbCrLf & _
                    "ただし、半角入力でお願いします。")

     Range("H1") = namae & vbCrLf & namae2

    Worksheets("Sheet3").Range("A1").AutoFilter Field:=1, Criteria1:=namae
    Worksheets("Sheet3").Range("A1").AutoFilter Field:=2, Criteria1:=namae2
    Worksheets("Sheet3").Range("A1").AutoFilter Field:=3, _
                                                    Criteria1:=">=0", _
                                                    Operator:=xlAnd, _
                                                    Criteria2:="<=5"
    Worksheets("Sheet3").Range("A1").AutoFilter Field:=4, _
                                                    Criteria1:="<>0", _
                                                    Operator:=xlAnd, _
                                                    Criteria2:="<>"
    Worksheets("Sheet3").Range("A1").AutoFilter Field:=5, _
                                                    Criteria1:="=0", _
                                                    Operator:=xlOr, _
                                                    Criteria2:="="
    Worksheets("Sheet3").Range("A1").AutoFilter Field:=6, _
                                                    Criteria1:="=0", _
                                                    Operator:=xlOr, _
                                                    Criteria2:="="

    If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
            MsgBox "0件です"
        Else
            MsgBox Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp)) _
                .SpecialCells(xlCellTypeVisible).Count & "件です"
        End If

    kensuu = WorksheetFunction.Subtotal(3, Worksheets("Sheet3").Range("D:D"))
    Worksheets("Sheet3").Range("I1").Value = kensuu - 1
    Worksheets("Sheet3").Select

End Sub

やりたいこと
１．このコードだと、部と課のコードを入れて絞り込み検索という事しかできないので
　　部のコード入れて、課のコードの入力も求めてくるので、何もいれずOKしたら、
　　部全体だけの件数が返ってくるというのをしたいです。

２．キャンセルの時と打ち間違えた時も「0」が返ってきてしまうので
　　インプットボックスで部のコードをＡ列の中にないコードを打ったら
　　「そのコード名はありません、打ち間違えている可能性があります。」的な事を伝えて
　　再入力を促したいです。
　　あと、キャンセル押すと「部コードを入力して下さい」と再入力を促したいです。
　　※部のコードを入れずに検索はないので…。

そういう条件を上記のVBのコードの中入れたいのですが、全然分かりません。
…正直できるのかもわかりません。
いろいろ調べたのですが、よく分からなかったです。

上記コードの中の
＞Range("H1") = namae & vbCrLf & namae2
で、何のコードで検索かけたかは入れています。
……自分でいろいろ試していたら、今、何のコードだったっけ？ってなったので(^_^;)
なので、そこは、あまり気にしないで下さい。

初心者で調べながら見よう見まねで作ったので、おかしい所もあるかもしれないので
その時は、そちらも教えて下さると助かります。

以上、よろしくお願いします。

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

 入力を下記のようにして、
    Do While True
        namae = StrConv(InputBox("部を入力して下さい。"), vbNarrow)
        If Application.CountIf(Worksheets("Sheet3").Range("A:A"), namae) > 0 Then Exit Do
        MsgBox "指定された部がありません。[" & namae & "]"
    Loop

    Do While True
        namae2 = StrConv(InputBox("課を入力して下さい。"), vbNarrow)
        If namae2 = "" Or Application.CountIf(Worksheets("Sheet3").Range("A:A"), namae2) > 0 Then Exit Do
        MsgBox "指定された課がありません。[" & namae2 & "]"
    Loop

 name2 の条件設定を
    If namae2 <> "" Then
        Worksheets("Sheet3").Range("A1").AutoFilter Field:=2, Criteria1:=namae2
    End If
 のように変えてできないでしょうか。

（Mook） 2015/04/27(月) 16:35

 ２．の部分のみ、ご参考まで。
 　「キャンセルの時と打ち間違えた時も「0」が返ってきてしまう」というのがちょっと？ですが
 　INPUTBOX関数では、キャンセルしたのか、そうでないのかが、入力として期待する値によっては紛らわしい場合がありますね。

 　一方、INPUTBOXメソッドと呼ばれるものは、キャンセルの場合、Falseが返ります。
 　戻り値の受けを、アップされたように　As String で規定した変数にしますと "False" という文字列になります。
 　かつ、このメソッドは、入力データの形式を指定できます。「数字」と指定すればアルファベットなどが入力された場合、
 　メソッドの中でエラーメッセージが出て、正しい入力を促します。
 　また、全角で　１２３４　といれても、自動的に　半角数字の　"1234" に変換して戻してくれます。

 以下、参考例です。

 Sub Sample()
    Dim ans As String
    ans = Application.InputBox("部を入力して下さい。" & vbCrLf & _
                    "ただし、半角入力でお願いします。", Type:=1)
    If ans = "False" Then Exit Sub 'キャンセル

    MsgBox "処理を続けましょう"

 End Sub

（β） 2015/04/27(月) 17:00

 コードで、シート修飾があったり、状況依存のシート修飾なしが混在しているのが気になりますね。
 結果はオーライでしょうけど、シート修飾で統一しましょう。

 Mookさんの部のチェック、課のチェックを借用すると、以下のようなコードになりますね。

 Sub インプット2つ()

    Dim namae As Variant
    Dim namae2 As Variant
    Dim sh As Worksheet

    Set sh = Worksheets("Sheet3")
    sh.AutoFilterMode = False
    sh.Range("A1").AutoFilter

    Do
        namae = Application.InputBox("部を入力して下さい。" & vbCrLf & _
                        "ただし、半角入力でお願いします。", Type:=1)

        If namae = False Then Exit Sub  'キャンセルボタン

        If Application.CountIf(sh.AutoFilter.Range.Columns(1), namae) > 0 Then Exit Do
        MsgBox "指定された部がありません。[" & namae & "]"

    Loop

Do

        namae2 = Application.InputBox("課を入力して下さい。 " & vbCrLf & _
                        "ただし、半角入力でお願いします。" & vbLf & _
                        "もし、すべての課が対象ならキャンセルを押してください", Type:=1)

        If namae2 = False Then Exit Do  'キャンセルボタン

        If Application.CountIf(sh.AutoFilter.Range.Columns(2), namae2) > 0 Then Exit Do
        MsgBox "指定された課がありません。[" & namae2 & "]"

    Loop

    sh.Range("H1") = namae & vbCrLf & IIf(namae2 = False, "未選択", namae2)

    With sh.AutoFilter.Range
        .AutoFilter Field:=1, Criteria1:=namae
        If namae2 <> False Then .AutoFilter Field:=2, Criteria1:=namae2
        .AutoFilter Field:=3, _
                Criteria1:=">=0", _
                Operator:=xlAnd, _
                Criteria2:="<=5"
        .AutoFilter Field:=4, _
                Criteria1:="<>0", _
                Operator:=xlAnd, _
                Criteria2:="<>"
        .AutoFilter Field:=5, _
                Criteria1:="=0", _
                Operator:=xlOr, _
                Criteria2:="="
        .AutoFilter Field:=6, _
                Criteria1:="=0", _
                Operator:=xlOr, _
                Criteria2:="="
    End With

    MsgBox sh.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 & "件です"

    sh.Range("I1").Value = WorksheetFunction.Subtotal(3, sh.Range("D:D")) - 1
    sh.Select

 End Sub

（β） 2015/04/27(月) 17:48

 ↑ 厳密にいえば

 sh.Range("I1").Value = WorksheetFunction.Subtotal(3, sh.Range("D:D")) - 1

 これは、オートフィルターリストのずっと離れた下のほうのD列に値が（ゴミも含めて）あった場合、カウントされます。

 sh.Range("I1").Value = WorksheetFunction.Subtotal(3, sh.AutoFilter.Range.Columns("D")) - 1

 このようにされるほうが安心ですね。

（β） 2015/04/27(月) 19:42

Mook様・β様

回答してくださり、ありがとうございます。
お2人の書いてくださった内容の知らない言葉を調べていたりしていたらお返事遅くなりました。

StrConv　初めて知りました。どうやら変換できるものらしいですね。 …勉強になります。

あの…自分が調べただけじゃなかなかコードの意味を理解できない所ありまして、
理解力がなく申し訳ないのですが、教えて頂きたいです(>_<)
最終的にコードは、β様のMook様の借用したコードでうまくいきました。が・・・

下記の部分…
＞あ　If namae = False Then Exit Sub 'キャンセルボタン

＞い　If Application.CountIf(sh.AutoFilter.Range.Columns(1), namae) > 0 Then Exit Do

    　　MsgBox "指定された部がありません。[" & namae & "]"

＞う　IIf(namae2 = False, "未選択", namae2)
　　　…これは、namae2がfalse（キャンセルされた時）は未選択と記入され、falseの時は、namae2を記入という意味でしょうか？

＞え　sh.Range("I1").Value = WorksheetFunction.Subtotal(3, sh.AutoFilter.Range.Columns("D")) - 1

キャンセルボタンはコードに書くと、緑色になるのですね。緑色になるのは何か意味があるのですか？
後、「あ行」と「い行」の部分と、「う行」の部分がいまいちハッキリ自分じゃ理解できなくて…
「え行」の部分で最後のColumns("D")と（"D:D"）の違いは、何ですか？
　("D:D")はＤ列選択と思ってるのですが、("D")は・・・？
　
いろいろ聞いてしまい、申し訳ありませんが、教えて下さい！！

よろしくお願いします。m(__)m

※追記です。
＞シート修飾があったり、状況依存のシート修飾なしが混在していると書いてあるのですが、私、ＶＢのルール？とかそういうのも言葉も知らないのでこの言葉は、どこら辺を指しているのかが、わからないのですが、教えていただけないでしょうか？
できる限りは調べているのですが、なかなか理解力が及ばなく…すみません。

（未知の世界） 2015/04/28(火) 13:33

 >　If namae = False Then Exit Sub 'キャンセルボタン

 　　Application.InputBoxメソッドでは、入力データの制限が可能です。Type:= で指定。
 　　今回は、1 にしていますが、これは「数字」と限定しています。数字以外が入力されれば
 　　ダイアログ内でエラーになって、正しい数字が入力されるか、あるいはキャンセルボタンがおされるまでは
 　　ダイアログから抜けてきません。一方でダイアログでキャンセルボタンがおされると「特殊」な「False」が返ってきます。
 　　False は　データ型としては　Bool型(Boolean)と呼ばれるものです。
 　　わかりにくいかもしれませんが、正常な入力とキャンセルボタンを区別するため、戻り値を受ける変数の namae や namae2　を
 　　Variant型（どんな型でも受け入れる）にしていて、そこに　False が返ってきたら、それを判定できるようにしています。

 >　If Application.CountIf(sh.AutoFilter.Range.Columns(1), namae) > 0 Then Exit Do

 　　すでにVBAでシート関数が使えるというのはご存じですので、疑問点がどこなのかな？　とも思いますが、
 　　まず、通常は WorksheetFunction.シート関数　ですね。Application.シート関数　も、ほとんど同じものです。
 　　たまたま、Mookさんのコメントで、こちらを使っておられたので、そのまま　Application.シート関数にしました。
 　　（厳密にいうと、違いはあって、その違いを利用したりする場面もありますが、今回に限っては全く同じです）

 　　で、COUNTIF の中の　対象領域の　sh.AutoFilter.Range.Columns(1) これがわかりにくかったですかね？
 　　sh.AutoFilter.Range.Columns(1) は　オートフィルターリストの領域です。その最初の列ということですから実際には
 　　Ａ列なんですが、領域から離れてずっと下のほうのＡ列に値があると、それも対象になってしまいますので
 　　「オートフィルターリスト内の１列目」というように範囲を限定しています。

 >    　　MsgBox "指定された部がありません。[" & namae & "]"

 　　たぶん、これはＯＫですよね？　もし、わかりにくければ、ためしに、存在しない部コードをいれてみてください。
 　　その時にでるメッセージの文字列を見れば、このコードの意味が分かると思います。

 >　IIf(namae2 = False, "未選択", namae2) 
　　　…これは、namae2がfalse（キャンセルされた時）は未選択と記入され、falseの時は、namae2を記入という意味でしょうか？

 　　IIF は　IIF(条件,Trueの場合の値,Falseの場合の値) です。
 　　で、namae2 は　キャンセル（課の選択をしない）場合は False になっているということは、上でも説明した通りです。
 　　そうしますと、条件が　namae2 = False ですから、False の時に　True になります。イコールですから。
 　　ちょっとわかりにくいですかね？　結果、namae2 が False の時に未選択という値、そうではないときには　入力された課コードとなります。

 >　sh.Range("I1").Value = WorksheetFunction.Subtotal(3, sh.AutoFilter.Range.Columns("D")) - 1 
 >  Columns("D")と（"D:D"）の違いは、何ですか？  　("D:D")はＤ列選択と思ってるのですが、("D")は・・・？

 　　Range("D:D") も Columns("D") も、全く同じもの、D列です。いずれの書き方でもいいのですが、これはβの好みということで。

 >キャンセルボタンはコードに書くと、緑色になるのですね。緑色になるのは何か意味があるのですか？

 　　キャンセルボタンが緑になるのではなく　ＶＢＥ画面でＶＢＡコードを書くときに、コード以外のコメントを記述できますよね。
 　　'コメント　というように　先頭に ' をつけますよね。このコメントが　ＶＢＥ画面上で緑色になるんです。

 >シート修飾があったり、状況依存のシート修飾なしが・・・・

 これは、奥の深い、かつ重要なことです。コメントしだすときりがなく膨大になりますので【キモ】だけを。

 ＶＢＡコードの中でセルを指定する際、たとえば　Range("A1") と書きますね。でも、このRange("A1") って、どこの A1 なんでしょうか？
 本来、すべてのセルは　このブック.このシート.このセル　という「正式な名札」を持っています。
 このブックやこのシートが省略されると、ＶＢＡは以下のように解釈します。

 シートモジュールでは、つねに、このシートモジュールが属するブックの、コードが書かれたシートとみなします。
 ところが標準モジュール等、シートモジュール以外では、「今、たまたまこのエクセル区画で一番前にあるブックの、表示されているシート」
 というように解釈します。自分が、Sheet1 を相手にしていると思っていても、もし、操作者が実行タイミングで別のシートを選んでいれば
 そのシートを参照してしまいますし、また、操作者が同じエクセル区画で全く別のブックを開いて、それが最前面の状態にあれば、そこを参照。
 操作者だけではなく、いずれ(未知の世界)さんがスキルアップすれば開発中にステップ実行ということをやっていくと思いますけど、
 マクロを動かしながら、いろんなブックやシートの状況を確かめることができます。その際に、本来Sheet1 が最前面だったのに、Sheet2をチェックして
 そのままの状態で実行を継続しますと、Sheet2が参照されてしまい、処理結果がめちゃくちゃになります。

 うんちくをたれても、なかなか理解しづらいでしょうね。
 新規ブックのSheet1のA1に "AAA" Sheet2 の A1に "BBB" をいれてください。
 で、以下のマクロを書いて、Sheet1 を選んで、マクロの実行をしてみてください。次に Sheet2 を選んでマクロの実行をしてみてください。
 さらに、ここで、新規ブックを作成して、最初のブックに作成したマクロの実行をしてください。
 ↑でもうしあげたうんちくのいったんを理解いただければ幸甚です。

 Sub Test()
    MsgBox Range("A1").Value
 End Sub

（β） 2015/04/28(火) 16:19

すごく丁寧な説明ありがとうございます！

何で、variant型にしたのかな？って実は考えていました。
他にもいろいろと、でも、あんまりいっぱい聞くのも…と思っていたのですが、他にもいろいろと奥深く教えて下さり助かりました。

シート装飾…の件も確かに、他のシートにしていて、ＶＢのコード上でF5を押して実行すると実行されるけど、結果がおかしかったので、自分の中では、コードの記述でシート名をきちんと選んでいたつもりでしたので、何で変な事になるのかな？って思ってました。

最後のしてみたいのですが、あがってしまったので会社に行ったらしてみます！

またお世話になることもあるかもしれませんが、その時はよろしくお願いします。

Mook様とβ様、ありがとうございました。
（未知の世界） 2015/04/28(火) 17:42

コメント返信：

[ 一覧(最新更新順) ]

[[20150427150145]] 『VBA インプットボックス』（未知の世界） ページの最後に飛ぶ

[[20150427150145]]　『VBA　インプットボックス』（未知の世界）　ページの最後に飛ぶ