[[20190615173928]]　『「数式だけコピーしたい』（T19）　ページの最後に飛ぶ

『「数式だけコピーしたい』（T19）

投稿
[[20060706134818]] 『数式だけコピーしたい』（たんぽぽ）　

お世話になります。
上記投稿の（みやほりん）さんの回答を基にB2:M2のセル範囲にB3:M3の内容をコピーした後から
数式以外を消去するコードを作成したのですが、数式も消去されてしまいます。
（元の数式は数種類あるのですが相対参照でB３:M４範囲内を参照しているものがあります）

おそらく元の数式の参照範囲がB3:M3以外になっているからだと考えていますが、
改善コードが思いつきません。
できる限り簡素なコードで解決したいのですが…

 Sub Insert_Row()
    With ThisWorkbook.ActiveSheet
　　　　' .......
        .Range("b3:m3").Copy Destination:=.Range("b2:m2")
        .Range("b2:m2").ClearContents
    End With
 End Sub

＜使用 Excel：Excel2010、使用 OS：Windows10 ＞

＞数式以外を消去する

とは、何を消したいのでしょうか。

＞数式も消去されてしまいます。

数式だけが残った状態をイメージできないのですが？

（マナ） 2019/06/15(土) 18:17

何かわかりませんが、消したいものを消去してから。
最後に、数式のみコピーとか？

