[[20070227164826]] 『行を挿入すると前の行の数式をコピーするには』(ゆん) >>BOT

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

 

『行を挿入すると前の行の数式をコピーするには』(ゆん)

 いつも参考にさせていただいています。
 もしどなたかご存知でしたら・・・
 ひとつの入力シートをつくっているのですが、a1からa100まで数式が入っています。
 vlookを使っているので、行を挿入すると、挿入した行の下の数式が自動的に
 その行に対応するようになるのは良いのですが、挿入した行にもその行に
 対応するvlookの関数が自動的に入るようにはできないでしょうか?

 他の列へのデータの入り状況を勘案すると、コピーした行の挿入を使うのは
 きびしいです。

 よろしくお願いいたします。

 対象シートの見出しを右クリック、VBEのコードウィンドウへ下記コード貼り付け。
Alt+Q でエクセルへ戻り、挿入>行を実行してみてください。
 
 Option Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)
     Rem 挿入行に数式のみコピー
     Dim c As Range
     If Target.Columns.Count < 256 Then Exit Sub
     If Target.Rows.Count > 1 Then Exit Sub
     For Each c In Target
         If c.Offset(-1, 0).Formula Like "=*" Then
             c.Offset(-1, 0).Resize(2, 1).Formula = c.Offset(-1, 0).Formula
         End If
     Next c
 End Sub
 
不便かもしれませんが、一行挿入のみ対応。
(みやほりん)(-_∂)b

 みやほりんさま

 カンゲキしました!
 数式だけがコピーされます!
 理想どおりのものです!!!

 ありがとうございます。
 お暇なときにで良いのですが、関数ならたぶん初心者でも覚えればすぐ書けるんだと
 思いますが、こういったVBAのコードってどう勉強したら自分で思うものを書けるように
 なれますか?

 ゆん

 自分でやろうと決意すること。
山ほど失敗作を作ること。
それでも諦めないこと。
みやほりん

 こちら、ありがとうございました。

 このVBAに追加していただきたいのですが、
 行を挿入すると、前の行のB列とD列の内容がそのままコピーされるようにしたいのですが、
 どのように書けばよいのでしょうか?

 みやほりん様のアドバイスを受けて、自分でやってみたいと思ったのですが・・・(T-T)できませんでした・・・
 どうぞよろしくお願いいたします。

 ゆん

 Option Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)
     Rem 挿入行に数式のみコピー
     Rem 変数宣言
     Dim c As Range
     Rem 対象範囲のセル数チェック
     If Target.Columns.Count < 256 Then Exit Sub
     Rem 対象範囲の列数をチェック
     If Target.Rows.Count > 1 Then Exit Sub
     Rem 対象範囲の各セルに対しループ
     For Each c In Target
         Rem 列番号が2及び4のとき一つ上の行の内容を複写
         If c.Column = 2 Or c.Column = 4 Then c.Value = c.Offset(-1, 0).Value
         Rem 上の行の内容が数式っぽいときはその内容を複写
         If c.Offset(-1, 0).Formula Like "=*" Then
             c.Offset(-1, 0).Resize(2, 1).Formula = c.Offset(-1, 0).Formula
         End If
     Next c
 End Sub
 
コメントも入れてみました。
実を言いますと、厳密な意味で「行挿入」を監視しているわけではありません。
変更されたセル範囲のサイズを確認しているだけなので、一行の範囲を選択して
行った編集操作に反応してしまいます。
また、数式複写の部分でも、「数式かどうか」ではなく、「一文字目が"="」かどうか
を見ているだけで、それがホントウに数式かどうかまではチェックしてません。
それっぽく動いているだけの「まがい物」のマクロであることは白状しておきます。
 
ご希望通り、「行を挿入」という一般的な操作で起動するマクロではありますが、
たとえば、一行選択してDelete・削除しても起動してしまいます。
結果、私の想定していない不都合が起こる可能性があることは覚えておいてください。
ユーザーが「行挿入」したかどうかを判断させるのはちょっと難しいのです。
 
