【Excel VBA】数式をコピペ&値で貼り付け直すマクロ 〜解説編〜

Excel VBA

数式をコピペ&値で貼り付け直すマクロを解説します。

このマクロには、実践的なオブジェクト変数の使い方も含んでいます。

Worksheeオブジェクトの基本編 >> こちら

Rangeオブジェクトの基本編 >> こちら

Microsoft Office搭載の中古PC【PCSTORE】

お題:契約開始日と契約終了日をもとに契約月数(満月数)を算出する

次のようなワークシートがあるとします。

  • ワークシート名 [契約月数]
  • A列 [契約開始日]
  • B列 [契約終了日]
  • C列 [契約月数(満月数)]

A列・B列に入力されている日付を利用して、C列に契約月数(満月数)を求めます。

マクロの実行前とマクロの実行後のイメージは、こんな感じです。

実行前

契約月数(満月数)の数式・値貼り付け前

実行後

契約月数(満月数)の数式・値貼り付け後

VBAコードの実例

こちらの記事に書いたVBAコードをもとに解説します。

'契約月数(満月数)を求める数式をコピぺ&値で貼り付け
Sub CopyPaste_FormulaToValue()
    
    '作業するワークシート(Worksheet)をオブジェクト変数に格納
    Dim Ws As Worksheet
    Set Ws = ThisWorkbook.Worksheets("契約月数")
    
    'コピペする範囲(Range)をオブジェクト変数に格納
    Dim Rng As Range
    Set Rng = Ws.Range("C2:C22")
    
    '契約月数(満月数)を求める数式
    Dim srcFml As String
    srcFml = "=DATEDIF(A2, B2, " & Chr(34) & "M" & Chr(34) & ")"
    
    'C列2行目に数式をセット
    Ws.Range("C2") = srcFml
    
    '対象範囲の全体に数式をコピペ
    Ws.Range("C2").Copy
    Rng.PasteSpecial Paste:=xlPasteFormulas
    
    '対象範囲の全体を値で貼り付け
    Rng.Copy
    Rng.PasteSpecial Paste:=xlPasteValues
    
    '貼り付け範囲の点線を解除
    Application.CutCopyMode = False
    
    'セルC1を選択
    Ws.Range("C1").Select

End Sub

VBAコードの解説

VBAコーディングの流れ

数式のコピペ&値で貼り付け直しを自動化するマクロ
  • 1
    作業に使うオブジェクトを変数に格納
    • Worksheetオブジェクト
    • Rangeオブジェクト
  • 2
    数式を変数に格納
    • 満月数の算出にDATEDIF関数を使用
  • 3
    C列2行目に数式をセット
  • 4
    対象範囲に数式をコピペ
    • Copyメソッド
    • PasteSpecialメソッド (xlPasteFormulas)
  • 5
    同一範囲を値で貼り付け直し
    • Copyメソッド
    • PasteSpecialメソッド (xlPasteValues)
  • 6
    終了処理

オブジェクトを変数に格納

    '作業するワークシート(Worksheet)をオブジェクト変数に格納
    Dim Ws As Worksheet
    Set Ws = ThisWorkbook.Worksheets("契約月数")
    
    'コピペする範囲(Range)をオブジェクト変数に格納
    Dim Rng As Range
    Set Rng = Ws.Range("C2:C22")

ワークシートとセルの範囲をオブジェクト変数に格納しています。

  • Worksheetのオブジェクト変数 Ws <– [契約月数]シート
  • Rangeのオブジェクト変数 Rng <– セルC2〜C22の範囲

Worksheetオブジェクト・Rangeオブジェクトの基本について詳しく知りたい方は、次の記事をどうぞ。

Worksheeオブジェクトの基本編 >> こちら

Rangeオブジェクトの基本編 >> こちら

数式を変数に格納&セルにセット

    '契約月数(満月数)を求める数式
    Dim srcFml As String
    srcFml = "=DATEDIF(A2, B2, " & Chr(34) & "M" & Chr(34) & ")"

数式を変数に格納するときは、文字列の先頭にあらかじめ = を付けておきます。

そうすることで、セルに格納された文字列は数式として認識されます。

途中に挟んでいるChr(34)は、ダブルクォーテーションの代わりです。

セルに格納されると、このような数式になります。

=DATEDIF(A2, B2, “M”)

契約月数(満月数)の算出には、Excel標準のDATEDIF関数を使用します。

=DATEDIF([起点の日付], [終点の日付], “M”)

    'C列2行目に数式をセット
    Ws.Range("C2") = srcFml

ワークシートWsを指定してC列2行目のセルに、変数に格納した数式を代入します。

対象範囲に数式をコピペ

    '対象範囲の全体に数式をコピペ
    Ws.Range("C2").Copy
    Rng.PasteSpecial Paste:=xlPasteFormulas

ワークシートWsのセルC2をコピーします。

コピーした数式を、範囲Rngに「形式を選択して貼り付け」します。

「形式を選択して貼り付け」で数式をペーストするには、次のように記述します。

(貼り付け先).PasteSpecial Paste:=xlPasteFormulas

同一範囲を値で貼り付け直し

    '対象範囲の全体を値で貼り付け
    Rng.Copy
    Rng.PasteSpecial Paste:=xlPasteValues

ワークシートWs内の範囲Rngをコピーします。

範囲Rngに「形式を選択して貼り付け」します。

「形式を選択して貼り付け」で値をペーストするには、次のように記述します。

(貼り付け先).PasteSpecial Paste:=xlPasteValues

終了処理

いずれも見た目の問題なので、省略してもOKです。

    '貼り付け範囲の点線を解除
    Application.CutCopyMode = False
    
    'セルC1を選択
    Ws.Range("C1").Select

コピペを実行すると、貼り付けられた範囲が点線で残ります。

通常は[Esc]キーを押すなどして消去するのですが、これをマクロで実行するのが次の1行。

Application.CutCopyMode = False

セルの選択については省略します。

まとめ

Worksheetオブジェクト・Rangeオブジェクトを使って、数式をコピペ&値で貼り付け直すマクロを解説しました。

オブジェクト変数をうまく使って、VBAコードを効率化しましょう!

Excel VBA
\ お友だちにも教えてあげましょう/
プロフィール
最適屋

Excel VBAとAccessを独学で習得。2011年から現在まで株式会社アントレ(旧: 株式会社リクルート アントレユニット)と業務委託契約。Excel/Accessで開発した様々な効果集計ツール・営業支援ツールを提供。

Excelで業務効率化
タイトルとURLをコピーしました