Excelで入力規則ドロップダウンリストに他シートの列名を指定する とか
はじめに
「VBAでこんなことできますシリーズ」の続きで
なんか、csv開いて、特定の言葉でデータを絞ったものを別シートに出して、
最終的にExcelファイルかcsvで出力できるみたいの
を作ろうと思ったら途中で色々あって、それなりに時間も使ってしまったので、ひとまず記事にしてみます。
VBAで入力規則ドロップダウンのリストを設定する
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セルです)
対象列に該当の言葉を含むセルがいくつあるかを動的に表示させてみようかしら(VBAのイベントとか使わずに)
ここが結構難しかったです。
VBAerは何でもVBAで解決したがるから複雑な関数とか実は苦手
というのはわりとあるあるかなと思うんですが、どうでしょう。
まあ、言い訳ですか。。。
- COUNTIFを使えば、部分一致での件数を表示させることができるけど範囲指定を動的にって???
- INDIRECTを使えばセル上に表示されている文字列をアドレスとして関数に入れられる(らしい)
- てことはセル上に「csv!列名:列名」て出せればいいんだろうが!
試行錯誤と謎の逆ギレの末、上のように色々組み合わせるとできることがわかりました。
下のようになっているcsvシートのK列(住所)に検索語である「北海道」を
含むセルがいくつあるかを表示しています。
。。。。。。。。。。。。。。。。
ここまでやんなきゃいかんのかな。
もっといいやり方あるのかもしれませんが、まあできたのでよし。
こんなん出ました
検索語を変更しても変わります(当たり前)