形式を選択して貼り付け 以下のコードで「元」シートと「先」シートのA列に同じ値があったら指定範囲をコ

形式を選択して貼り付け

以下のコードで「元」シートと「先」シートのA列に同じ値があったら指定範囲をコピペをしています。
これを数式で貼り付けの仕様に変更したいです。
お詳しい方宜しくお願い致します。

Dim c As Range, r As Range
With Sheets("元")
For Each c In .Range("A7", .Cells(Rows.Count, "A").End(xlUp))
If c <> "" Then Set r = Sheets("先").Range("A7", Cells(Rows.Count, "A")).Find(c)
If Not r Is Nothing Then
c.Copy r
c.Offset(, 6).Resize(2, 6).Copy r.Offset(, 6)
Set r = Nothing
End If
Next
End With

質問者からの補足コメント

• ご回答ありがとうございます。数式で貼り付けにしたい訳は、「元」シート
  のセルの色などを引っ張って来ないために必要なのです。
  何か良い方法はありませんでしょうか？

    補足日時：2022/11/11 12:33

No.4 ベストアンサー 回答者： めぐみん_ 回答日時： 2022/11/11 14:34

セルの色を引っ張ってとはセルの背景色をデフォルトにしたいと言う事ですか？

もしそうなら貼り付けた後で背景色を塗り替えるって考えもありますよ。
https://www.tipsfound.com/vba/07007

そしてその範囲をどう決めるかですが、
c.Offset(, 6).Resize(2, 6).Copy r.Offset(, 6)
だとしたなら範囲は２行×６列になりますので、

r.Offset(, 6).Resize(2, 6).Interior.ColorIndex = 0

をコピペした後に追加するとか？

若しくは条件付き書式によるものですかね？
https://www.relief.jp/docs/excel-macro-remove-fo …

r.Offset(, 6).Resize(2, 6).FormatConditions.Delete

かな？
未検証なのでミスりましたらごめんなさい。

No.5 回答者： fujillin 回答日時： 2022/11/11 16:10

こんにちは

＞数式で貼り付けにしたい訳は、「元」シート
＞のセルの色などを引っ張って来ないために必要なのです。
「数式で貼り付け」という言葉からは、2種類の内容が考えられますが、どちらなんでしょうね。

その前に、もしも「値だけ貼り付け」で良いのなら
　Range2.Value = Range1.Value
とすることで、値だけの転記ができますので、これで済むのなら一番簡単です。
あくまでも「値の転記」ですので、セルに数式は設定されません。


◇「数式で貼り付け」が「元のセルの数式のみをコピペ」したいという意味なら
手操作の「形式を選択して貼り付け」で数式を貼り付けた時と同様にすれば良いです。
構文は「マクロの記録」でも取れると思いますが、VBAならPasteSpecialメソッドがこれに相当します。
https://learn.microsoft.com/ja-jp/office/vba/api …
ただし、手操作でもそうですが、元の式の内容によっては思う通りの結果にならない場合がありますのでご注意。（シート間で一部のセル範囲をコピペする場合は特に）

◇「数式で貼り付け」が「元のセルを参照する式を貼り付けたい」という意味なら
セルの数式として、"=元のセル番地" を設定すればすみます。
対象がセル範囲なので
対象セル範囲.FormulaLocal = ”=" & 元のシート名 ＆ "!" & 元のセル範囲の左上のセル番地
とすることで、まとめて設定が可能です。
ただし、「左上のセル番地」は絶対アドレスではなく、相対アドレスにする必要がありますので、
　　左上のセル.Address(0,0)
で求めることが必要です。


※ なお、Findの対象範囲の指定に関しては、No2様のご懸念の通りと思います。

この回答へのお礼

ありがとうございます！教えていただいたことを一つづつ検証しました。時間がかかりましたが理解できたと思います。
結果うまくいきました❗

お礼日時：2022/11/15 07:00

No.3 回答者： めぐみん_ 回答日時： 2022/11/11 12:27

＞Set r = Sheets("先").Range("A7", Sheets("先").Cells(Rows.Count, "A")).Find(c)

こちらでもいけるかな？

Set r = Sheets("先").Range("A7:A" & Rows.Count).Find(c)

この回答へのお礼

ありがとうございました❗
いろいろ教えていただき感謝です！
早速修正させていただきました。

お礼日時：2022/11/15 07:03

No.2 回答者： めぐみん_ 回答日時： 2022/11/11 10:48

気になった点。

＞ Set r = Sheets("先").Range("A7", Cells(Rows.Count, "A")).Find(c)

Cellsに対してシートが適切に指定されないケースもあり得ますので、

Set r = Sheets("先").Range("A7", Sheets("先").Cells(Rows.Count, "A")).Find(c)

としてみてはいかがでしょうか。
本来はwith~end withで纏めますが、別のシートで既に使用されてますからシート名を入れておくべきかなと。

No.1 回答者： めぐみん_ 回答日時： 2022/11/11 08:53

仮にここだとして

c.Offset(, 6).Resize(2, 6).Copy r.Offset(, 6)

普通『値なら値を、数式なら数式を』コピペすると思います。