[[20180406201256]]　『行挿入時実行時エラー１００４が出ます回避方法は』（Ia）　ページの最後に飛ぶ

『行挿入時実行時エラー１００４が出ます回避方法は有りますでしょうか』（Ia）

以前このサイトで投稿されていましたＶＢＡを使用させて頂いていますが
どちらも，行を挿入しますと実行時エラー（デバッグ）が出ます。
回避方法が有れば教えて頂けませんでしょうか。
マクロ初心者です。宜しくお願いします。<m(__)m>

 Private Sub Worksheet_Change(ByVal Target As Range)
     Dim r As Range
     If Intersect(Target, Columns(11)) Is Nothing Then Exit Sub
Application.EnableEvents = False
     For Each r In Intersect(Target, Columns(11))
         If r.Row > 1 Then
             With r.Resize(, r(, Columns.Count - 10).End(xlToLeft).Column - 10)
                 .Offset(, 1).Value = .Value
             End With
         End If
     Next
Application.EnableEvents = True
 End Sub

AOption Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)
     Dim lc As Long
     Dim trgrow As Long
     Dim buf As Variant

    If Application.Intersect(Target, Range("e:e")) Is Nothing Then
        Exit Sub
    End If
    ' ここに、項目の入力されている行を超えた場合処理中止のifがあったほうが良い
    Application.EnableEvents = False
    If Target.Value <> "" Then
        trgrow = Target.Row
        lc = Cells(trgrow, Columns.Count).End(xlToLeft).Column
        If lc >= 2 Then '(NN)が修正←過去の点検記録が無い場合
            buf = Range(Cells(trgrow, 2), Cells(trgrow, lc))
            Range(Cells(trgrow, 5), Cells(trgrow, lc + 3)).Value = buf
            Range("e" & trgrow).Value = "" '(NN)が追加←B列入力後空欄にする
            Range("d" & trgrow).Value = "" '(NN)が追加←B列入力後空欄にする
            Range("c" & trgrow).Value = "" '(NN)が追加←B列入力後空欄にする
        End If
    End If
    Application.EnableEvents = True
    If Application.Intersect(Target, Range("c:c")) Is Nothing Then
        Exit Sub
    End If
    ' ここに、項目の入力されている行を超えた場合処理中止のifがあったほうが良い
    Application.EnableEvents = False
    If Target.Value <> "" Then
        trgrow = Target.Row
        lc = Cells(trgrow, Columns.Count).End(xlToLeft).Column
        If lc >= 2 Then '(NN)が修正←過去の点検記録が無い場合
            buf = Range(Cells(trgrow, 2), Cells(trgrow, lc))
            Range(Cells(trgrow, 6), Cells(trgrow, lc + 4)).Value = buf
        End If
    End If
    Application.EnableEvents = True
    If Application.Intersect(Target, Range("b:b")) Is Nothing Then
        Exit Sub
    End If
    ' ここに、項目の入力されている行を超えた場合処理中止のifがあったほうが良い
    Application.EnableEvents = False
    If Target.Value <> "" Then
        trgrow = Target.Row
        lc = Cells(trgrow, Columns.Count).End(xlToLeft).Column
        If lc >= 2 Then '(NN)が修正←過去の点検記録が無い場合
            buf = Range(Cells(trgrow, 2), Cells(trgrow, lc))
            Range(Cells(trgrow, 7), Cells(trgrow, lc + 5)).Value = buf
        End If
    End If
    Application.EnableEvents = True
 End Sub
また，一つ教えて頂きたいのですがＯ列に入力するとＱ列の同じ行に同じ日付が入る
ＶＢＡがあれば教えて頂けませんでしょうか，関数で処理はできるのですが，
処理が終わればＱ列に入力する必要が有ります。その都度関数を入力するのは効率が悪いので、
値だけの移動と，行挿入時にエラーが出ないように出来ますでしょうか

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

逆に質問です。
そもそも提示したマクロの動きは理解されてますか？
（コードを読むことができますか？）

＞また，一つ教えて頂きたいのですがＯ列に入力するとＱ列の同じ行に同じ日付が入る
＞ＶＢＡがあれば教えて頂けませんでしょうか
＞値だけの移動
値のコピーなのか、移動なのか明確にしましょう。