起点を「行挿入した」ではなくて、ショートカットキーやコマンドボタンにして、
行を挿入もマクロに任せたほうが不都合が発生しにくいはず。
(みやほりん)(-_∂)b

 ありがとうございます!!
 なるほど・・・というか目から鱗でした。

 みやほりん様に書いていただいたマクロを書き換えれば、
 きっと自分で変更できると思い、ずっとニラメッコしていたのですが、
 何が何を意味しているのか、まったくわかりませんでした・・・

 行を挿入するマクロを作ってみたり、コピーしたり、いろいろやってできたマクロを
 覗いてみても、似たような表示に全然ならないので、すっかりこちらに頼ってしまいました・・・

 自分がやりたい操作がどのような操作なのかを分析しながらマクロを書く必要があることが
 よくわかりました。

 本当にありがとうございます!

 ゆん


 この内容を参考にしコードを書いた(コピペした)のですが、
 上手くいかないのでご質問させていただきます。
 (質問内容が同じなのでコメントに重ねて質問させて頂いているのですが、良かったのでしょうか…)

 Option Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)
     Rem 挿入行に数式のみコピー
     Dim c As Range
     If Target.Columns.Count < 256 Then Exit Sub
     If Target.Rows.Count > 1 Then Exit Sub
     For Each c In Target
         If c.Offset(-1, 0).Formula Like "=*" Then
             c.Offset(-1, 0).Resize(2, 1).Formula = c.Offset(-1, 0).Formula
         End If
     Next c
 End Sub

 上手くいかない具体的に内容は、
 行を挿入(一番左で右クリック⇒挿入)しても、挿入行の一つ上の式がコピーされないということです。
 ただし、行を削除(一番左で右クリック⇒削除)した場合はコピーされます。

 重ねて、
 c.Offset(-1, 0).Resize(2, 1).Formula = c.Offset(-1, 0).Formula
 の左項がc.Resize(2, 1).Formulaではなく、c.Offset(-1, 0).Resize(2, 1).Formulaなのが不思議です。
 (Resize(2, 1)する意図も不明なんですが。。。)

 よろしければ、ご説明して頂ければ至極幸いです。

 (だぁ)

c.Resize(2, 1).Formula
Range型変数cを基点とした一列二行のセル範囲に拡張
c.Offset(-1, 0).Resize(2, 1).Formula
Range型変数cをの1行上のセルを基点とした一列二行のセル範囲に拡張

 これを
c.Formula = c.Offset(-1, 0).Formula
とした場合は、cの上の行の数式がセル範囲がシフトしないで全く同じ数式が
cに入力されます。この現象を回避したかったのでしょうね、当時は。
 
 今から思えば、 c.FormulaR1C1 = c.Offset(-1, 0).FormulaR1C1 でよかった。
 
ただ、運用方法として、「行を挿入して起動するマクロ」は必要なのかどうか、
ですね。この問題も「入力と表示(印刷)」を同じ部分で処理することから
発生しているもののような気がします。
 
先に書いているように「まがい物」であることはお断りしておきます。
数式の入力してある列が常に固定的であるならば、いっそのこと、その列の
数式をクリアしてしまって、適当な部分まで再度数式を入力しなおす、
というロジックの方が正確に動作するでしょう。
 
私自身はこのようなケースでは入力過程でCtrl+Dを多用するか、もしくは、
入力過程で特定のセルに入力があったら、(数式を入力するのではなくて)
数式に相当するマクロを実行、のような作りにします。
(みやほりん)(-_∂)b


 みやほりん様

 早速のご回答、ありがとうございます。
 いやぁ、いろんな考え方できて、それぞれに対処法があって、おもしろいですね!

 精進していきます☆

 (だぁ)

みやほりん様

はじめまして。このページで紹介されていたのが、
ちょうど自分が探していた答えだったもので、
ためさせていただきました。

ただ、残念ながら、上のものをそのまま標準モジュールに
貼り付けると、以下の部分でエラーがでてしまいます。

     If Target.Columns.Count < 256 Then Exit Sub

また、
Option Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)
のままでは、マクロとして認識されなかったため、
Sub Worksheet_Change()
に書き換えて実行しております。

解決策がお分かりでしたら、
ご教授いただけると助かります。
どうぞよろしくお願い致します。

  (なな)


 このトピを参照した形の新しいトピを立ち上げたほうがよかったと思うけど、とりあえず。

 シートモジュールの Private Sub Worksheet_Change(ByVal Target As Range) は、そのシートでセルの変更があった時に
 自動的に、エクセルによって起動される。で、その時に、変更のあったセルを Target という引数で渡してくれる。
 なので、我々は、 Target という Rangeオブジェクトの参照が可能になる。

 一方、標準モジュールに書いた Worksheet_Change は、名前はそれっぽいけど、単なるマクロプロシジャ。
 これを実行させても、対象のセルがどこだったかはわからない。
 Option Explict を記述してあれば(記述してあるべきだけど)変数未定義のコンパイルエラーになるし
 もし、横着に(?)Option Explict を記述してなければ、Targetという名前の空白のVariant型変数が生成され
 コードでそれを参照することはできるけど、空白なので実行時エラーになるね。

 標準モジュールにおいたとして、具体的には、どのタイミングで、どのセルを処理させたいのかな?

 (ぶらっと)

私が提示しているマクロは最初から標準モジュールでの動作は想定していません。
したがって、想定外の標準モジュールからの実行がうまく行かないのを「残念ながら」と表現するのは、
塗り薬を飲み薬と間違えて飲んでいるのに、この薬は利かない、と愚痴をもらしている患者の言い分と同じことです。

