[[20190510203545]] 『結合されたセル 別シート数式をコピーしオートフ』(ぬた) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『結合されたセル 別シート数式をコピーしオートフィルで最終行まで貼り付けたい』(ぬた)

Excel VBAにて"Sheet2"の結合セル(A21:A40)内にある数式をコピーし、"Sheet1"の結合セル(A21:A40)にペースト、最終行までオートフィルを実行させたいのですが、うまく実行できません。

"Sheet2"の貼り付け対象範囲セルは元データ数によって変化します。
元データ数:20→20行分,元データ数:50→50行分
また対象セルはすべて結合されており(結合されて出てくるようになっています)、(A21:A40),(A41:A60),(A61:A80)...と1セルあたり20個のセルが結合されています。

  Dim i As Long

    Dim j As Long

    i = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    j = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

    Worksheets("Sheet1").Range("A21:A40" & i).Copy
    Worksheets("Sheet2").Range("A" & j).Offset(2, 0).PasteSpecial xlPasteAll
ご教授お願い致します。

< 使用 Excel:Excel2010、使用 OS:Windows10 >


上記のコードにて実行しましたが、2行目以降数式が反映されていませんでした。

(ぬた) 2019/05/10(金) 21:34


 >2行目以降
 というのを把握(理解)できませんが

 >"Sheet2"の結合セル(A21:A40)内にある数式をコピー
 と
 >Worksheets("Sheet1").Range("A21:A40" & i).Copy
 は明らかに一致しませんね。
 特に "A21:A40" & i とは?
    "A21:A"   & i ですか?

(チオチモリン) 2019/05/10(金) 22:01


コメントありがとうございます。

誤記訂正させていただきます。

"Sheet2"の結合セル(A21:A40)内にある数式をコピー → >"Sheet1"の結合セル(A21:A40)内にある数式をコピー  
×「Excel VBAにて"Sheet2"の結合セル(A21:A40)内にある数式をコピーし、"Sheet1"の結合セル(A21:A40)にペースト、最終行までオートフィルを実行させたいのですが、うまく実行できません。」

〇「Excel VBAにて"Sheet1"の結合セル(A21:A40)内にある数式をコピーし、"Sheet2"の結合セル(A21:A40)にペースト、最終行までオートフィルを実行させたいのですが、うまく実行できません。」

"A21:A" & i で実行したところコピー対象範囲が行の最終行(1048576行)までになってしまい、うまく実行できませんでした。

作成コードですが、他サイトにて掲載されていたコードをコピーさせていただいたものですので、自身で把握できていないのが正直なところになります。
申し訳ありません。
(ぬた) 2019/05/10(金) 22:42


何か繰り返し処理のコードが入っているのではないでしょうか。
コードの一部だけ切り出しても正しい情報を十分に得ることはできないので
もしわかるならその部分、
わからないのであればコード全部をこちらに示してください。
(sheet無限増殖) 2019/05/10(金) 23:20

(sheet無限増殖)さん コメントありがとうございます。

下記コードが全てになります。
また、参考にさせていただいたサイトのリンクになります。
 http://www.excelvba.club/entry/2016/11/02/211136
こちらのサイトに掲載されているコードが自分が行いたい処理に近いのではと思い、単純に当てはめた次第です。

 Dim i As Long
    Dim j As Long

    i = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    j = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

    Worksheets("Sheet1").Range("A21:A40" & i).Copy
    Worksheets("Sheet2").Range("A" & j).Offset(2, 0).PasteSpecial xlPasteAll

大変申し訳ありませんが、ご教授お願い致します。

(ぬた) 2019/05/10(金) 23:46


横からですけど、
 Worksheets("Sheet1").Range("A21:A40" & i).Copy
         ↓
 Worksheets("Sheet1").Range("A21:A" & i).Copy

ではありませんか?

(もこな2) 2019/05/11(土) 09:46