（マナ） 2019/06/15(土) 18:20

 こんばんは！

 数式以外が↓なら
 .ClearContents

 これは、書式以外をクリアだと思うのですが？

 数式以外を削除となると　値　ですから、、、

 今は、編集と言わないのかもしれませんが(古い人間ですみません）

 編集→ジャンプ→値→Delete

 これを記録されてみてはどうでしょうか？
（SoulMan） 2019/06/15(土) 18:55

 なぜこのコードで数式だけを残せると思ったんですか？
 ご提示のコードは参照先でも書式のみを残したい場合の方法となってますよね。

 >おそらく元の数式の参照範囲がB3:M3以外になっているからだと考えていますが、
 全くの見当違いです。
 ClearContentsの意味が分かってますか？

 もう初心者とは呼べないほど年数を重ねられてると思いますが、安易に人に質問してばかりで、
 自分で調べたり試す努力が全く足りてないから、分かったつもりにだけになって、
 こんな初心者でも簡単に気付けるような頓珍漢な質問をしてしまうんじゃないですか。

（sy） 2019/06/15(土) 22:22

質問の解釈が難しい・・・・
＞数式以外を消去
SoulManさんが言ってるけど、これを書式をクリアしたかったと解釈すると
ClearContents　→　ClearFormats　ですね。

【参考】
https://www.tipsfound.com/vba/07018

そうじゃなくて、値のうち数式でない=定数
と解釈した場合は、こちらもSoulManさんが言ってるようにSpecialCellsを使えばよいとおもいます。
ただ、SpecialCellsの場合、該当するセルがないときに操作しようとするとエラーになっちゃうからその対策はしておいてほうがよさそうにおもいます。

まとめるとこんな感じに。

    Sub さんぷる()
        Stop ' ←ブレークポイントのかわり

        With ThisWorkbook.ActiveSheet.Range("b2:m2")
            .Offset(1).Copy .Cells

            '▼書式をクリア
            .ClearFormats

            '▼値のうち定数をクリア
            On Error Resume Next
            .SpecialCells(xlCellTypeConstants).ClearContents
            On Error GoTo 0

        End With

    End Sub

（もこな２） 2019/06/15(土) 23:02

みなさん、ありがとうございます。

「.ClearContents」や「値」の解釈をまちがっていました。
「値」＝「書式と数式以外」と思っており、今まで特に問題なかったもので．．
取り急ぎ対策として、掲載コードの後に数式のあるセルを対象に以下コードを追加しました。
（今のところ問題なさそうです．．）

 .Range("e2:f2").FormulaR1C1 = .Range("e3:f3").FormulaR1C1     
 .Range("j2:k2").FormulaR1C1 = .Range("j3:k3").FormulaR1C1

1行目にタイトル行があり、2行目のデータ入力完了後に順次1行挿入～データ入力します。
その時に3行目以降の書式と数式は継承したく質問しました。
いろいろな方法がありそうですので、基礎を覚えて試したいと思います。

これからもよろしくお願いします。

（T19） 2019/06/16(日) 08:43

 >「.ClearContents」や「値」の解釈をまちがっていました。 
 >「値」＝「書式と数式以外」と思っており、
 ん？？？
 ClearContentsは右クリックメニューの「数式と値のクリア」の事ですよ。
 「Delete」キーでも同じ結果になりますけど。

 >3行目以降の書式と数式は継承したく質問しました。
 これは値だけ転記したくないと言う事ですか？
 入力規則やコメントは書式に含まれませんが、今回は無視して良いのかな？

 参照先では誰も触れてませんが、これは数式のみを転記するコードではありません。
 数式と一緒に値も転記されます。
 これに関してはT19さんが勘違いしてもある意味仕方ないと思います。
 いい加減な回答ですね。
 でもこう言うのも試せば直ぐに分かる事です。
 >.Range("e2:f2").FormulaR1C1 = .Range("e3:f3").FormulaR1C1     
 >.Range("j2:k2").FormulaR1C1 = .Range("j3:k3").FormulaR1C1

 >「値」＝「書式と数式以外」
 厳密には違います。
 コメントやメモは書式ではありません、入力規則も。
 セル情報は「値」「数式」「書式」「入力規則」
 「コメント(2016以降はメモと名称変更)・2016以降のコメント(対話型メモ)」に別れます。

 >掲載コードの後に数式のあるセルを対象に以下コードを追加しました。 
 >今のところ問題なさそうです．．） 
 >.Range("e2:f2").FormulaR1C1 = .Range("e3:f3").FormulaR1C1     
 >.Range("j2:k2").FormulaR1C1 = .Range("j3:k3").FormulaR1C1
 なら「入力規則」と「コメント」を使用してない(もしくは転記されても良い)なら、全てコピペでも同じ事ですよね。

 >.Range("b3:m3").Copy Destination:=.Range("b2:m2")
 これだけで望む結果にならないんですか？

 上述の「入力規則」や「コメント」を使用していて、それらはコピペしたくないと言う事でしょうか？
 それともそれらは試してないと言う事でしょうか？

 >いろいろな方法がありそうですので、基礎を覚えて試したいと思います。
 と言うのなら試行錯誤する癖を身に付けて下さい。

（sy） 2019/06/16(日) 11:45

 >「コメント(2016以降はメモと名称変更)・2016以降のコメント(対話型メモ)」

 間違ってました、すいません。
 正しくは以下です。

 「コメント(365ではメモと名称変更)・365のコメント(スレッド形式に変更)」

（sy） 2019/06/16(日) 13:13

＞1行目にタイトル行があり、2行目のデータ入力完了後に順次1行挿入～データ入力します。
＞その時に3行目以降の書式と数式は継承したく質問しました。

たぶんテーブル機能が使えます。マクロは必要ありません。
https://www.becoolusers.com/excel/table.html

（マナ） 2019/06/16(日) 13:32

syさん
Ｅ２：Ｆ２，Ｊ２：Ｋ２（計算セル）以外のセルはデータ入力セル（文字列、数値）で、
その数値を使用して上記計算セルで計算させています。
「入力規則」と「コメント」は使用していません。

 >.Range("b3:m3").Copy Destination:=.Range("b2:m2")
 これだけで望む結果にならないんですか？

データ入力セルは空白にしときたいので掲載コードを追加しましたが、わざわざ
「ClearContents」して数式を追加なんぞせずに、上記コードの後にデータ入力セルを
「=""」にすれば事足りることに気づかせていただき、修正しました。

書式や値などセル情報の基本をわかるようにしたいと思います。

マナさん
「テーブル」は全くといっていいほど、使えません。（涙）
ピボットテーブルを使おうとしたことがあるのですが、スッと頭に入ってこなくて
どうしても“避けて（逃げて？）”いるのが現状です。
でもご紹介サイト、　わかりやすそうですね。
ブックマークして理解に努めたいと思いました。

ありがとうございます。　これからもよろしくお願いします。
（T19） 2019/06/16(日) 17:53

 >「ClearContents」して数式を追加なんぞせずに、上記コードの後にデータ入力セルを 
 >「=""」にすれば事足りることに気づかせていただき、修正しました。
 いや、そう言う事でも無いんですけど、、、

 >>.Range("b3:m3").Copy Destination:=.Range("b2:m2")
 これはT19さんのコピペの範囲が今一不明瞭だったからです。

 >Ｅ２：Ｆ２，Ｊ２：Ｋ２（計算セル）以外のセルはデータ入力セル（文字列、数値）
 と言う事なら、Ｅ２：Ｆ２，Ｊ２：Ｋ２の範囲だけをコピペすれば良いだけですよねと言ってるんです。

  Sub Insert_Row()
    Dim i As Long
    With ThisWorkbook.ActiveSheet
        i = .Range("e" & Rows.Count).End(xlUp).Row + 1
        .Range("e2:f2").Copy .Range("e" & i)
        .Range("j2:k2").Copy .Range("j" & i)
    End With
 End Sub

 テーブルなら数式はEXCELが自動で挿入してくれるので、マナさんの言われるようにマクロは必要無いです。

 >どうしても“避けて（逃げて？）”いるのが現状です。
 現在のデータ範囲を全て選択して、「テーブルとして書式設定」を選ぶだけでOKですよ。

（sy） 2019/06/16(日) 23:43

いやぁ～ホントに“頭固かった”です。
コピーペーストも基礎から学ばないと、ですね…

テーブルはこれからですが、何度もありがとうございました。
（T19） 2019/06/17(月) 09:44

コメント返信：

[ 一覧(最新更新順) ]

[[20190615173928]] 『「数式だけコピーしたい』（T19） ページの最後に飛ぶ

[[20190615173928]]　『「数式だけコピーしたい』（T19）　ページの最後に飛ぶ