Excel VBA 置き換えた文字列を別のセルに出力する

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

置き換えた文字列を別のセルに出力する方法

文字列の置換えをするには、指定したセル範囲の中から指定した値を別の値に置換えるRange【レンジ】オブジェクトReplace【リプレイス】メソッドや、文字列を別の文字列に置換えるReplace【リプレイス】関数がありますが、いずれも、置換えるデータを置換え後のデータに上書きするルーチンです。そこで、置換える前のデータはそのままで、置換え後のデータを別のセルに出力することができるワークシート関数のSubstitute【サブスティテュート】関数をご紹介します。Substitute【サブスティテュート】は「代替」という意味があります。

Substitute【サブスティテュート】関数

データを置換えて元のデータはそのままで置換え後のデータを別のセルに出力するにはワークシート関数のSubstitute【サブスティテュート】関数を使用します。

Substitute【サブスティテュート】関数の書式

[]内は省略可能です。
オブジェクト.Substitute(文字列, 検索文字列, 置換文字列, [置換対象])


設定値(引数)の説明

  • オブジェクト(必須)
    ワークシート関数のコンテナ(入れ物)を表すApplication【アプリケーション】オブジェクトのWorksheetFunction【ワークシートファンクション】プロパティで取得したWorksheetFunction【ワークシートファンクション】オブジェクトを指定します。
  • 文字列(必須)
    置き換える文字を含む文字列を指定します。目的の文字列が入力されたセル参照を指定することもできます。
  • 検索文字列(必須)
    置換する文字列を指定します。
  • 置換文字列(必須)
    検索文字列を検索して置き換える文字列を指定します。
  • 置換対象(省略可)
    検索文字列に含まれるどの文字列を置換文字列と置き換えるかを指定します。 置換対象を指定した場合、検索文字列中の置換対象文字列だけが置き換えられます。 指定しない場合、検索文字列中のすべての文字列が置換文字列に置き換えられます。

A列のデータを置換えてB列に出力した実行結果


コード例

Sub 代替()
Dim 範囲 As Range
Dim 置換前 As String
Dim 置換後 As String
Dim 置換文字 As String
置換前 = "前"
置換後 = "後"
Set 範囲 = Range("A2", "A" & Cells(Rows.Count, 1).End(xlUp).Row)
範囲.Select
For Each 範囲 In Selection
置換文字 = 範囲.Value
置換文字 = WorksheetFunction.Substitute(置換文字, 置換前, 置換後)
範囲.Offset(, 1).Value = 置換文字
Next
End Sub

コードの解説

2行目【Dim 範囲 As Range】
文字列を置換えるデータ範囲を格納する変数「範囲」をオブジェクト型(Range)で宣言します。


3行目【Dim 置換前 As String】
Substitute【サブスティテュート】関数の引数に設定する置換えする置換え前の文字列を格納する変数「置換前」を文字列型(String)で宣言します。


4行目【Dim 置換後 As String】
Substitute【サブスティテュート】関数の引数に設定する置換えする置換え後の文字列を格納する変数「置換後」を文字列型(String)で宣言します。


5行目【Dim 置換文字 As String】
置換した後の文字列を格納する変数「置換文字」を文字列型(String)で宣言します。


6行目【置換前 = “前”
変数「置換前」に置換え前の検索する文字列の「前」を代入します。


7行目【置換後 = “後”】
変数「置換後」に置換え後の文字列の「後」を代入します。


8行目【Set 範囲 = Range(“A2”, “A” & Cells(Rows.Count, 1).End(xlUp).Row)】
Range【レンジ】プロパティで指定したA2セルからCells【セルズ】プロパティを使用して参照したA列の最終行セルからEnd【エンド】プロパティを使用して上方向にデータが入力されている最終セルまで移動し、その行番号をRow【ロウ】プロパティで取得して参照したA列のデータが入力されている範囲をオブジェクト変数「範囲」にSet【セット】キーワードを使用して代入します。


9行目【範囲.Select】
オブジェクト変数「範囲」に格納されているA列のデータ範囲をRange【レンジ】オブジェクトのSelect【セレクト】メソッドを使用して選択します。


10行目【For Each 範囲 In Selection】
For Each【フォーイーチ】ステートメントを使用してオブジェクト変数「範囲」に選択されているオブジェクト変数「範囲」のセルを繰り返し処理の中で順にオブジェクト変数「範囲」に代入して、オブジェクト変数「範囲」に格納されているセルの数分、繰り返します。


11行目【置換文字 = 範囲.Value】
繰り返し処理の中で、変数「範囲」に代入されているセルの値を変数「置換文字」に代入します。つまり、A2セルからデータが入力されている最終行までのセルの値をA2セルから順に代入します。


12行目【置換文字 = WorksheetFunction.Substitute(置換文字, 置換前, 置換後)】
ワークシート関数のコンテナ(入れ物)を表すApplication【アプリケーション】オブジェクトのWorksheetFunction【ワークシートファンクション】プロパティで取得したWorksheetFunction【ワークシートファンクション】オブジェクトのSubstitute【サブスティテュート】関数を使用して、文字列を置換えて変数「置換文字」に再代入します。


13行目【範囲.Offset(, 1).Value = 置換文字】
Range【レンジ】オブジェクトのOffset【オフセット】プロパティを使用して、置換え前のデータ範囲が格納されているオブジェクト変数「範囲」を1列右に移動した位置に変数「置換え文字」に格納されている置換え後の文字列を代入します。


14行目【Next】
ここまでの処理をオブジェクト変数「範囲」に格納されているセルの数分繰り返します。


以上で、置き換えた文字列を別のセルに出力する方法についての解説を終了します。ありがとうございました。

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