また、マクロコードはそれぞれ意味がありますので、思い込みで

>ちょうど自分が探していた答えだったもので、
 :
 :
>Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>のままでは、マクロとして認識されなかったため、
>Sub Worksheet_Change()
>に書き換えて実行しております。

ということをやっても大抵失敗します。

解決策はコードは元のまま、標準モジュールではなくて、
作業対象とするワークシートモジュールへコードを登録することです。

(みやほりん)(-_∂)b


 ところで、領域を、2003までならリスト、2007以降ならテーブル(いずれも、正体はListObject)にしておけば
 領域内の行挿入、領域への行追加含めて、式は(マクロなしで)自動コピーされる。

 (ぶらっと)

みやほりん様 ぶらっと様

アドヴァイス本当にありがとうございました。ななです。
それから、大変失礼致しました。私の知識不足が原因で、本来動くはずのものが
動かないんですね。本当に失礼な問い合わせをしてしまい申し訳ありませんでした。

おかげさまで取り急ぎテーブル機能を用いて問題は解決できたのですが、
できれば、マクロでも、対応できるようにしたいので、
もう1つアドヴァイスいただけないでしょうか。
現在、標準モジュールではなく、ワークシートにそのままのコードを
貼り付けて、マクロを選択して実行しようとしています。
ただ、マクロボタンを押してもマクロのタイトルが表示されないため
マクロが実行できないのです。
たぶん、またもや手順を間違えているんだと思うのですが、
何がいけなかったのでしょうか?

どうぞよろしくお願いいたします。

(なな)


 シートモジュールに書いた Private Sub Worksheet_Change(ByVal Target As Range) は、コメントしたように
 シート上のセル変更によって、エクセルが自動的に起動してくれるものであって、マクロボタンに登録したりして
 呼び出すことができるものとは異なる。

 逆に言えば、ボタンに登録しなくてもいい。

 そうではなく、あくまで、ボタンに登録して、実行のタイミングを操作者に指定させる必要がある要件なら
 通常のマクロとして、標準モジュールに書いておく。

 ただ、これもコメントしたとおり、たとえば

 Sub Worksheet_Change()

  コード

 End Sub

 というマクロだったとして、これは、どんなタイミングで、どんなことをしようとしているのかな?
 おそらくは、今、準備しているコードでは役に立たないと思われる。

 1.何をどうしようとしているかを具体的に『ことば』で説明し
 2.そのために、今準備してあるコードをアップすれば、回答あるいはアドバイスがあると思うよ。

 (ぶらっと)

 マクロにもいろいろ種類があることを調べてみるとよいかと思います。
http://www.k1simplify.com/vba/tipsleaf/leaf311.html

 この質問で取り扱っているのは、シートのイベント処理(条件によって実行される処理)
 で標準モジュールなどのように、直接実行する種類のものではありません。

 イベント処理とは異なるものですが、こういうもので代替できないでしょうか。
 アクティブセルに行を挿入し、前の行の式をコピーするものです (ただし先頭行では行の挿入のみです)。
 Sub 行挿入_AND_数式コピー()
    ActiveCell.EntireRow.Insert
    If ActiveCell.Row = 1 Then Exit Sub
    ActiveCell.Offset(-1, 0).EntireRow.Copy
    ActiveCell.EntireRow.PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False
 End Sub
 (Mook)


おそらく「数式だけ」を挿入行へ追加したい、との要望だと思われます。
Mookさんのコードの xlPasteFormulasでの貼り付けだと、定数セルもそのまま
コピーされてしまうので、要望と異なるかもしれません。その場合は、

    ActiveCell.EntireRow.SpecialCells(xlCellTypeComments).ClearContents

をEnd Subの前に追加して、定数セルのクリアとするのが良いかもしれません。
(みやほりん)


 ↑の、みやほりんさんのコードの(xlCellTypeComments。これは xlCellTypeConstants かな?

 いずれにしても、この行に仮に、定数部分がなければエラーになるので、ねんのため、前後に
 On Error Resume Next と On Error GoTo 0 を書いておいた方がいいね。

 (ぶらっと)


みやほりん様 ぶらっと様 Mook様

せっかくすぐにアドヴァイスいただいているのに、
理解するのに時間がかかってレスポンスが遅くなってすみません。
ななです。

おかげさまでできました!!
ワークシートに貼り付け後、ただそのまま挿入すればよかったんですね!!
あ〜〜知識不足のせいで理解するのに、時間がかかってしまいましたが、
おかげさまでこれで、2003と2007のエクセルが混在する場所で
問題の対処ができそうです。

皆様のおかげです。

本当にありがとうございました。

他にもアドヴァイスをいただきました部分、
少し時間はかかりますが、
理解して取り入れていきたいと思います。

本当にありがとうございました。m(_ _)m

(なな)


コメント返信:

[ 一覧(最新更新順) ]


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