VBAで文字列を検索・置換するマクロを作成してみた【 コピペして使えるサンプルマクロを掲載 】

Left Caption

豆父ちゃん

VBAで文字列を検索・置換するマクロを作りたい

こんな要望にお応えします。

この記事では、エクセルに備わっている「検索・置換」機能と同じような処理を行うプログラムをVBAを使って作成する方法をご紹介しています。自分でプログラムを作成することで実務にあったいろんな条件を追加で設定することができます。

 

  • 検索してヒットしたテキストの色を変更する
  • 検索してヒットしたテキストを「」でくくる
  • 検索してヒットしたテキストを別のテキストに変換し色を変える

上記のような処置を自由自在に追加することができます。

VBAを使った「検索・置換」プログラムはエクセルで大量のデータを集計、編集するような業務の効率をアップさせることができます。エクセルで大量のデータ分析や集計作業が多いという方は是非参考にしてみてください。

 

VBAを使った検索・置換プログラムとは

※上記の動画ではユーザーフォームが映っていませんが、ユーザーフォームに検索値、検索範囲を入力するとプログラムが実行される。実際のエクセルでは下記のようなユーザーフォームが表示されています。

動画内のユーザーフォームに入力した値は以下のようになります。

検索値 検索範囲
イチゴ 1列目19行まで
イチゴ 6列目19行まで
ブドウ 1列目19行まで
ブドウ 6列目19行まで

指定した検索範囲で検索値を検索し、一致したとき色を操作するプログラムです。

Left Caption

豆父ちゃん

「イチゴ」が一致したとき「ラーメン」に置換するプログラムにしようか迷ったけど色を操作した方がわかりやすいので今回のプログラムにしました

色を操作するVBAについては下記の記事で詳しく紹介しています。

「検索・置換」VBAと色を操作するVBAを組み合わせています。大量のデータの統計をとる、集計を行う際にとても便利なプログラムなので是非参考にしてみてください。

 

検索範囲を決めるユーザーフォームの作り方

今回の「検索・置換」VBAはユーザーフォームでテキストボックスを作り、入力された数値で検索範囲を決め、列毎に検索するというプログラムにしました。

ほかにもプログラムの作り方によっていろんな範囲検索が可能

・Sheet全体を検索

・行毎に検索

・セルからセルまでの範囲を検索

Left Caption

豆父ちゃん

Excelデータの内容によって範囲検索の方法を変えていこう

検索プログラム(標準モジュール)

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の検索・置換のツールはとても便利なものですが、検索をかけてヒットした後の処理が自由に設定できなくて不便を感じていました。

Left Caption

豆父ちゃん

検索をする仕事ではExcel VBAで作った検索・置換プログラムが大活躍します

よく検索をかけるようなExcel業務をしている方にはおすすめのプログラムです。

仕事でこのプログラムを導入して1日約2時間、1ヵ月約35時間の作業の効率化を行いました。

Excelに最初からある検索・置換ツールでは出来ない処理が可能となり、Excel作業の効率化につながります。ぜひ参考にしてみてください。