住所録入力フォーム
エクセル住所録
祝日対応カレンダー
カレンダー機能
userform
ユーザーフォーム
データの操作
VLOOKUPをVBAでやる

検索して見つけたセルの入力済み最終右セルを選択や文字を記載

エクセルVBAセルの操作セルの操作

エクセルで一番面倒で時間がかかる作業が文字や値をシートの中から探す作業じゃないですか？

例えば縦軸（行）に47都道府県が並んでいて、横軸に12種類の項目があるとします。

すると47行の中から探したい都道府県を探して、見つけたら12種類の項目に必要な項目のみ入力する作業をしなくては行けなくなったらどうしますか？

単純計算で47都道府県×12種類＝564個のセルの入力に、1か所30秒で作業をしても5時間弱かかってしまいます。

ところがユーザーフォームを使えば、1行につき10秒ちょっとでも10分程で作業を終えることができます。仮に1行につき1分かかったとしても47分です。

スポンサーリンク

FindとActiveCellとOffsetを組み合わせる

検索横追加2
クリックで拡大します

ここでやりたいことは、上の図のように店舗名（47都道府県名）が縦に並んでいて（行）、支払い可能な取扱い決済が横に（列）追加できるようにします。

ユーザーフォームに記載するVBA

検索横追加3
オリジナル入力フォームを作成

ここでは、このままのVBAコードを記載すると複雑になるので、下記のように絞って記載します。

  1. リストボックスから都道府県を選択
  2. テキストボックスに文字入力
  3. コマンドボタンに選択状態の都道府県と同じ行を見つけて右側のセルに入力文字を記載する
ActiveCellの入力済み最終右列に追加記載する

Private Sub CommandButton1_Click()
Dim myRange As Range
Set myRange = Range(“D2:D48”).Find(What:=Range(“A2”).Value, LookAt:=xlWhole)
If Not myRange Is Nothing Then
myRange.Select
If ActiveCell.Offset(, 1) = “” Then
ActiveCell.Offset(, 1) = TextBox1.Value
Else
ActiveCell.End(xlToRight).Offset(, 1) = TextBox1.Value
End If
End If
End Sub

VBA解説
  1. コマンドボタン1がクリックされたら
  2. データ型の変数「myRange」を宣言する
  3. セルD2～D48の範囲でセルA2の値と同じセルを検索し、最初に見つけたセルを変数「myRange」に格納する
  4. もしmyRangeが見つかった場合は見つけたセルを選択状態にする
  5. もし選択状態のセルの右側のセルが空欄だったら
  6. 選択状態の右側のセルにテキストボックス1の入力文字を記載する
  7. そうではなかったら（右隣のセルが空欄でない時）
  8. 選択状態のセルの入力済み最終列の右側のセルにテキストボックス1の入力文字を記載する
  9. Ifステートメント終了
  10. Ifステートメント終了
  11. マクロ記録終了

これで、項目追加したい都道府県をリストから選択して、テキストボックスに項目文字を入力後コマンドボタンを押すと、選択した都道府県と同じ行へ移動して右側セル（E列）が空欄ならばE列に入力し、E列がすでに入力済みならばさらに隣の右側セル（入力済の右終端セル）に記載されます。

リストボックスに記載するVBA

先ほどのユーザーフォームに記載したVBAコードには、リストボックスに関するVBAコードの記載をしていません。

検索するセルA2と同じ値こそがリストボックス1で選択された都道府県なので、リストボックスに記載するリストを設定する事と、リストを選択したらセルA2に記載されるようにVBAコードをかくひつようがあります。

リストボックスにリストを表示させる

まずユーザーフォームが表示されたら自動的にリストボックスに都道府県名が表示されないと選択ができないので、ユーザーフォームイニシャライズにVBAコードを書き込みましょう。

UserForm_Initialize

Private Sub UserForm_Initialize()
ListBox1.RowSource = “D2:D48”
End Sub

VBA解説
  1. 下記の処理をしてからユーザーフォームを表示する
  2. リストボックス1のRowSource設定をD2:D48とする
  3. マクロ終了

ユーザーフォームイニシャライズに関しては下記ページと重複するので、ここでは省略します。

UserForm_Initializeでセル値を取得してから表示する方法
ユーザーフォームを開いた瞬間に複数セルのデータを次々と連続で読み込みこんでラベルやコマンドボタンのCaptionが書き換わっていたら、手間が省けて作業効率アップできますよ。
tripbowl.com
2021.01.21

リストを選ぶと自動的にセルに記載させる

最後に表示されたリストボックス内の都道府県名をクリックして選択状態にするだけで、セルA2に自動的に記載させます。

リストボックスのChangeイベントにより、リストを選択状態にするだけで実行できるので、選択後にクリックしたりEnterキーを押したりするちょっとした手間ですが省略できます。

ListBox1_Change

Private Sub ListBox1_Change()
Range(“A2”).Value = ListBox1.List(ListBox1.ListIndex, 0)
End Sub

VBA解説
  1. リストボックス内の変更時下記を実行する
  2. セルA2の値をリストボックス内のリスト選択状態の項目とする
  3. マクロ記録終了

以上、この3つのVBAコードでデータ検索して見つけた項目と同じ行の右側の入力済み最終列のセルに、追加記載できるようになります。

スポンサーリンク