また、
"Sheet2"の結合セル(A21:A40)
"Sheet1"の結合セル(A21:A40)
というようにどちらも、【結合】セルなので、値を操作するときは、左上に当たるセルのみを指定します。
    Sub test()
        Worksheets("Sheet2").Range("A21:A40").Cells(1.1).Formula = _
        Worksheets("Sheet1").Range("A21:A40").Cells(1.1).Formula
    End Sub

さらに、オートフィルは【マクロの記録】でどのような命令を使えばよいかの情報が得られるはずですので試してみてください。

ただ、
>また対象セルはすべて結合されており(結合されて出てくるようになっています)、(A21:A40),(A41:A60),(A61:A80)...と1セルあたり20個のセルが結合されています。
が、私にはよく理解できないです。
もともと結合されているのか、結合されたセルを貼付した結果、結合されているのか・・・

場合によっては、オートフィルは関係なくて、数式と書式をコピペするだけでよいかもしれません。

(もこな2) 2019/05/11(土) 10:05


(もこな2)さん コメントありがとうございます。

アドバイスをいただき心より感謝申し上げます。

下記コードによる結合セルの操作を教えていただきありがとうございます。
Sub test()

        Worksheets("Sheet2").Range("A21:A40").Cells(1.1).Formula = _
        Worksheets("Sheet1").Range("A21:A40").Cells(1.1).Formula
    End Sub
こちらのコードをもとにFor〜Nextで最終行取得ができるのではと思い自分の僅かな知識をもとに作成及び実行しようとしましたが、できませんでした。

  Dim i As Integer

    Dim j As Integer
    Dim c As Long
    Dim d As Long

    i = 21
    j = 40
    For c = i To Cells(Rows.Count, 1).End(xlUp).Row
    For d = j To Cells(Rows.Count, 1).End(xlUp).Row

    Worksheets("Sheet2").Range(Cells("A" & i), Cells("A" & j)).Cells(1.1).Formula = _
    Worksheets("Sheet1").Range("A20:A41").Cells(1.1).Formula
    Next
    Next
VBA初心者がある知識で入力しましたが、全くの見当違いだと思います。。。

また、マクロの記録にて、試しに”A80”までオートフィルをしてみました。

 Range("A21:A40").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A21:A40").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A21:A80"), Type:=xlFillDefault
    Range("A21:A80").Select
>Range("A21:A80")部分を最終行までできるように工夫を凝らせば良いのでしょうか。。

>また対象セルはすべて結合されており(結合されて出てくるようになっています)、(A21:A40),(A41:A60),(A61:A80)...と1セルあたり20個のセルが結合されています。
が、私にはよく理解できないです。
もともと結合されているのか、結合されたセルを貼付した結果、結合されているのか・・・

>もともと結合されています。
かんばん?を大量に発行する作業があるのですが、手順としてはまずシートは3つあります。(※例a,b,c)
まず、aシートに任意の数値を入力します。仮に20とします。
入力後、マクロのボタンがあるのでクリックすると、bシートに20枚分のかんばんが行方向に出てきます。
※最初のかんばん一枚目の範囲(A1:J20)以降同範囲が数値分
最後に、cのシートに数式貼り付け用の模擬かんばんがあるので、模擬かんばん(A21:J40)範囲内の(A21:A40)結合セル範囲の数式をコピーし、bシートかんばん2枚目の(A21:A40)に貼り付け、データがあるところまで下方向にオートフィルを行います。
かんばんのイメージとしては※例一枚目(A1:A20)が連番を表示する範囲、(B1:J20)がかんばん情報が表示されるようになっています。いずれの範囲もすべて結合セルになります。
?@かんばん範囲/列番号範囲 一枚目(A1:J20)/(A1:A20)、二枚目(A21:J40)/(A21:A40)...となります。

2枚目から数式を張り付けるのは最初の一枚目に関しては連番”1”がふられているのですが、2枚目からは連番部分が空白になっているためです。
空白の理由としては、かんばん情報の内容が途中で切り替わり、切り替わり後はまた1からにする必要があるためです。その判定を行う数式がcシートのものになります。

