VBAで文字列を検索・置換するマクロを作成してみた【 コピペして使えるサンプルマクロを掲載 】
豆父ちゃん
こんな要望にお応えします。
この記事では、エクセルに備わっている「検索・置換」機能と同じような処理を行うプログラムをVBAを使って作成する方法をご紹介しています。自分でプログラムを作成することで実務にあったいろんな条件を追加で設定することができます。
- 検索してヒットしたテキストの色を変更する
- 検索してヒットしたテキストを「」でくくる
- 検索してヒットしたテキストを別のテキストに変換し色を変える
上記のような処置を自由自在に追加することができます。
VBAを使った「検索・置換」プログラムはエクセルで大量のデータを集計、編集するような業務の効率をアップさせることができます。エクセルで大量のデータ分析や集計作業が多いという方は是非参考にしてみてください。
目次
VBAを使った検索・置換プログラムとは
※上記の動画ではユーザーフォームが映っていませんが、ユーザーフォームに検索値、検索範囲を入力するとプログラムが実行される。実際のエクセルでは下記のようなユーザーフォームが表示されています。
動画内のユーザーフォームに入力した値は以下のようになります。
| 検索値 | 検索範囲 |
| イチゴ | 1列目19行まで |
| イチゴ | 6列目19行まで |
| ブドウ | 1列目19行まで |
| ブドウ | 6列目19行まで |
指定した検索範囲で検索値を検索し、一致したとき色を操作するプログラムです。
豆父ちゃん
色を操作するVBAについては下記の記事で詳しく紹介しています。
「検索・置換」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時間かかっていた作業が一瞬で終わります。
検索後の便利な処理について下記の記事で詳しく紹介しています。ぜひ参考にしてみてください。
VBAでつくる検索・置換の機能の方がずっと便利
Excelの検索・置換のツールはとても便利なものですが、検索をかけてヒットした後の処理が自由に設定できなくて不便を感じていました。
豆父ちゃん
よく検索をかけるようなExcel業務をしている方にはおすすめのプログラムです。
仕事でこのプログラムを導入して1日約2時間、1ヵ月約35時間の作業の効率化を行いました。
Excelに最初からある検索・置換ツールでは出来ない処理が可能となり、Excel作業の効率化につながります。ぜひ参考にしてみてください。
VBA初心者が効率化マクロを導入する方法
VBAなんて扱ったことないけどエクセル作業を自動化するマクロを導入したい！
このような問題を抱えながら日々のエクセル業務をこなしている方がおおくいらっしゃるのではないでしょうか。
こんな問題を解決する方法がこちら
- マクロの開発を外注に依頼する
- マクロが設定されたエクセルファイルをダウンロードする
- 参考書やネットでVBAスキルを身に付ける
VBAの勉強を始める前は上記の方法で効率化マクロを導入していました。
完成されたマクロを使ってエクセル業務を処理する
↓
作業効率が２倍以上アップ！！
↓
便利すぎて自分でもマクロを設定したいと考え始める
↓
参考書やネットの情報でVBAスキルを身に付ける
↓
マクロ開発を代行できる程のスキルを習得
VBAは
他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
という特徴があります。
毎日のルーティーン作業にうんざりしている、だれでもできる単純作業に時間を費やしている、という方はあなたに合った方法でVBAを導入しちゃいましょう。
マクロの開発を外注に依頼する
マクロの開発を外注に依頼する導入方法は
VBAを全く扱えない方でもエクセル作業を自動化できる最も簡単な方法
です。
「こんなエクセル作業を自動化したい」「作った資料を自動的にPDFで保存したい」「決まった様式に写真を自動で貼り付けたい」
こんな感じで実現したい内容を依頼するだけで業務内容に合ったマクロを開発してくれます。
ガッツポーズの人
デメリットは、費用がかかることだけ。
開発内容、依頼先によってかかる費用は大きく異なります。
私が会社員の頃利用していた業者さんは１マクロあたり2万円～3万円の費用がかかりました。
疑問がある人
ちなみに、マメBlogでもエクセルマクロ開発代行サービスを承っています。
開発内容の確認、VBAコードの設定、動作確認後の調整、など全ての工程を私（マメ父ちゃん）が行っているので、
費用が安い！スピード納期！！
でやらせてもらっています。
ほとんど独学で身に付けたVBAスキルなので、内容によっては開発できないこともあるのでご了承ください。
ご相談、見積もり依頼は完全無料ですので気になる方は下記のリンクよりお問合せ下さい。
マクロが設定されたエクセルファイルをダウンロードする
VBA初心者でも『マクロが設定されたエクセルファイルをダウンロード』ですぐにエクセル作業を自動化することができます。
たとえば、
A４用紙に写真を貼り付けてコメントを入力する作業を自動化したい！という場合、
『写真を貼り付けるマクロ』が設定されたエクセルファイルをダウンロードすれば問題解決です。
実際に、『写真を貼り付けるマクロ』をダウンロードして使っている様子がこちら
ガッツポーズの人
『マクロが設定されたエクセルファイルをダウンロード』する導入方法は、
VBA初心者でもマクロを導入できる、費用が安い、
というメリットがあります。
しかし
VBAに関する情報が多く公開されているとはいえ、ネット上で想定するマクロは見つけることが難しい
というデメリットもあります。
ガッツポーズの人
個人ブログやファイルを販売できるサイト「note」や「Tips」ではいろんなマクロの情報が掲載されているので是非参考にしてみてください。
今までマメBlogで受注した効率化マクロを下記のnoteで掲載していますので併せてチェックしてみてください。
参考書やネットでVBAスキルを身に付ける
先程も紹介しましたが、
VBAは他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
プログラミングです。
言い換えると、
情報量が豊富なので自分で勉強できちゃう
ということです。
私のVBAスキルはほとんど独学で身に付けたもので、
考えた処理のほとんどをVBAで表現できるレベルにまで到達した！
と思っています。
私の作ったマクロをプロのプログラマーが見れば、
お前の書くコードはインチキだ！「変数の宣言」とか「引数」を正しく使えよ！！
と感じるはずです。
でも、
私からすればどうでもいい！！！想定した内容を正しく処理できればよくないですか！？
独学であっても『写真を貼り付けるマクロ』『全てのシートをPDF出力するマクロ』『連番を振るマクロ』など、
業務内容に合ったマクロを開発できるようになれます。
独学でVBAスキルを身に付けるコツは、
エラーが発生してもいいからひたすらコードを書く、なんでもいいからエクセル作業をマクロ化してみる
です。
とは言っても、全くの知識0の方は何をすればいいかわからないですよね。
VBA知識0の初心者の方は、参考書『たった1秒で仕事が片づくExcel自動化の教科書【増強完全版】』を読んでみてください。
VBAの知識0の方でもすいすい読み進められる内容になっています。
やさしい内容にも関わらず「え！！VBAを使えばこんなことできるの！？」と感動すると思います。
実際に私はこの参考書を読んでからVBAの勉強を始めました。
ひらめく人
VBAの魅力を発見した後は、
ひたすら自動化したい処理を実現するコードを書くだけ
です。
【VBA 写真を貼る】、【VBA PDF出力】、のようにネット検索すれば知りたいコードをすぐにゲットできます。
実現したい処理に向かって、「調べる→書いてみる→エラーを改善する→調べる」を繰り返していると
自然といろんなマクロが作れるようになっているはずです。
VBAに興味が湧いてきたという方は今からVBA学習をスタートさせちゃいましょう。