置換の２種類の方法
VBAで置換を行うには２つの方法があります。
１つはReplace関数を使う方法で、もう１つはReplaceメソッドを使う方法です。
Replace関数はVBAの処理で使う文字列の置換用関数で、ReplaceメソッドはExcelの検索と置換ダイアログをVBAで動作させることができます。
どちらを使っても結果は同じなのですが、ReplaceメソッドはExcel上での検索と置換ダイアログの設定内容に影響を及ぼすため、そういうことがないReplace関数を使った方が都合が良いためこちらを利用します。
なお、以下でReplace関数とReplaceメソッドについては以下で紹介していますので必要があればご参照ください。
Replace関数「VBAで文字列を別の文字列に置換する（Replace）」
Replaceメソッド「VBAでセルの置換を行う（Replaceメソッド）」
処理手順
選択セル範囲の文字列を一括置換する手順ですが、Selectionプロパティを使って選択セル範囲を取得し、あとは１セルずつ設定内容を置換していくことになります。
基本的な処理はこれで全てですが、注意すべき点があります。
それはセルの値が数式の場合です。
数式がないことが分かっているのであれば、各セルのRangeオブジェクトのValue値をReplace関数で置換してしまえばいいのですが、数式がある場合はValue値は数式の演算結果が格納されているため、数式が削除され、ただの値に置き換わるという意図しない置換が行われる恐れがあります。
それを回避するために、セルが数式かどうかを判定した上で置換を行うことになります。
なお、セルが数式かどうかの判定についての詳細については「VBAで数式が設定されているセルを高速で探す」をご参照ください。
選択セル範囲の文字列を一括置換するサンプルコード
上記の説明の内容を実装すると以下のようなコードになります。
数式かどうかを判定するにはFormulaプロパティ値が”=”で始まっていて、かつ、Valueプロパティ値と異なっている場合です。その条件を満たさない場合は数式ではありません。
選択セル範囲に、単に「”aaa”」と入力されているセルと、数式で「=”aaa”」と入力されて”aaa”が表示されているセルがある場合は、それぞれ「”AAA”」と「=”AAA”」に置換されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Sub SelectionReplaceTest() Dim rSelection As Range '// 選択セル範囲 Dim r As Range '// １セル Dim sFind '// 検索文字列 Dim sReplace '// 置換文字列 Dim f '// Formula値 Dim v '// Value値 sFind = "aaa" sReplace = "AAA" Set rSelection = Selection '// 選択セル範囲を１セルずつループ For Each r In rSelection f = r.Formula v = r.Value '// セルが数式の場合 If (Left(f, 1) = "=") And (f <> v) Then '// 数式を置換 r.Formula = Replace(f, sFind, sReplace) '// セルが数式でない場合 Else '// Value値を置換 r.Value = Replace(v, sFind, sReplace) End If Next End Sub |
複数セルのSelection.Valueへの一括置換は出来ない
Selectionプロパティを使えば１セルずつループせずに対象セル全部のValue値を一気に置換できるのでは！？と思ってこういうコードを思いつくかもしれません。
しかし残念ながらエラーで動きません。
12行目の左辺の「r.Value」への代入でエラーになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub SelectionReplaceTest1() Dim r As Range '// 選択セル範囲 Dim sFind '// 検索文字列 Dim sReplace '// 置換文字列 sFind = "a" sReplace = "A" Set r = Selection r.Value = Replace(r.Value, sFind, sReplace) '// ここでエラー End Sub |
各セルに入っているValue値はバラバラのため、値を一意に特定できずにエラーになります。
仮に選択セル範囲のValue値が全て同じであっても、そもそも複数セルのRangeオブジェクトにはValueプロパティが存在しません。