（もこな２） 2018/04/06(金) 20:43

もこな２様

＞そもそも提示したマクロの動きは理解されてますか？

 （コードを読むことができますか？） 
　正直マクロの動きは理解できていません。<m(__)m>
＞値のコピーなのか、移動なのか明確にしましょう。 
　　　B列　　　O列　　　P列　　　　Q列
　　項目　　入荷日　　品質　　　○店入荷日
　リンゴ　　 4/6       A          4/6
  みかん　　4/5        B          完売
　イチゴ　　4/1        C          4/1
  かき　　　4/2        A          返却
O列と同じ値がQ列に入力されるといいのです。
分りにくい質問で申し訳ありません。宜しくお願いします。
（Ia） 2018/04/06(金) 21:08

＞正直マクロの動きは理解できていません。
余計なおせっかいですが、それは結構危ない状況です。
なにしろ、どこの誰がどんな意図をもってどのように動くことようにしたものかわからないコードを平気で実行していることになるので、最悪データ消されても文句言えないです

さて本題です。

ご提示のコードのうち

 Worksheet_Change　←ここに注目です。

これは、ワークシートイベントの一つChangeイベントであることを意味します。
動きとしては、セルの値が変更されたときに発生します。
（※値そのものが入力されたときや消去されたときに発生。）

なので、「Ｏ列に入力するとＱ列の同じ行に同じ日付が入る」であれば
変更のあったセル（範囲）にＯ列に含まれるものがあったら、Ｑ列の同じ行に
同じ値を入れればよいってことになりますよね。

コードにするとこんな感じです。

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim buf As Range, MyRNG As Range

        Stop '←実行上は要らない。ブレークポイントの代わり

        'セルの値を変更したセル（範囲）とO列が重複する範囲を求めて「buf」に格納
        Set buf = Intersect(Target, Columns("O"))

        'bufがNothingでない（つまり、変更したセル範囲にＯ列のものが含まれている）場合だけ実行
        If Not buf Is Nothing Then
            '「buf」に格納したセル（範囲）から「MyRNG」に一つずつ取り出して処理
            For Each MyRNG In buf
                Cells(MyRNG.Row, "Q").Value = MyRNG.Value
            Next MyRNG
        End If

    End Sub

これをステップ実行して実際にどうなるか、変数に何が格納されているかを
確認してみてください。
そして、上記のコードが理解できるようになったら、提示したコードが
何をやっているのかもう一度考えてみてください。

ちなみに、エラーにも種類があるので、質問される場合はどのようなエラーが
出ているのかも伝えたほうがよいです。回答者側で状況が掴みやすくなります。

（もこな２） 2018/04/06(金) 23:21

もこな２様
夜遅くまで有難うございます。
＞そして、上記のコードが理解できるようになったら、提示したコードが
何をやっているのかもう一度考えてみてください。
勉強してみます。マクロは難しいですね。(~_~;)
申し訳ありませんが、教えて頂いたコードを使用したところ
＞ Stop '←実行上は要らない。ブレークポイントの代わり
にデバックで黄色で塗りつぶされます。やり方が間違っているのでしょうか、
ヒントを頂けないでしょうか、宜しくお願いします。<m(__)m>
（Ia） 2018/04/06(金) 23:56

 ＞これをステップ実行して
         ~~~~~~~~~~~~

とお伝えしたつもりですが・・・・・
（もこな２） 2018/04/07(土) 06:59

もこな２様
お早う御座います。朝早くから有難うございます。
＞これをステップ実行して
ステップINしていくと実行されました。
＞そして、上記のコードが理解できるようになったら、提示したコードが
何をやっているのかもう一度考えてみてください。
マクロは始めたばかりで難しくて、時間がかかりそうです。
O列に入力してEnterで実行できないでしょうか、マクロ初心者で申し訳ありません。
宜しくお願いします。<m(__)m>
（Ia） 2018/04/07(土) 07:51

なんだ・・質問じゃなくてマクロ作成依頼だったんですね・・・

