Excelの入力規則のリストをサーチキーで絞り込むマクロを改良。絞り込んだ結果が1つの場合にはリストを表示しないようにしました。


前回作成した、サーチキーで勘定科目を絞り込むマクロですが、その後少し使ってみたところ、そもそもサーチキーで絞り込んだ結果1つしか勘定科目がないのにわざわざリストから選択するのが面倒に感じたのでマクロの記載内容を少し変更・追加してみました。


具体的にはサーチキーで絞り込んだ結果、勘定科目が一つしかなかったらその勘定科目をリストから選択することなく、直接セルに入力するように変更してみました。

前回作成したマクロの記事はこちらからどうぞ↓

Excelの入力規則のリストをサーチキーで絞り込むマクロ。現金出納帳のリスト項目が多くなりすぎたときに使えるかも。

Excelの入力規則のリストをサーチキーで絞り込むマクロ。絞り込んだ結果が1つの場合にはリストを表示しない。


どんな感じに今回変更したのかイメージを掴んでもらうために動画を用意しました。

この動画を見てもらうとタイトルの意味がわかると思います。



イメージは掴んでもらえたでしょうか?


前回までのマクロであれば例えば「租税公課」を絞り込もうとしてセルに「so」などと入力してctrlキーとenterキーを同時に押して確定するとこんな感じにリストが表示されていました↓



こうなってしまうと絞り込んだ結果が1つしかないのに、わざわざその1つをリストから選択するという面倒くささがでてきてしまうことに気づきました。


ってことで、上の動画にあるように、サーチキーで絞り込んだ結果、勘定科目が1つしかないのであればその勘定科目を直接セルに表示させるように変更してみました。


サーチキーで勘定科目を絞り込んでExcelの入力規則のリストに表示するマクロ(改良版)


今回、変更・追加した後のマクロはこんな感じになります。

追加した部分をハイライト表示しています。具体的には15行目と39行目、それから54~67行目になります。

それ以外の部分については前回作成したものと全く同じになります。


Option Explicit

Sub siborikomi()

'■C列に入力されたサーチキー(アルファベット)で勘定科目を絞り込み
'■入力規則に表示させるマクロ (サジェスト機能)
'■絞り込んだ結果、勘定科目が1つしかない場合にはその科目を表示する


'■変数宣言

Dim listbox As Variant
Dim i As Long
Dim lastrow As Long
Dim j As Long


'■最終行の取得 マスタを作成している場所を参照する

lastrow = Cells(Rows.Count, 6).End(xlUp).Row


'■処理実行部分

    '■アクティブセル(C列のセル)に入力されたアルファベットのサーチキーでF列からInstr関数で該当する勘定科目を検索する
    '■もし該当する勘定科目があれば変数「listbox」に格納する
    '■マスタに登録された勘定科目の数だけ繰り返す。(lastrowで登録された勘定科目の数を取得する)
    
    
For i = 2 To lastrow


    If Range("f" & i).Value <> "" Then
    
        If InStr(Range("g" & i).Value, ActiveCell.Value) = 1 Then
           
              listbox = listbox + Range("f" & i).Value + ","
              
              j = j + 1
    
        End If
    
    End If
    

Next i


    '■変数「listbox」に何らかの勘定科目が格納されている場合に、入力規則をC列に設定する。

If Len(listbox) > 0 Then

    
      '■検索にヒットした勘定科目が1つしかない場合の処理(その科目をアクティブセルに入力する)
    
    
    If j = 1 Then
    
        Range("c:c").Validation.Delete  '■入力規則をクリアする ※必須。これがないとエラーが発生する。
        
        Range("c:c").Validation.Add Type:=xlValidateInputOnly '■すべての値を入力可
        
        Range("c:c").Validation.IMEMode = xlIMEModeOff '■サーチキーを使いやすくするために日本語入力をオフにする
        
        '■変数「listbox」に格納された文字列のうち、「,」を除いてアクティブセルに表示する
        
        ActiveCell.Value = Left(listbox, Len(listbox) - 1)
        
    
    Else
    
    
         '■検索にヒットした勘定科目が2以上の場合の処理
    
    
        Range("c:c").Validation.Delete  '■入力規則をクリアする ※必須。これがないとエラーが発生する。
        
        Range("c:c").Validation.Add Type:=xlValidateList, Formula1:=listbox  '■絞り込んだ勘定科目で入力規則をセットする
        
        Range("c:c").Validation.IMEMode = xlIMEModeOff '■サーチキーを使いやすくするために日本語入力をオフにする
        
        Range("c:c").Validation.ShowError = False  '■入力規則にないデータ(勘定科目)を入力してもエラーにならないようにする
        

    End If

End If

End Sub



追加したマクロの内容の説明(考え方とマクロの記述について)


追加したマクロの内容というか考え方を自分なりに説明しておきます。

  • 変数「listbox」に格納された勘定科目の数を数える変数「j」を新たに作る。(15行目)

  • サーチキーで検索した結果、勘定科目がヒットしたら変数「listbox」に勘定科目を追加すると同時に変数「j」に1を足していく。(39行目)

  • サーチキーによるすべての検索が終わったタイミングで変数「j」の中身を確認して、「1」が格納されていたら、サーチキーで検索した結果、勘定科目が1つしかなかったことになる。


    →わざわざExcelの入力規則のリストを表示させることはせずに、変数「listbox」に格納されている1つ「だけ」の勘定科目をセルに直接入力させる。(54~67行目)


こんな感じで考えています。

まとめ


前回作成したサーチキーで勘定科目を絞り込むマクロについて、絞り込んだ結果、勘定科目が1つしかない場合にはその勘定科目をリストに表示することなく、直接セルに入力するようにマクロに変更を加えてみました。


作ったマクロを実際に使用してみて、足りないところを自分の好みに追加していくことができる点はマクロの魅力の一つですね。(難しいことはできませんが。。。)





【本日の近況報告】

10代のころから花粉症持ちですが、とうとう今年もシーズンインした模様。

鼻が詰まりだして、目がかゆい。そして鼻水とくしゃみの総攻撃が急にきております。

毎年、市販の薬ではなく、内科で処方された薬を使用して乗り切っているので、今年ももらいにいかないと。

【本日の1曲】

Bring it home to me /sam cooke

この曲は自分がサムクックを知ることになった1曲。

曲を知ったキッカケは、ウルフルズのトータス松本さんが自身のソロアルバム「トラベラー」の中でこの曲をカバーしていたのを聴いたのが始まりです。


最初に聴いて「何ていい曲だ。。」と。


そこからオリジナルがサムクックということを知り、オリジナルの曲も聴いていって感じです。


歌詞の内容は恋人と別れて未練たらたらの男性の歌?みたいな感じでしょうか。


よくわかりませんが。


ただ、この曲に関してはオリジナルもカバーもどっちもかっこいい。サムクックはこの曲以外にもたくさんいい曲がありますね。「twistin’ the night away」とか「you send me」 とか。