豆父ちゃん
こんな要望にお応えします。
この記事では、エクセルに備わっている「検索・置換」機能と同じような処理を行うプログラムをVBAを使って作成する方法をご紹介しています。自分でプログラムを作成することで実務にあったいろんな条件を追加で設定することができます。
- 検索してヒットしたテキストの色を変更する
- 検索してヒットしたテキストを「」でくくる
- 検索してヒットしたテキストを別のテキストに変換し色を変える
上記のような処置を自由自在に追加することができます。
VBAを使った「検索・置換」プログラムはエクセルで大量のデータを集計、編集するような業務の効率をアップさせることができます。エクセルで大量のデータ分析や集計作業が多いという方は是非参考にしてみてください。
目次
VBAを使った検索・置換プログラムとは
※上記の動画ではユーザーフォームが映っていませんが、ユーザーフォームに検索値、検索範囲を入力するとプログラムが実行される。実際のエクセルでは下記のようなユーザーフォームが表示されています。
動画内のユーザーフォームに入力した値は以下のようになります。
| 検索値 | 検索範囲 |
| イチゴ | 1列目19行まで |
| イチゴ | 6列目19行まで |
| ブドウ | 1列目19行まで |
| ブドウ | 6列目19行まで |
指定した検索範囲で検索値を検索し、一致したとき色を操作するプログラムです。
豆父ちゃん
色を操作するVBAについては下記の記事で詳しく紹介しています。
| セルの色の塗りつぶしやフォントの色の変種を自動化したい！こんな処理はVBAを使えば実現できます。 例えば、大量にあるデータから数値が100以下の場合背景色を黄色にする、期限が過ぎた項目のフォントの色を赤色にするといった処理もVBAを使えば一瞬で処理できるようになります。 色を変えるVBAをマスターすれば、面倒な手作業から解放されます。「セルの色を1つずつ変えるのに時間がかかる」「条件に応じて自動で色分けしたい」「プロジェクト管理を視覚的にしたい」といった悩みを持つ方必見です。 本記事では... 【VBA】色を変える方法～サンプルコードや図を使ってVBA初心者でもわかりやすく解説～ - mamemametochan.com |
「検索・置換」VBAと色を操作するVBAを組み合わせています。大量のデータの統計をとる、集計を行う際にとても便利なプログラムなので是非参考にしてみてください。
検索範囲を決めるユーザーフォームの作り方
今回の「検索・置換」VBAはユーザーフォームでテキストボックスを作り、入力された数値で検索範囲を決め、列毎に検索するというプログラムにしました。
ほかにもプログラムの作り方によっていろんな範囲検索が可能
・Sheet全体を検索
・行毎に検索
・セルからセルまでの範囲を検索
豆父ちゃん
検索プログラム(標準モジュール)
| 1 | Sub テスト() |
| 2 | Dim t As String |
| 3 | Dim j As Long |
| 4 | Dim k As Long |
| 5 | t = UserForm1.TextBox1.Value |
| 6 | j = UserForm1.TextBox2.Value |
| 7 | k = UserForm1.TextBox3.Value |
| 8 | For i = 2 To j |
| 9 | If (Cells(i, k).Text = t) And (t = “イチゴ”) Then |
| 10 | Range(Cells(i, k + 1), Cells(i, k + 1)).Interior.Color = RGB(255, 0, 0) |
| 11 | 以下省略 |
| 12 | End Sub |
上記プログラム解説
| 1 | |
| 2 | 検索値の変数宣言 |
| 3 | 検索範囲(行）の変数宣言 |
| 4 | 検索範囲(列）の変数宣言 |
| 5 | 変数tにユーザーフォームで入力した検索値を入れる |
| 6 | 変数jにユーザーフォームで入力した検索範囲(行)を入れる |
| 7 | 変数kにユーザーフォームで入力した検索範囲(列)を入れる |
| 8 | 2行目から検索範囲(行)まで繰り返す |
| 9 | もし検索範囲に検索値があり、検索値がイチゴの場合 |
| 10 | 該当した検索範囲の1つ右列を塗りつぶす |
| 11 | 以下省略 |
| 12 | End Sub |
検索プログラム(ユーザーフォーム)
| 1 | Private Sub CommandButton1_Click() |
| 2 | Call テスト |
| 3 | End Sub |
| 4 | |
| 5 | Private Sub TextBox1_Change() |
| 6 | End Sub |
| 7 | |
| 8 | Private Sub TextBox2_Change() |
| 9 | End Sub |
| 10 | |
| 11 | Private Sub TextBox3_Change() |
| 12 | End Sub |
| 13 | |
| 14 | Private Sub UserForm_Click() |
| 15 | End Sub |
| 16 | |
| 17 | Private Sub UserForm_Layout() |
| 18 | UserForm1.Left = 750 |
| 19 | UserForm1.Top = 200 |
| 20 | End Sub |
検索でヒットしたときの処理は自由自在
今回紹介しているExcel VBAでは検索した後の処理として、色を操作するプログラムを組み込んでいます。
よく仕事で使われる処理プログラム
・テキストを変換
・行や列を削除
・行や列を非表示
・別のBookに張り付ける
などなど
〇〇で検索し、ヒットしたら〇〇を行う
といった機械的な作業を行っている方はこの検索・置換VBAを導入するべき、今まで3時間や4時間かかっていた作業が一瞬で終わります。
検索後の便利な処理について下記の記事で詳しく紹介しています。ぜひ参考にしてみてください。
| 主にデスクワークをしている方で、図形の操作が必要となるExcelでの書類作成は意外と多いのではないでしょうか・図形の〇をつける・図形の横棒で斜線を引く・図形の三角で印をつける契約書や注文書を作成する際、以前使ったデータをコピーし、参考にしながら作成するという方法がよく使われますが、この方法には大きなリスクがあります。それは、図形の変更を忘れてしまう！ということ 豆父ちゃん私はよく忘れて上司に叱られています・・VBAを使えば図形の変更を忘れてしまう！というリスクがなくなり、ほかにもいろんなメリット... VBAで図形操作を自動化【 すぐに使えるサンプルを紹介 】 - mamemametochan.com |
VBAでつくる検索・置換の機能の方がずっと便利
Excelの検索・置換のツールはとても便利なものですが、検索をかけてヒットした後の処理が自由に設定できなくて不便を感じていました。
豆父ちゃん
よく検索をかけるようなExcel業務をしている方にはおすすめのプログラムです。
仕事でこのプログラムを導入して1日約2時間、1ヵ月約35時間の作業の効率化を行いました。
Excelに最初からある検索・置換ツールでは出来ない処理が可能となり、Excel作業の効率化につながります。ぜひ参考にしてみてください。