私は作成依頼はお受けしない方針なのでここで降ります。
他の回答者？をお待ちください。
（もこな２） 2018/04/07(土) 08:20

もこな２様
そんなつもりでは無かったんですが、申し訳ありません。
たまたまこのサイトを見て投稿マクロを見て初めてマクロを使いました。
大変申し訳ありませんでした。ご教授して頂きましたマクロを勉強していきます。
作成して頂く意図は有りませんでしたが、指摘して頂くとおりですね。
大変、大変申し訳ありませんでした。深夜から朝早くまで有難うございました。
（Ia） 2018/04/07(土) 08:38

 >どちらも，行を挿入しますと実行時エラー（デバッグ）が出ます。

 ＞Private Sub Worksheet_Change(ByVal Target As Range)
の行にブレークポイントを設定して
（参考URL＞＞http://www.ken3.org/vba/excel-help.html）
ウォッチ式をTarget.Address(0,0)とr.Address(0, 0)と設定しておいて、
（参考URL>>https://www.vba-ie.net/debug/watch.html）

行を挿入してみると、
イベントが発生したときに、変数「Target」にその行のセル範囲が代入されるようです。

そのセル範囲からｒが求められてますが、
r(, Columns.Count - 10).End(xlToLeft).Column
で、データ最終列を求めていると思いますが、
当然空白なのでEnd(xlToLeft)で1列目の1が返ってきます。
それから無条件に-10してますので、マイナスの値で
セル範囲をリサイズしようとしてますが、マイナスのセル範囲って存在しないですよね？
なので、実行時にそれが解ったので実行時エラーとなります。

ちなみに、実行前に文法がおかしくないかチェックされますが、
おかしければ、コンパイルエラーとなります。

なぜそういう風に書いたかわかりませんが、
言い回しがすごく遠回り（そういう風にしか表現できない場合もあるでしょうが）なので、
解りずらいです。
もっと簡単に表現したらいいと思います。

まずは日本語でやりたいことを明確にしてはいかがでしょう？
で、それをVBA語ではどう表現するのかを調べたり聞いたりしてはいかがでしょうか？
（まっつわん） 2018/04/07(土) 09:40

＞また，一つ教えて頂きたいのですがＯ列に入力するとＱ列の同じ行に同じ日付が入る
＞ＶＢＡがあれば教えて頂けませんでしょうか
僕が書いたらこんな感じ。

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngEvent As Range   'イベント処理をする対象セル範囲
    Dim rngTarget2 As Range '操作対象セル範囲

    'O列の1行目を除くセル範囲
    With Me.Columns("O")
        Set rngEvent = .reize(, .Rows.Count - 1).Offset(1)
    End With

    '値の変更があった場合、対象のセル範囲と重なるセル範囲を操作対象として取得
    Set rngTarget2 = Intersect(Target, rngEvent)
    '重なるセルが無ければ処理を抜ける
    If rngTarget2 Is Nothing Then Exit Sub

    'イベント処理の無効化（マクロでセルに入力する際はイベント処理を止める）
    Application.EnableEvents = False
    '2列右に転記
    rngTarget2.Offset(, 2).Value = rngTarget2.Value
    'イベント処理の有効化
    Application.EnableEvents = True
End Sub

これなら、短いコードの方も応用して書き換えられるのでは？

長い方のコード。
何をやりたいか解らないので、
どう対応していいかわからない。

手っ取り早く判定するなら、
一番最初に、
「Targetに渡されたセル範囲の全部セルが空白なら（行挿入と判定して）処理を抜ける」
とでも記述したらいいように思いますが、そんなんでいいか
やりたいことが解ってないのでなんとも。。。。。。

＞その都度関数を入力するのは効率が悪いので、
＞値だけの移動と，行挿入時にエラーが出ないように出来ますでしょうか
すごーく簡単に考えていると思いますが、
エクセルの標準機能にない機能をカスタマイズして追加しようとしてるのですよ。
操作者は、どんな操作をするか解らないです。
つまり、想定外の操作でプログラムが止まったり、
デバッグの画面が出ないようにプログラムを作ったり、メンテナンス出来る必要があります。
当然、困ったらこういう場所で相談するのもありでしょうが、
自分でメンテナンスをする気概がなければ、マクロを使わない方が良いです。