※既にあるマクロに手を加えることはできません。
※自分は新たに連番の数式を張り付けれるマクロを作成しようとしています。

かなり説明が長くなってしましましたが、ご確認いただきご教示ください。
お願い致します。
(ぬた) 2019/05/11(土) 11:49


 説明を読む限り ↓ で良さそうですけど

 Worksheets("Sheet2").Range("$A$21:$A$400").FormulaR1C1 = "=R[-20]C+1"

(チオチモリン) 2019/05/11(土) 13:23


(チオチモリン)さん コメントありがとうございます。

教えていただいたコードをもとに数式を代入、実行したところ任意のセルまで貼り付け、処理ができました。

Worksheets("Sheet2").Range("$A$21:$A$400").FormulaR1C1 = "=IF(RC[1]=R[-20]C[1],R[-20]C+1,1)"

上記のコードになるのですが、

>Range("$A$21:$A$400").FormulaR1C1
この部分を可変する最終行まで表示させたいのですが、どのようなコードがありますでしょうか。
最終行は対象データによって変わるので、Cells(Rows.Count,1)End(xlUp).Rowなどを使用するのでしょうか。

ご教示お願いいたします。

(ぬた) 2019/05/11(土) 14:19


 >Cells(Rows.Count,1)End(xlUp).Row
 この書き方は好きな人も多いですが私はほとんど使いません。
 状況依存のコードだからです。
 本件の場合も
 >可変する最終行
 を判定するのに1列(A列)が適していないのはわかりますが
 じゃあどの列で判定出来るかというと、これまでの説明の中には記述されていません。
 故に ↓ を使うなら、判定できる列を指定する事。がアドバイスになります。
 >Cells(Rows.Count,1)End(xlUp).Row

 又 SpecialCells(xlCellTypeLastCell)から求める方法もありますが、これも状況によりますので好みません。
 (本件では使えそうですが)

 で、これまでの説明の中で
 >aシートに任意の数値を入力します。仮に20とします。
 >入力後、マクロのボタンがあるのでクリックすると、bシートに20枚分のかんばんが行方向に出てきます。
 ということなので、
 Range("$A$21:$A$400")を
      ↓
 Range("$A$21:$A$" & Cstr(Range("$A$21:$A$" & Worksheets("Sheet1").Range(aシートに任意の数値を入力するセルのアドレス).Value * 20))に変更
 を試してみて下さい。

(チオチモリン) 2019/05/11(土) 16:53


 チオチモリンさんへ

 > >Cells(Rows.Count,1)End(xlUp).Row
 > この書き方は好きな人も多いですが私はほとんど使いません。
 > 状況依存のコードだからです。

 ちょっと驚きの見解なんですが、
 チオチモリンさんが常用する「最終行確認方法」がどんなものなのか、教えていただけませんか?

 ※ 横道にそれるとマズいので、このトピックが解決した後で結構です。

(半平太) 2019/05/12(日) 16:18


 チオチモリンさんへ

 いまここで論じると、脇道にそれ過ぎるので、
 そっくり、新トピックに移動させていただきます。ご了承ください。m(__)m
       ↓
 最終行確認の作法
[[20190513094442]]

(半平太) 2019/05/13(月) 09:58


チオチモリンさん

コメントいただきありがとうございます。
また返信を遅れてしまい大変申し訳ありません。

実は[aシートに任意の数値を入力する]ですが、かなり細かなマクロが組まれています。
自分が少し簡略的に説明しすぎました。大変申し訳ありません。

ですが、チオチモリンさんのコードをヒントに自分の導きたい答えに繋がることができ、無事完成しましたことを報告させていただきます。
本当に感謝しています。

皆さま、今回は貴重なご意見、アドバイスを下さりありがとうございました。
自分自身、ステップアップできるよう日々精進します。
(ぬた) 2019/05/14(火) 02:38


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.