読者です 読者をやめる 読者になる 読者になる

セル内の数式を一括して書き換える

 結構な規模の表を作ってしまってから、「あっちゃー! 数式を○○にしときゃ良かったよ!」というときが結構ある。
 まあ、数式を入れたセルが全部連続していたら、先頭のセルだけ書き換えて後はオートフィルで一発、で済むんだが、数式の入っているセルが不連続の場合だったらちょっとめんどくさい。

 

 ウチの職場では、ラベルシールの印刷をExcelでやっていて(!)、しかも長年使い続けられているから今さらWordの差込印刷に移行するのもめんどくさいという実にばかげた状態になっていた。
 んで、ラベル内の住所とか相手の名前とかは、全部VLOOKUPで拾ってくるようになっているんだけど、それが丸裸のVLOOKUPなもんだからデータのないところはことごとく「#N/A」が印字されるわけで、手書きで再利用できないというwww

 まあ、あまり需要はないかも知れないけど、そのときのことを思い出して数式書き換えマクロをご紹介しましょうかね。


f:id:akashi_keirin:20170218121534j:plain

 こんな表があるとしよう。見たら分かると思うけど、E1~F9セルの範囲に「ランク表」という名前を付けてある。

f:id:akashi_keirin:20170218121540j:plain

 C列にはごらんの通りの数式が入っている。B列の得点によって表引きをして、F列のランク名を返す、というもの。

f:id:akashi_keirin:20170218121543j:plain

 当然、丸裸の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)で、できあがった数式を元のセルの数式としてセットして、セルに対する操作は完了。次のセルに進む。

f:id:akashi_keirin:20170218121547j:plain

 処理したい範囲を選択してマクロを実行。

f:id:akashi_keirin:20170218121552j:plain

 ほれ、この通り。ちゃーんと元の数式がIFERROR関数でネストされとります。

 条件設定次第で、シート全体に散らばっている数式を一括してネストしたいときなんかに使えると思います。