Iaさんは単に、
1個1個のセルに単に入力する場合しか想定してなかったのですよね？
コードはある程度複数セルをコピペした場合も想定されているように感じます。
ただ、「間違ったセル範囲に貼りつけてしまった。」というようなことは
想定されて無いような気がします。
まぁ、万が一にもコピペなんかしない。
たまにデバッグ画面が出てもきにしない。
ということならそれでもいいかもしれませんが、
僕ならこんな操作はマクロ化するより、
エクセルの手動の操作に慣れろって思います。

なぜなら、
1つ目の理由：
マクロを実行すると、元に戻すの履歴が消えて元に戻せなくなるからです。

2つ目の理由：
マクロを設定してあるファイルしか使えなくなるので、
いつまでたってもエクセルが使えないのは操作者にとって損。
慣れたらどんどんいろんなことをエクセルでして欲しいですよね？

3つ目の理由：
Windowsやエクセルは3～4年ごとにバージョンアップしてます。
パソコンの買い替えやなんやらで、
いずれ使えなくなったりメンテナンスしなければいけない状況が出てくるかもしれない。
ソフトが動かない＝仕事が出来ない
という状況が出てこない保証がない。
その時に早急に対応できるの？？？？

と、まぁ大げさに言うとそういう状況が出てきます。
遊びの延長でマクロを扱うのもありですが、
そもそも同じ値を2回入れるのにどれくらい不便なんだろうって思ってます

O列のセルに行って、数値をテンキーで入れる、
Enterキーじゃなくて、左手でTABキー2回押して右手でテンキーでまた数値入力
ここでEnterキー押下で次の行
としたらよいと思います。
他にもショートカットキーで調べたらいろいろ便利なキー操作があるので、
そちらを覚えた方が得策かなぁとは思います。

まぁ、元凶は
.End(xlToLeft)
だろうからそれを使わない表現でセルの位置を表現できるようになればいいと思います。

（まっつわん） 2018/04/07(土) 10:33

まっつわん様　もこな２様
有難うございます。
マクロは難しく手が出せなかったのですが、たまたまこのサイトで投稿されていたマクロが
使えそうなので、分らないまま変更し使いました。無知で申し訳ありません。
＞Enterキーじゃなくて、左手でTABキー2回押して右手でテンキーでまた数値入力
ここでEnterキー押下で次の行
としたらよいと思います。
項目数が多いのと入力時がバラバラんなので、単純に投稿しました。申し訳ありません。
＞なんだ・・質問じゃなくてマクロ作成依頼だったんですね・・・
他の投稿者様にご迷惑をお掛けし申し訳ありませんでした。
まっつわん様にはマクロを作成して頂き有難うございました。
ご教授頂きましたことをもとに、これからマクロを勉強したいと思います。
また、分らないことが有れば質問したいと思います。その節は宜しくお願いします。
本当に有難うございました。<m(__)m>

（Ia） 2018/04/07(土) 11:10

降りると言っておきながら気になったのでコメントします。

申し訳ないとおっしゃっておられますが、別に怒っても責めてもいませんので気にしないでください。

わたしはまっつわんさんと違ってものぐさなので、マクロ作ってちょっとでも楽するっていうのは賛成なんです。

ただ、よくわからないマクロをそのまま”使える”との思いこみだけで実行してしまうと、マクロの作成者が善意であっても想定外の動作をさせることにつながりますし、マクロの作成者が悪意を持っている場合、マクロを使って深刻なダメージを与えることもできてしまいますから、いまのままで大丈夫かな～って勝手に心配してるだけです。

たとえば、以下のマクロを「Thisworkbook」と書かれているモジュールに張り付けてみると楽しいですよ＾＾
なんて言われたら実行してしまうのではないでしょうか？（この程度であれば実害はないですが・・・）

    Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Application.EnableEvents = False
        Target.Clear
        Application.EnableEvents = True
    End Sub

