表記の揺れや間違いを一括修正する方法
多数の人が入力したデータや複数箇所から読み込んだデータには、表記の揺れやミスが付き物です。
このような場合は、置き換えて修正したい値の一覧表を作成してRange【レンジ】オブジェクトのデータを置き換えるReplace【リプレイス】メソッドとループ処理を組み合わせて一括して表記を修正する仕組みを用意すると大変便利です。
ここでは、例として修正一覧表のシート作成と、修正実行ボタンを作成するコード、修正実行ボタンをクリックした時に実行するReplace【リプレイス】メソッドとループ処理を組み合わせて一括して表記を修正するコードをご紹介します。
修正一覧表のシートと修正実行ボタンを作成する例
実行イメージ
コードと解説
Sub 修正一覧表() Dim ボタン As Range Set ボタン = Range("C2") With Worksheets.Add(after:=Worksheets(Worksheets.Count)) .Name = "修正一覧表" .Range("A1").Value = "置き換え前" .Range("B1").Value = "置き換え後" End With With ActiveSheet.Buttons.Add(ボタン.Left, ボタン.Top, ボタン.Width, ボタン.Height) .OnAction = "置き換え" .Characters.Text = "置き換え" End With MsgBox "「修正対象」の列に修正したい文字列を入力し" & vbCrLf & _ "「置き換え」列に置き換えたい文字列を入力してください。" & vbCrLf & _ "入力が終了したら「置き換え」ボタンをクリックしてください。" End Sub
作成するボタンの位置の基準となるセルを格納する変数「ボタン」をオブジェクト型(Range)で宣言します。
3行目【Set ボタン = Range(“C2”)】
C2セルをオブジェクト型変数「ボタン」にSetキーワードを使用して代入します。
4行目【With Worksheets.Add(after:=Worksheets(Worksheets.Count))】
Worksheets【ワークシーツ】コレクションのAdd【アド】メソッドを使用してワークシートを追加します。
シートを指定したシートの右に追加するを表す引数after【アフター】にはWorkSheets【ワークシーツ】プロパティの引数をWorksheets【ワークシーツ】コレクションのCount【カウント】プロパティで指定した、ワークシート数を指定して一番最後(右側)にワークシートを追加して、With【ウィズ】ステートメントで指定します。
5行目【.Name = “修正一覧表”】
With【ウィズ】ステートメントで指定した、追加したワークシートのName【ネーム】プロパティを使用して、追加したワークシートに「修正一覧表」と名前を付けます。
6行目【.Range(“A1”).Value = “置き換え前”】
With【ウィズ】ステートメントで指定した追加したワークシートのA1セルに「置き換え前」の文字列を入力します。
7行目【.Range(“B1”).Value = “置き換え後”】
With【ウィズ】ステートメントで指定した追加したワークシートのB1セルに「置き換え後」の文字列を入力します。
9行目【With ActiveSheet.Buttons.Add(ボタン.Left, ボタン.Top, ボタン.Width, ボタン.Height)】
WorkSheet【ワークシート】オブジェクトのフォームコントロールのコマンドボタンの集まりを表すButtons【ボタンズ】コレクションのAdd【アド】メソッドを使用して、ワークシートに「コマンドボタン」を作成してWith【ウィズ】ステートメントで指定します。ボタンの位置と大きさを表す引数はRange【レンジ】オブジェクトのLeft、Top、Width、Heightプロパティでポイントを取得して設定します。
10行目【.OnAction = “置き換え”】
With【ウィズ】ステートメントで指定した、作成したコマンドボタンを表すButton【ボタン】オブジェクトの実行するマクロ名を設定するOnAction【オンアクション】プロパティに実行するマクロ名の「置き換え」を設定します。
11行目【.Characters.Text = “置き換え”】
With【ウィズ】ステートメントで指定した作成したコマンドボタンを表すButton【ボタン】オブジェクトのコマンドボタンの表面に表示する文字列を設定するCharacters.Text 【キャラクターズテキスト】プロパティに「置き換え」を設定します。
13行目~15行目【MsgBox “「修正対象」の列に修正したい文字列を入力し” & vbCrLf & _
“「置き換え」列に置き換えたい文字列を入力してください。” & vbCrLf & _
“入力が終了したら「置き換え」ボタンをクリックしてください。”】
MsgBox関数を使用してメッセージを表示します。
「置き換え」ボタンをクリックしたときに発生するマクロ例
実行イメージ
コードと解説
上記の修正一覧表作成コードと同じ標準モジュールに記述します。
Sub 置き換え() Dim 修正一覧 As Range Dim データ範囲 As Range Dim i As Long Set 修正一覧 = Worksheets("修正一覧表").Range("A1").CurrentRegion Set データ範囲 = Worksheets(1).UsedRange For i = 2 To 修正一覧.Rows.Count データ範囲.Replace _ What:=修正一覧.Cells(i, 1).Value, _ Replacement:=修正一覧.Cells(i, 2).Value, _ Lookat:=xlWhole Next i Worksheets(1).Select MsgBox "データを置き換えました。" End Sub
先のコードで追加した、修正一覧シートの修正一覧表のセル範囲を格納する変数「修正一覧」をオブジェクト型(Range)で宣言します。
3行目【Dim データ範囲 As Range】
文字列を置き換える対象のシートのセル範囲を格納する変数「データ範囲」をオブジェクト型(Range)で宣言します。
4行目【Dim i As Long】
修正一覧表のデータの行数を格納する、繰り返し処理で使用されるカウンター変数「i」を長整数型(Long)で宣言します。
5行目【Set 修正一覧 = Worksheets(“修正一覧表”).Range(“A1”).CurrentRegion】
先のコードで追加した「修正一覧表」シートのA1セルからCurrentRegion【カレントリージョン】プロパティを使用して、A1セルから始まるデータ範囲を参照し、オブジェクト変数「修正一覧」にSetキーワードを使用して代入します。
6行目【Set データ範囲 = Worksheets(1).UsedRange】
Worksheets【ワークシーツ】プロパティで引数にインデックス番号を表す1を設定して、一番左(手前)にあるワークシートを参照し、Worksheet【ワークシート】オブジェクトのUsedRange【ユーズドレンジ】プロパティを使用して使用されているセル範囲を参照し、オブジェクト変数「データ範囲」にSetキーワードを使用して代入します。
7行目【For i = 2 To 修正一覧.Rows.Count】
For Next【フォアネクスト】ステートメントを使用して、繰り返し処理の始まりです。カウンター変数「i」に繰り返し処理の中でオブジェクト変数「修正一覧」に格納されている修正一覧リストの行数を行の集まりを表すRows【ロウズ】コレクションのCount【カウント】プロパティで修正一覧表の行数を取得して2~行数の値を順次代入します。
8行目~11行目【データ範囲.Replace _
What:=修正一覧.Cells(i, 1).Value, _
Replacement:=修正一覧.Cells(i, 2).Value, _
Lookat:=xlWhole】
置き換えしたい文字列があるシート(Worksheet(1))のUsedRange【ユーズドレンジ】プロパティで参照した使用されているセル範囲が格納されているオブジェクト変数「データ範囲」の
置き換えをする、Replace 【リプレイス】メソッドを実行します。
修正の対象になる文字列を設定する、引数What【ワット】にはオブジェクト変数「修正一覧」に
格納されている「修正一覧」シートの修正一覧表の1列目の置き換え前のデータを設定します。
置き換え後の文字列を設定する引数Replacement【リプレイスメント】には、2列目の置き換え後のデータを設定します。検索内容を引数What【ワット】で指定した内容と完全一致させるかどうかを設定する引数LookAt【ルックアット】には完全一致を表すxlWholeを設定します。
12行目【Next i】
7行目~ここまでの処理をカウンター変数「i」に順次格納される2~修正一覧表の行数分繰り返し処理が実行され対象データの置き換え処理が実行されます。
13行目【Worksheets(1).Select】
繰り返し処理が抜けたら置き換え対象のデータがあるWorkSheet(1)を選択してアクティブにします。
14行目【MsgBox “データを置き換えました。”】
MsgBox関数を使用してメッセージを表示します。
以上で、表記の揺れや間違いを一括修正する方法についての解説を終了します。ありがとうございました。