セル内の数式を一括して書き換える
結構な規模の表を作ってしまってから、「あっちゃー! 数式を○○にしときゃ良かったよ!」というときが結構ある。
まあ、数式を入れたセルが全部連続していたら、先頭のセルだけ書き換えて後はオートフィルで一発、で済むんだが、数式の入っているセルが不連続の場合だったらちょっとめんどくさい。
ウチの職場では、ラベルシールの印刷をExcelでやっていて(!)、しかも長年使い続けられているから今さらWordの差込印刷に移行するのもめんどくさいという実にばかげた状態になっていた。
んで、ラベル内の住所とか相手の名前とかは、全部VLOOKUPで拾ってくるようになっているんだけど、それが丸裸のVLOOKUPなもんだからデータのないところはことごとく「#N/A」が印字されるわけで、手書きで再利用できないというwww
まあ、あまり需要はないかも知れないけど、そのときのことを思い出して数式書き換えマクロをご紹介しましょうかね。
こんな表があるとしよう。見たら分かると思うけど、E1~F9セルの範囲に「ランク表」という名前を付けてある。
C列にはごらんの通りの数式が入っている。B列の得点によって表引きをして、F列のランク名を返す、というもの。
当然、丸裸のVLOOKUPだから、B列が空白になったら「#N/A」エラーが返る。
そこで、このコード。
Sub addIfError()
Dim objCell As Range
Dim str As String
For Each objCell In Selection '……(1)
With objCell
If .Formula <> "" And _
Mid(.Formula, 1, 8) <> "=IFERROR" Then '……(2)
str = objCell.Formula '……(3)
str = Right(str, Len(str) - 1) '……(4)
str = "=IFERROR(" & str & ","""")" '……(5)
objCell.Formula = str '……(6)
End If
End With
Next
End Sub
(1)で、選択範囲の各セルへの繰り返し処理にする。ちょい書きマクロではよく使うと思う。
(2)は条件指定。今回は連続セルに同じような数式が入っているパターンなので不要といえば不要。一応、「数式が入っていて、なおかつ『=IFERROR』で始まっていないセル」だけを処理対象にするための条件指定。
ここは、必要に応じて条件を追加したら良いと思う。
(3)で、変数strに一旦数式を入れる。この例の1回目のループだったら、「=VLOOKUP(B2,ランク表,2,TRUE)」がstrに入る。
(4)で、strに入っている数式から左端の「=」を取り除く。次に出てくるIFERROR関数の引数にするため。
(5)で、変数strを書き換える。元の数式から「=」を取り除いたものを、IFERROR関数の第1引数にセットしている。なお、この例では、エラーが出た場合に何も表示しないようにするため、第2引数には「""」を指定している。ちょっと「"」(ダブルクォーテーション)の対応関係がややこしいので注意しよう。
これで、変数strの中身は「=IFERROR(VLOOKUP(B2,ランク表,2,TRUE,"")」になる。「VLOOKUP~がエラーを返さなかったらそのまま表示して、エラーだったら何も表示しない」ということになる。
(6)で、できあがった数式を元のセルの数式としてセットして、セルに対する操作は完了。次のセルに進む。
処理したい範囲を選択してマクロを実行。
ほれ、この通り。ちゃーんと元の数式がIFERROR関数でネストされとります。
条件設定次第で、シート全体に散らばっている数式を一括してネストしたいときなんかに使えると思います。