may the VBA be with you

Excel VBAとか業務自動化とか

Excelで入力規則ドロップダウンリストに他シートの列名を指定する とか

はじめに

VBAでこんなことできますシリーズ」の続きで

なんか、csv開いて、特定の言葉でデータを絞ったものを別シートに出して、
最終的にExcelファイルかcsvで出力できるみたいの

を作ろうと思ったら途中で色々あって、それなりに時間も使ってしまったので、ひとまず記事にしてみます。

VBAで入力規則ドロップダウンのリストを設定する

csvシートに、テストデータ・ジェネレータさんで生成された住所データがあります。(実在しない人たちのデータです)

f:id:vba-belle-equipe:20190728134624p:plain
csvシート

この1行目の「NO、 名前。。。」という部分を
他のシートのセルの入力規則として設定するためのコードはこちらです。

Sub 検索列入力リスト設定()
    Dim maxCol As Long
    Dim rng As Range
    With ThisWorkbook.Sheets("csv")
        maxCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        If maxCol < 1 Then
            Exit Sub
        Else
            Set rng = .Range(.Cells(1, 1), .Cells(1, maxCol))
        End If
    End With
    
    With ThisWorkbook.Sheets("テスト").Range("B2").Validation
        .Delete
        .Add Type:=xlValidateList, _
             Operator:=xlEqual, _
             Formula1:="=csv!" & rng.Address
    End With
End Sub

無事、下のように出てきました。
(「テスト」というシートのB2セルです)

f:id:vba-belle-equipe:20190728134235p:plain
ドロップダウン

対象列に該当の言葉を含むセルがいくつあるかを動的に表示させてみようかしら(VBAのイベントとか使わずに)

ここが結構難しかったです。

VBAerは何でもVBAで解決したがるから複雑な関数とか実は苦手

というのはわりとあるあるかなと思うんですが、どうでしょう。

まあ、言い訳ですか。。。

f:id:vba-belle-equipe:20190728134910p:plain
数式とか

  • COUNTIFを使えば、部分一致での件数を表示させることができるけど範囲指定を動的にって???
  • INDIRECTを使えばセル上に表示されている文字列をアドレスとして関数に入れられる(らしい)
  • てことはセル上に「csv!列名:列名」て出せればいいんだろうが!

試行錯誤と謎の逆ギレの末、上のように色々組み合わせるとできることがわかりました。

下のようになっているcsvシートのK列(住所)に検索語である「北海道」を
含むセルがいくつあるかを表示しています。

f:id:vba-belle-equipe:20190728134624p:plain
csvシート再掲

。。。。。。。。。。。。。。。。


ここまでやんなきゃいかんのかな。

もっといいやり方あるのかもしれませんが、まあできたのでよし。

こんなん出ました

f:id:vba-belle-equipe:20190729222251g:plain
検索列を変更したところ

検索語を変更しても変わります(当たり前)

f:id:vba-belle-equipe:20190729225322g:plain
検索語を変更したところ