なので、素性のわからない人が作ったマクロを実行するのであれば、がんばってどのような動きをするものであるのか理解をしたうえで実行することを強くお勧めします。
そして、その過程で分からない部分があれば、その都度調べたりすればよいですし、どうしてもわからなければその部分をこのような質問サイトで聞いてみればいいとおもいます。

ちなみに、O列に入力があったら、Q列に同じ値を書き込むだけでよければ、
　Stop '←実行上は要らない。ブレークポイントの代わり

って書いた部分を削除すれば止まらずに動くと思いますよ。
ステップ実行して研究してほしかったので意図的に止まるようにしただけなので・・・
（もこな２） 2018/04/07(土) 13:07

＞わたしはまっつわんさんと違ってものぐさなので、
＞マクロ作ってちょっとでも楽するっていうのは賛成なんです。
あぁ、ぼくはもっともっとものぐさなので、
訳のわからんマクロで悩むくらいなら手動の操作を極めた方が、
応用が効くだろうと思っているだけです。
（まっつわん） 2018/04/07(土) 13:18

もこな２様　まっつわん様
親身に教えて頂き有難うございます。
＞Stop '←実行上は要らない。ブレークポイントの代わり
ブレークポイントを知れべていましたが、削除は分かりませんでした。
有難うございます。
＞たとえば、以下のマクロを「Thisworkbook」と書かれているモジュールに張り付けてみると楽しいですよ
張り付けて勉強したいと思います。
＞よくわからないマクロをそのまま”使える”との思いこみだけで実行してしまうと、マクロの作成者が
善意であっても想定外の動作をさせることにつながります
投稿しました行挿入時の実行時エラーについては、まっつわん様、もこな２様の助言を参考にし、
私が投稿したマクロを勉強し再度質問させて頂きます。
自信はありませんが(~_~;)その節は宜しくお願いします。
無知な私に親切にご教授頂きまして、本当に有難う御座いました。感謝感謝。

（Ia） 2018/04/07(土) 14:37

ごめんなさい。いろいろ言葉が足りなかったようで・・・

「マクロ作ってちょっとでも楽するっていうのは賛成」っていうのは、そういう考え方は好きですよって言いたかっただけなんです。たとえ手動操作よりすご～く回りくどくなってしまっても、それはそれで本人に経験値として蓄積されるので、将来同じ問題に直面した時に、マクロ、手動どちらのアプローチがいいのか見極める力がつくと思うんですよね。
要は、『成功するための失敗なら、それは必要な失敗である』みたいな・・・
まっつわんさんの考え方がおかしいというつもりはなかったので、気分を害されてしまったのであれば申し訳ないです。

「以下のマクロを「Thisworkbook」と書かれているモジュールに張り付けてみると楽しいですよ」
こちらは、なんでも盲目的に信じるとよろしくないコードも実行してしまう恐れがありますよといことをお伝えしたかったんです。
ちなみに、そのコードをブックモジュールにはりつけると、ブック内のどのシートのどのセルに入力しても、すぐにセルの書式を含めてクリアされるという迷惑なコードになっていますので、どうしても試すのであれば新規ブックに張り付けてくださいね。

（もこな２） 2018/04/07(土) 16:18

もこな２様
有難う御座います。
＞マクロを「Thisworkbook」と書かれているモジュールに張り付けてみると楽しいですよ
試してみました。
マクロは難しいですね。ご教授有難う御座います。
（Ia） 2018/04/07(土) 16:47

挿入時にエラーにならないようにするということだけなら、
If Target.Count = Columns.Count Then Exit Sub
を最初に追加します。
（γ） 2018/04/08(日) 08:03

（r）様
お早う御座います。
いけました。本当に有難う御座いました。
皆様方の優しさには感謝しております。
もっと勉強ししっかり質問できるようにしたいと思います。
本当に有難う御座います。

（Ia） 2018/04/08(日) 08:56

コメント返信：

[ 一覧(最新更新順) ]

[[20180406201256]] 『行挿入時実行時エラー１００４が出ます回避方法は』（Ia） ページの最後に飛ぶ

[[20180406201256]]　『行挿入時実行時エラー１００４が出ます回避方法は』（Ia）　ページの最後に飛ぶ