Excel VBA 表記の揺れや間違いを一括修正する

スポンサーリンク
スポンサーリンク

表記の揺れや間違いを一括修正する方法

多数の人が入力したデータや
複数箇所から読み込んだデータには
表記の揺れやミスが付き物です。

このような場合は
置き換えて修正したい値の一覧表を作成して
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

コードの解説

2行目
【Dim ボタン As Range】

作成するボタンの位置の基準となるセルを格納する
変数「ボタン」を
オブジェクト型(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

コードの解説

2行目
【Dim 修正一覧 As Range】

先のコードで追加した
修正一覧シートの修正一覧表のセル範囲を格納する
変数「修正一覧」を
オブジェクト型(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関数を使用してメッセージを表示します。


以上で
表記の揺れや間違いを一括修正する方法についての解説を終了します。
ありがとうございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク