[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『コピーしたセルの挿入時、挿入行の行番号 を把握するための方法について教えてください。』（かず）
コピーしたセルの挿入時、挿入行の行番号
を把握するための方法について教えてください。
１．業務要件
(1)建設プロジェク(＝案件と呼ぶ。)の案件名、担当社、売上、利益
などを１行にまとめて月次でメンテしています
(2)大元のマスタリストがあり、それを月に一回、10人の担当者に配布。
担当者は自分の担当案件の追加や、案件の売上や利益の変化を、配布
された表に記載して返信。
(3)各担当から返信されたリストを大元のマスタに反映させています
２．担当者が案件情報をリストに反映する際の記載ルール
▼(更新)：
リストの各行に対し変更ががある場合、行の1列目に▼印をつける。
削除は行削除ではなく、案件の進捗を示すセルを用意して、
失注として表す。リストの行の削除は考慮不要。
★(挿入)：
リストに対し、案件＝行を追加する場合1列目に★印をつけて、行
ごと追加する
２．ワークシートのイメージ
(1) 最初にマスタ側リスト作成時、リスト右端余白で今は使われていない
部分に以下の作業列を追加
列 作業列１ 作業列２ 作業列３
番号 日時時刻 ｼｰｹﾝｽNo 枝番 A/１ (略) BL/64 BM/65 BN/66 現時点の枝番号の計算式 行 -------------------------------------------------------------------------------- 10 ␣ (略) 2016/3/26/ 18:23 1 1 =COUNTIF($BM$10:$BM709,BM10) 11 ␣ 略 12 ␣ 略 13 ★ (単純な空行の挿入) ・␣・ 1 =COUNTIF($BM$11:$BM709,BM11) 14 ␣ 15 ・・ ・2016/3/26/ 18:23・・6・・1 =COUNTIF($BM$15:$BM15,BM15) (A) 16 ★・(15行目をコピー挿入) 6・・2 =COUNTIF($BM$16:$BM16,BM16) (A) 17 ␣ 略 18 ␣ 略 19 ★・(20行目をコピー挿入)・ 9・・2 =COUNTIF($BM$18:$BM709,BM18) (B) 20 ␣・・・・・・・・・・・・ 9・・2 =COUNTIF($BM$19:$BM709,BM19) (B) 709 ・・・・・・・・・・・・・697・ 1 =COUNTIF($BM$10:$BM709,BM709)
【説明】
行の挿入には、単純な空行の追加とコピーした行の挿入の２種類がある。
どちらの場合も、井川はるき著 VBA裏ワザ大辞典Sample31_1のコードを
参考にして行が挿入されたことは添付のコードで検知可能。
業務要件から１列目に★印をつけたいが、コピー元はそのままで
コピー先にだけ★印をつけたい。
⇒ 単純空行の挿入の場合は、本来はシーケンスNoとしているBM列に空白が できるのでそこをFind文で探して 行番号を取得可能。 ・ただしコピーして挿入の場合は、BM列の値は同じ値の行が複数できてしまう。 BN列に枝番号や重複有無を示す計算式を入れる方法、(上記(A)(B)あるがいずれも うまくいかない）では重複していることはわかるがどの行が挿入されたかわからない。
・コピーして挿入なので タイムスタンプではコピー前とコピー後の区別がつかない
【質問事項】
Q1 コピーした行を挿入のイベントが発生した場合に、イベント処理の中で
どの行が挿入されたか枝番やタイムスタンプか、他の方法で特定する方法
をご存知の方は教えてください。
Q2 上記のイベント処理では、ブックを開いて閉じるまでのイベントをタイマ 監視しているが何かの理由でイベントの監視ができなくなった場合、 イベント処理をあきらめて手動でワークシートを記載してもらえればよい ようにするにはどうしたらよいでしょうか
既定のイベントでは Application.EnableEvents = False を発行すれば
イベントが発生しなくなるので、自作イベントでもそのようにするにはど
すればいいいでしょうか？
イベント監視処理は中止します。修正が終了したら１列目に▼や★を書くの
を忘れず記入してくださいとメッセージがだせれば十分です。
３．イベントプロシジャを使うコード
井川はるきさんおサンプルコードを一部改変して作成しました。
私には少し難しい方法のようです
' 井川はるき さんのサンプルコード
' ★印の行は 投稿者 かずが改変した部分
’***************************************
' サンプル解説の?@ クラスモジュール clsRowsInsertEvent
' ***************************************
Public Event RowsInsert(Cancel As Boolean, InsRow As Long) ’★ InsRowを追加 by かず
Public Sub CheckRowsInsert(ByVal mySht As Worksheet)
Static myRow As Range
Dim myInsRow As Long '★ 行を挿入した行番号を格納するための変数
Dim myCancel As Boolean
Dim w_FndRng As Range
Const TopRow As Integer = 29 '★ 作業列２の最初の行番号の
Const SeqCol As Integer = 65 '★ 作業列２の列番号
Const EdaNum As Integer = 66 '★ 作業列２ 枝番を格納するための行
Dim i As Long '★ 制御変数
If mySht Is Nothing Then Exit Sub
With mySht
If Not myRow Is Nothing Then
On Error Resume Next myInsRow = myRow.Row
If Err().Number <> 0 Then
’★単純な行の挿入の場合、SeqCol列の空白を探す
Set w_FndRng = Range(Cells(TopRow, SeqCol), Cells(Rows.Count, SeqCol).End(xlUp)).Find("", , xlValues, xlWhole, xlByRows, xlNext) '★
If Not w_FndRng Is Nothing Then ’★
' ★単純に行挿入された場合 SeqCol列は""空白のセルがある
myInsRow = w_FndRng.Row ' ★
Else
'★ コピーして挿入の場合、作業列２には元の行と同じ値が入る
i = TopRow '★
Do While (i < Rows.Count) '★
If Cells(i, EdaNum) > 1 Then '★
myInsRow = i '★
Exit Do '★
End If '★
i = i + 1 '★
Loop '★
End If '★
RaiseEvent RowsInsert(myCancel, myInsRow)
If myCancel Then
Application.Undo
End If
End If
End If
Set myRow = .Rows(.Rows.Count)
End With
End Sub
'***********************************************************
' ?A 標準モジュール
'***********************************************************
Sub TimerProc(ByVal Hwnd As Long, ByVal uMsg As Long _
, ByVal idEvent As Long, ByVal dwTime As Long)
On Error Resume Next
ThisWorkbook.RowsInsertEventClass.CheckRowsInsert Sheet3 ’★引数Sheet3をかずの環境にあわせて設定
End Sub
’***************************************
' ?B ThisWorkbook
' ***************************************
Private Declare Function SetTimer Lib "user32" ( _
ByVal Hwnd As Long, ByVal nIDEvent As Long _
, ByVal uElapse As Long, ByVal lpTimerFunc As Long) _
As Long
Private Declare Sub KillTimer Lib "user32" ( _
ByVal Hwnd As Long, ByVal nIDEvent As Long)
Private WithEvents myRowsInsertEventClass As clsRowsInsertEvent
Private myTimerId As Long
Private Sub myRowsInsertEventClass_RowsInsert(Cancel As Boolean, myInsRow As Long)
'
'★ 作業列１ 時刻取得して タイムスタンプをとる(予定)
'★ 作業列２ シーケンシャル番号を格納する(予定)
MsgBox myInsRow & "行に挿入されました" ’★ 現状 挿入行を確認するため MsgBoxを出力
Cancel = MsgBox("行が挿入されました。" & vbCrLf _
& "キャンセルしますか？", vbInformation Or vbYesNo) = vbYes
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myRes As VbMsgBoxResult
If Not Saved Then
myRes = MsgBox("'" & Name & "' への変更を保存しますか？" _
, vbExclamation Or vbYesNoCancel)
If myRes = vbYes Then
Save
ElseIf myRes = vbNo Then
Saved = True
Else
Cancel = True
Exit Sub
End If
End If
KillTimer 0&, myTimerId
Set myRowsInsertEventClass = Nothing
End Sub
Private Sub Workbook_Open()
myTimerId = SetTimer(0&, 0&, 0&, AddressOf TimerProc)
End Sub
Public Property Get RowsInsertEventClass() As clsRowsInsertEvent
If myRowsInsertEventClass Is Nothing Then
Set myRowsInsertEventClass = New clsRowsInsertEvent
End If
Set RowsInsertEventClass = myRowsInsertEventClass
End Property
４．サンプルコードの解説 (井川春樹さんの本から抜粋)
'*************************************************************
' 井川さんのサンプルコードの解説
'*************************************************************
では(1)から順に見ていきましょう。
まず、1行目でEventステートメントを使ってイベントを宣言しています。このクラスは
Rowslnsertというイベントを発行して、そのイベントプロシージヤは、CancelというBoolean d
の引数を参照渡しで受け取りますよ、といった宣言になります。
CheckRowsInsertメソッドは、この後解説するタイマー処理によって繰り返し実行され、
行が挿入されたタイミングでイベントを発行するメソッドです。
ここで重要なのは、Static変数｢myRow｣が最終行を表すRangeオブジェクトへの参照を保持していることです。
初めて実行されるときだけは変数｢myRow｣が｢Nothing｣なので、その場合には最終行を
表すRange オブジェクトへの参照を格納するだけの処理を行いますが､２回目以降には
いよいよチェックを行います。
このチェックには、On Error Resume Nextステートメントを使って、実行時エラーを
無視するようにしてから、変数｢myRow｣に参照が格納されているRangeオブジェクトの
何らかのプロパティを取得してみるのが簡単です。
ここではRowプロパティを取得していますが他のプロパティでもかまいません。
このとき、行が挿入されていれば最終行は存在しなくなっているのでエラーが発生します。
ですから、｢エラーの発生＝行が挿入された｣と判断できるわけです。エラーの発生の
有無はErr関数を使って参照を取得できるErrObjectオブジェクトのNumberプロパティ
で判断できます。
エラーが発生していなければNumberプロパティの値が｢O｣となるからです。
そして、行が挿入されたと判断したらRaiseEventステートメントを使ってRowslnsert
イベントを発行します。引数Cancelには変数[myCancel]を指定し、イペントプロシー
ジヤによって、変数.[myCancel｣の値が｢True｣に変更される(引数Cancelに｢True｣
が設定される)と、ApplicationオブジェクトのUndoメソッドを使って、行挿入の操作
を元に戻します。
次に(2)のコードをご覧ください。これはタイマープロシージヤと呼ばれるプロシー
ジヤで、この後解説するWin32API関数のSetTimer関数の引数lpTimeerFuncにこのプロ
シージヤのアドレスを指定すると、繰り返し非同期で実行されるようになります。
宣言部については決まり事として覚えておいてください。ここで行っている処理は、
clsRowsInsertEventオブジェクトのCheckRowsInscrtメソッドを実行するといった
ものです。
clsRowsInsertEventオブジェクトへの参照は、?Bのコードで定義している
ThisWorkbookクラスのRowsInsertEventClassプロパティを使って取得します。
最後に(3)のコードの要点をまとめます。
まず､このブックモジュールでclsRowsInsertEventオブジェ列が発行するイベント
をハンドルするために、モジュールレベルのオブジェクト変数
｢mvRowsInsertEventClass｣を、WithEventsキーワードを付けて
、cIsRowsInsertEvent型で宣言します。
そして、そのclsRowsInsertEventオブジェ列のRowslnsertイペントプロシージャに、
行挿入時に実行する処理を記述します。ここでは、キャンセルするかどうかの
問い合わせのみを行っています。
タイマー処理の開始はブックのOpenイベントプロシージャで、終了はBeforeCloseイベント
ブロシージャで行います。
タイマー処理を開始するのはWin32API関数のSetTimerで、引数HwndとnlDEventには0を、
nElapseにはタイマー処理を実行する問隔(ミリ秒)を、lpTimerFunc にはタイマー
プロシージャのアドレスを指定します。プロシージャのアドレスはAddressOf演算子を
使って取得します。
なお、サンプルではnElapseに｢O｣を指定していますが、当然ですがOミリ秒ごとに処理
を繰り返すといった非現実的なことは不可能です。このような場合処理できる極めて
微小な時間単位で処理が繰り返されます。
SetTimer関数の戻り値はタイマーIDと呼ばれる識別子で、
KillTimer関数のnIDEventに指定することでタイマー処理を終了できます。
サンプルでは、ブックのBeforeCloseイベントプロシージャの処理に保存確認の
ロジックを内包しているのは、BeforeCloseイペントプロシージャの処理によって
タイマー処理(イベント発行のための監視)を完了してから、Excelの機能によって
保存確認が行われた場合にキャンセルすると、イベントをハンドルできない状態で
開かれたままになるからです。
さて(3)のコードの最後で定義しているRowsInsertEventClassプロパティの内容
はまったく難しいものではありません。モジュールレベル変数
[my RowsInsertE vent Class]が「Nothing｣であれば新たにインスタンスを生成
してから、その参照を返すプロパティです。
しかし１つだけ重要なことがあります。
このようにして外部にclsRowsInsertEventオブジェクトヘの参照を返す(公開する)
場合には、clsRowsInsertEventクラスのInstancingがデフォルトのPrivate]のまま
ではいけないからです。
このような場合には、あらかじめ[プロパティ]ウインドウを使ってInstancingを
[PublicNotCreatable]に設定しておきます。
ポイント
タイマー処理中に実行時エラーが発生すると、Excelが即座に落ちてしまいます。
万が一にもそのようなことがないように、絶対に実行時エラーが発生しない処理
でない限り、必ずOn Error Resume Nextステートメントを付けるようにしましょう。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
あちらでβが、質問だけして、レスしなかったので、見切りをつけてこちらに投稿されたんですね。 こちらにも、このあたりに詳しい先生方がたくさんおられますから、早晩、参考になるレスがでてくると思います。
興味深いテーマですので、私も、識者からの回答を待ちながら、自分でも考えてみようと思いますが まずは、説明文を一生懸命読むところからですので、考える前に へこたれるかも。
いずれにしても 挿入行の頭に ★ をつけることができればいいのですよね。
## まぎらわしいので、あちらは コメント入れて、クローズ扱いにしておかれたらいかがでしょう。
（β） 2016/03/27(日) 15:51
とりあえず、回答できるできないは別にして、井川さんのコードを移植したブックを作成しましたが ThisWorkbookモジュールの
Public Property Get RowsInsertEventClass() As clsRowsInsertEvent
この Public 指定、そちらでは問題なく記述できますか？（コンパイラーから何もいってきませんか？）
（β） 2016/03/27(日) 16:06
↑のような状況ですので、井川さんのコードを試すこともできていませんし、コード全体を読んでもいないのですが このコードに基づく回答は、いずれ識者の方々からアップされると思います。
それとは、全く異なる方向ですが、行挿入ということだけを認識するなら、通常のシートモジュールのChangeイベントを使って以下で把握できませんか？
１．まず Workbook_Open あたりで 対象シートの 最終行 つまり そのシート.Range("A1",そのシート.UsedRange).Rows.Count を保持。
２．対象シートのイベントで、Range("A1",UsedRange).Rows.Count が、保持した最終行より大きければ挿入が行われたと認識。
（複数ブロック選択した複数挿入の場合も、１つずつ Changeイベントが発生しますので、Target の Areas は 常に 1つだと思います）
３．Target.Columns.Count が Columns.Count に等しければ、行挿入。 （セル挿入の場合はここではじかれます）
４．こうして行挿入だと判定された場合は Target.Columns(1).Value に "★" を記入。
５．行挿入だったか否かにかかわらず、かならず、現時点の最終行数で保持した値を置換。
ただし、値のある行をコピーして、シート末尾にペーストすると、行挿入ではありませんが、この処理では行挿入とみなされます。
どこかに抜けがあるかもしれませんが。
●Thisworkbook モジュール
Public MAXROW As Long
Private Sub Workbook_Open()
With Sheets("Sheet1") '★対象シート
MAXROW = .Range("A1", .UsedRange).Rows.Count
End With
End Sub
●シートモジュール
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sv As Long
sv = Range("A1", UsedRange).Rows.Count
If sv > ThisWorkbook.MAXROW Then
If Target.Columns.Count = Columns.Count Then
Application.EnableEvents = False
Target.Columns(1).Value = "★"
Application.EnableEvents = True
End If
ThisWorkbook.MAXROW = sv
End If
End Sub
（β） 2016/03/27(日) 16:48
ご指摘有難うございます。
今会社で詳しくコメントを詳しくみることができません。
コメント頂いた件、後ほど対応いたします。
有難うございます。
（かず） 2016/03/27(日) 17:13
このコードではE列に挿入判定用の連番が入っていてそれで行の挿入や行全体のコピーの判定を行っています。
仮にB列は全て埋まっているとの前提でB列で最終行の取得を行っています。
後うっかり数十万行の挿入や削除を行うと、オーバーフローするのでエラーチェック入れてます。
Private Sub Worksheet_Change(ByVal Target As Range) Dim row1 As Long Dim row2 As Long Dim col1 As Long Dim col2 As Long Dim i As Long
On Error GoTo err1
Application.EnableEvents = False
'選択範囲取得
row1 = Selection(1).Row
row2 = Selection(Selection.Count).Row
col1 = Selection(1).Column
col2 = Selection(Selection.Count).Column
i = Range("B" & Rows.Count).End(xlUp).Row
'キャンセル判定
'行全体が範囲指定されているか
If col1 > 1 Then GoTo Last_Step
If col2 < Columns.Count Then GoTo Last_Step
'1行目は対象外
If row1 = 1 Then GoTo Last_Step
'データ行を超えると対象外
If row2 > i Then GoTo Last_Step
'連番が重複しない時は対象外
If Cells(row1, "E").Value <> "" Then
If WorksheetFunction.CountIf(Range("E:E"), Cells(row1, "E")) = 1 Then
GoTo Last_Step
Else
'末尾行なら対象外
If Cells(row2 + 1, "E").Value = "" Then
GoTo Last_Step
End If
End If
End If
'行の挿入か行全体のコピーの時は★
Range(Cells(row1, "A"), Cells(row2, "A")).Value = "★"
Last_Step:
'連番を振り直す
Range("E2").Value = 1
Range("E3").Value = 2
Range("E2:E3").AutoFill Destination:=Range("E2:E" & i)
Application.EnableEvents = True
Exit Sub
err1:
MsgBox "エラーが発生しました!" & vbLf & _
"エラーNo. ： " & Err.Number & vbLf & _
"エラーの種類： " & Err.Description
Application.EnableEvents = True
End Sub
（sy） 2016/03/27(日) 18:04
アップしたコードをつらつら眺めて・・・
自分で
>>５．行挿入だったか否かにかかわらず、かならず、現時点の最終行数で保持した値を置換。
こう書いておきながらコードでは、行挿入の場合にのみ更新していました。
ThisWorkbook.MAXROW = sv
これを、最後の End If の下にもってきてください。
（β） 2016/03/27(日) 20:33
アップしたコード、対象シートを限定しましたが、もしかしたら、全シート対象でしたか？
もし、そうであれば、アップしたシートモジュールのコードを消去し、ThisWorkbookモジュールを以下で置き換えてください。
Dim MAXROW As Variant
Private Sub Workbook_Open()
Dim sh As Worksheet
ReDim MAXROW(1 To ThisWorkbook.Worksheets.Count)
For Each sh In ThisWorkbook.Worksheets
With Sheets(sh.Index) '各シート
MAXROW(sh.Index) = .Range("A1", .UsedRange).Rows.Count
End With
Next
End Sub
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Dim sv As Long
sv = sh.Range("A1", sh.UsedRange).Rows.Count
If sv > MAXROW(sh.Index) Then
If Target.Columns.Count = Columns.Count Then
Application.EnableEvents = False
Target.Columns(1).Value = "★"
Application.EnableEvents = True
End If
End If
MAXROW(sh.Index) = sv
End Sub
（β） 2016/03/28(月) 09:21
にもマルチポスト。 （マルチーズ） 2016/03/28(月) 10:49
教えて頂いたコード
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sv As Long
sv = Range("A1", UsedRange).Rows.Count
If sv > ThisWorkbook.MAXROW Then
If Target.Columns.Count = Columns.Count Then
Application.EnableEvents = False
Target.Columns(1).Value = "★"
Application.EnableEvents = True
End If
End If
ThisWorkbook.MAXROW = sv
End Sub
によって行の挿入時に１列目に★マークが付けられることを確認できました。
ただ２つ課題が残ります
１つ目はこの方法では、UNDO またはCtrl + Zが効かなくなってしまいます。
どうしてUNDOに影響が出るのか理解できていません
２つ目は、私の投稿の冒頭に書いた 作業列３ シーケンシャルNoについて
既存の行に対する変更では シーケンシャルNoは変わらず。
行の挿入＝ここではコピーして挿入の場合、コピー元のシーケンシャルNoは不変。
コピー先のシケーンシャルNoは枝番やタイムスタンプなどで、コピー元の
シーケンシャルNoと区別がつくようにしたいです
お気づきの点があれば教えてください
（かず） 2016/03/29(火) 04:20
>>２つ目は、私の投稿の冒頭に書いた 作業列３ ・・・・・
はい。何もしてませんから。
単に、行挿入を把握するために、大がかりな井川さんのコードの構造をとらなくても、できるのではないですかという 提案ですから。
この仕組みでＯＫであれば、あとは、アプリ要件として、追加すればよろしいので、まずは、仕組みの確認というか そちらの操作の結果、思った通りの挿入把握ができたかどうか、確かめてもらうフェーズです。
>>１つ目はこの方法では、UNDO またはCtrl + Zが効かなくなってしまいます。 >>どうしてUNDOに影響が出るのか理解できていません
これは、当たり前だと理解していますが。 マクロで書きこんだ行為は、操作からのアンドゥが効きませんので。
そちらのコードでは、実行後の Ctrl/z や アンドゥボタンのクリックが可能になっているのですか？ （コメントしましたように、まだ、コードも、説明文も読んでいませんので）
もし、マクロ処理した結果が、Ctrl/z や アンドゥボタン で元に戻るようになっているとすれば 私にとっては素晴らしすぎることなので、今から、そちらのコードを、目をさらにしておいかけてみます。
●いずれにしても、（β） 2016/03/27(日) 16:06 でコメントしたような事情で、アップされたコードを取り込むことすら できていません。そちらでは、問題ないのですかと質問したんですが、回答もいただけませんし。 また、質問箱のほうは、まぎらわしいので・・・とコメントさせていただいた点もＮＯ ＡＣＴＩＯＮ ですし 他の回答者さんからのコメントも入っているんですが、それらに対してもＮＯ ＲＥＰＬＹ ですねぇ。
私自身は、マルチポストは、個人的に好きじゃないということはありますが、それ以上に、 Ｑ／Ａ は、それぞれに、レスしながら、解決に向かって進んでいくものだと思っていますので 自分が興味のあるコードが投げかけられたときのみ、反応し、そのほかの書き込みは一切無視というスタイルは いただけません。
本トピのお手伝い継続しようか、やめようか、迷っているところです。
（β） 2016/03/29(火) 06:34
私が継続してお手伝いするかどうかは横におき。
本件、あまりにも大規模な井川さんのコードと、その解説が どどど〜〜んと目立っていて 皆さん、それを突き付けられるだけで、ちょっと面倒だから後回し・・・という気分になっておられるかもしれません。
で、それを使わなくても、そこはできるということですから、これ以降は、アプリ要件（データ要件）に絞って 進めていかれたらいかがですか。
それには、まず（あらためて）データ全体をどう管理したいのか、その要件で、既存行からの値付複数行挿入、 空白複数行の挿入、それぞれのデータを、どのようにしたいのか。（BL,BM,BN 列をどうしたいのかを中心に）
数式がどうとか、枝番にこれを付加して・・といったこと、これは、管理するために かず さんが考えられた仕組みですよね。 それはそれで、整理して説明いただくことが必要ですが、それを離れて、こんな管理ができるようにしたい ということが 重要だと思います。それによっては、まったく別の対処方法のアイデアがレスされるかもしれません。
現行の かず さんが考えられた手法にこだわってしまうと、解決が遠のく可能性もあります。
BL,BM,BN 列の役割も説明いただきたいですね。 そもそも、まず、かずさんが 既存の行に対して、これら３つの列に数式なり生打ちなりで値を埋めるのでしょうが その状態で 日時時刻って何の日時時刻なのか。もしかしたら全行同じ？ シーケンスは どのようなものか、どう入力しているのか。 枝番は数式のようですが、一応、この役割（というか意味）も説明してください。
そういった説明があれば、あれ？ 日時時刻っていらないんじゃない（わかりません。いるかもしれませんけど）とか =COUNTIF($BM$18:$BM709,BM18) この 709 って何だろうとか・・・
そういうポイントでも検討というか、考えることができます。
いずれにしても、これら3列の情報で何をどうしたいのかがわかれば、解決に近づいていくと思います。
（β） 2016/03/29(火) 20:14
言い訳になりますが、年度末で3/31 まで
まったく検討時間が取れず、検証や文章を
まとめる時間がありません。
今しばらく猶予をいただきたいと思います
（かず） 2016/03/30(水) 04:19
やりたいことは
１．挿入と更新があった時、★と▼を１列目につける
・現状このマークを拾って別のリストとのマージ処理を行っているが
記載ミスが多いのでイベントで拾って入力を補正したい・
・２つのリストの突き合わせとは 大元のマスターのリストを担当者
に配布し、それを突き合わせるという意味。 現状は 突き合わせる
キー情報はリスト中の情報を使っており、キーが変わる場合に対応する
ユーザには変更前のキー情報を備考欄に書いてもらっておりミス多い
・大元のリストの作業列にキー情報を書いて置き、それを配布し
そのリストで変更された行は、もとのキー情報を保持し、挿入行には
枝番のようにして挿入された行とわかるようにしたい
２、実装として
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sv As Long
Dim i As Long
Dim ins_Base As Long
Dim ins_Cnt As Long
Const N_Col As Long = 5
sv = Range("A1", UsedRange).Rows.Count
If sv > ThisWorkbook.MAXROW Then
If Target.Columns.Count = Columns.Count Then
Application.EnableEvents = False
Target.Columns(1).Value = "★"
ins_Cnt = Target.Rows.Count
ins_Base = Cells(Target(1).Row - 1, N_Col).Value
For i = 1 To ins_Cnt
Cells(Target(1).Row + i - 1, N_Col).Value = ins_Base & "_" & i
Next i
Application.EnableEvents = True
End If
End If
ThisWorkbook.MAXROW = sv
End Sub
を作成 （ただし挿入の部分だけ）
３．問題
・エクセルを開くのに時間が30秒くらいかかる
・何かの拍子に通所のUNDOが効かなくなる
・この方式が効かない時には通常の編集ができる形にしたい
・挿入行のキー情報を 8_1 の形で表現したが
挿入行に再度挿入された場合、
Cells(Target(1).Row + i - 1, N_Col).Value = ins_Base & "_" & i
の行でエラーとなるので修正要
現状の検討状況は以上です。お気づきの点があればアドバイスをいただければ
助かります
週末にはもう少しじっくり考えたいと思います
以上
（かず） 2016/04/01(金) 08:09
まず、
>>大元のリストの作業列にキー情報を書いて置き、それを配布し >>そのリストで変更された行は、もとのキー情報を保持し、挿入行には >>枝番のようにして挿入された行とわかるようにしたい
これは、あくまで、元の行がどれだったのかを把握するために かず さんが考えた仕掛けですね？ もし、それが別の方法でできれば、必ずしも 時刻、連番、枝番 の構成でなくてもいいのですね？
ということを質問しているんです。
もちろん、やっぱり 時刻、連番、枝番 での管理がいいね ということになるかもしれませんが。
次に、
集まったデータをもとに、かず さんが、何らかの作業をするわけですね。 で、その作業結果に基づき、また、【新しい大元リスト】として配布するんですよね。
その新しい大元リストを配布する際には、★や▼マークは、つけられたままですか？ それとも、いったんA列は空白にして配布するのですか？ また、削除行は 失注という文言のまま、新しく配布するのですか？ それとも、それは削除した上で配布するのですか？
それと、その際の、時刻や、連番や枝番は、新しくセットしなおして配布するのですよね？
問題 としてあげられた項目については、今から読んでみますが、とりあえず。
>>・エクセルを開くのに時間が30秒くらいかかる
（β） 2016/03/27(日) 16:48 でアップした、1シート対応 ベースなのか、（β） 2016/03/28(月) 09:21 でアップした 全シート対応なのかわかりませんが （そちらの要件として、どちらなのかの回答もいただいていませんが？？） いずれにしても、提示した Workbook_Open で30秒もかかるはずはありません。
このブックから Workbook_Open を消したものを別名で保存し、それを開いてみてください。 それでも、同じように時間がかかるとすれば、これは Workbook_Open処理の問題ではなく 単純に、このブックが、開くのに時間がかかるブックだということです。
>>何かの拍子に通所のUNDOが効かなくなる
マクロ処理結果は、UNDO対象にはならない、これはエクセルの仕様だと、申し上げたことは理解された上での発言ですか？ また、そちらのコードでは、マクロ処理の結果の戻しも、UNDOボタンで、できているのですか？ と質問しているのに その回答がいただけないのはなぜですか？
>>この方式が効かない時には通常の編集ができる形にしたい
この方式とは？ 通常の編集とは？
>>の行でエラーとなるので修正要
エラーの調査には、エラー番号とエラーメッセージ文言が必要です。それらが何だったのか、連絡願います。
（β） 2016/04/01(金) 08:56
ちょっと テーマからはずれますが参考として。
「エクセル 開く時間が長い」あたりで検索すると、様々な原因の説明がでてきます。 膨大な行数で、しかも数式がどっさりと埋め込まれている なんてのもその理由ですね。 特に TODAY() といった「揮発性関数」が、たくさん埋め込まれているなんてのも典型的な理由でしょうね。
数式を入れなくてもいいところは値でシートを作成しておく といったことは、当然、検討するポイントでしょうね。
また、ブックそのものがおかしくなってしまっている というケースも多いですね。 検索で出てきた情報で かず さんのブックに当てはまるところがないか、調査してみられたらよろしいかと。
（β） 2016/04/01(金) 09:08
もう1点、教えてください。
今、この運用を始める、最初の時点だとします。 つまり、配布して、★や▼がつけられて、回収して・・・ということの前の 本当に、今日からこの仕組みで運用しようというタイミングです。
このとき、なにかしらのデータを一覧にして、各担当に配布しますよね。 その際に かず さんは、BL,BM,BN 列 に 日時、連番、枝番をセットして配布するわけですよね。
・この時の 日時 は、関数ですか？ それとも手打ちですか？ ・連番は 1,2,3,・・・・ですよね。何か順番のルールはありますか？ それとも、無条件に最初の行が 1、次の行が2、・・・ という連番ですか？ ・枝番は 空白 ですね？
で、これとともに、すでに質問していますが、回収して、なんらかの作業をしたうえで、次回、配布する際には この大元のリストの BL,BM,BN 列 を、どのようにして配布されるのかも回答お願いしますね。
（β） 2016/04/01(金) 21:20
回答遅くなり申し訳ありません。あまり時間がなく十分な回答になっていないかもし
れませんが取り急ぎ報告させていただきます
まず4/1 9:08の質問から
Ｑ１ エクセルを開くのに時間がかかったのは事実なのですが、
http://blog.eset-smart-security.jp/2015/07/excelexcel.html
などを参考にエクセルの設定ファイルの修復をしてみると大分改善されました。
他に思いつく理由としては
・PC起動直後に操作したためで、他のプロセスと競合したのかもしれません。
・揮発性関数は使っていないかの点は、Now()とかは使っていないが
Row()を項番の列で使っていますが、サンプル表で20行位しかなせいかもし
れませんが、今現在は遅くなくなっています。
Ｑ２
>>１つ目はこの方法では、UNDO またはCtrl + Zが効かなくなってしまいます。 >>どうしてUNDOに影響が出るのか理解できていません
これは、当たり前だと理解していますが。 マクロで書きこんだ行為は、操作からのアンドゥが効きませんので。
ここは見落としていました。申訳ありません。 そもそもマクロで行った処理はUNDOできなものということは聞いたことが ありました。 舌足らずでしたが、いいたかたのはマクロの狙いは、行の 挿入や変更した場合に★や▼をつけるのを忘れるののを防ぐことなのですが、 利用者によっては自分の好きな入力方法を選びたい、入力ミスは目でチェック すればよいというひ人や、私のマクロが不具合でエラーとなった場合には マクロは停止して、マクロの影響をうけない方法で入力する方法も残して くということをUNDO と言っていました。 今考えているマクロは、行の挿入操作ではない操作、単一セルの更新などは UNDOもできるので、利用者にちゃんと伝えることが必要と理解しました。 Ｑ３
このとき、なにかしらのデータを一覧にして、各担当に配布しますよね。 その際に かず さんは、BL,BM,BN 列 に 日時、連番、枝番をセットして 配布するわけですよね。
この時の 日時 は、関数ですか？ それとも手打ちですか？
日時については、「コピーしたセルを挿入」という操作をされたら コピー元とコピー先を区別する方法が必要で、主なキーとなる 列だけで判断できないものを助ける意味で設定する必要から 考えたものでした。タイムスタンプなら目で見てわかるものという趣旨 です。実際には最初のセルに式を入れてドラッグするイメージです
ちなみに今現在のコードは
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sv As Long
Dim i As Long
Dim ins_Base As String ' 挿入の後に挿入になるとき 12_1、12_2 などになるので文字にする
Dim ins_Cnt As Long
Const N_Col As Long = 5
'If Not Application.Intersect(Target, Columns("B:E")) Is Nothing Then ' 行の範囲(A列 〜 E列）
' Cells(Target.Row, 1).Value = "▼"
'Else
'End If
sv = Range("A1", UsedRange).Rows.Count
If sv > ThisWorkbook.MAXROW Then
If Target.Columns.Count = Columns.Count Then
Application.EnableEvents = False
Target.Columns(1).Value = "★"
ins_Cnt = Target.Rows.Count
ins_Base = Cells(Target(1).Row - 1, N_Col).Value
For i = 1 To ins_Cnt
Cells(Target(1).Row + i - 1, N_Col).Value = ins_Base & "-" & i
Next i
Application.EnableEvents = True
End If
End If
ThisWorkbook.MAXROW = sv
End Sub
です。今は挿入だけを考慮しています。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sv As Long
Dim i As Long
Dim ins_Base As String ' 挿入の後に挿入になるとき 12_1、12_2 などになるので文字にする
Dim ins_Cnt As Long
Const N_Col As Long = 5
'If Not Application.Intersect(Target, Columns("B:E")) Is Nothing Then ' 行の範囲(A列 〜 E列）
' Cells(Target.Row, 1).Value = "▼"
'Else
'End If
sv = Range("A1", UsedRange).Rows.Count
If sv > ThisWorkbook.MAXROW Then
If Target.Columns.Count = Columns.Count Then
Application.EnableEvents = False
Target.Columns(1).Value = "★"
ins_Cnt = Target.Rows.Count
ins_Base = Cells(Target(1).Row - 1, N_Col).Value
For i = 1 To ins_Cnt
Cells(Target(1).Row + i - 1, N_Col).Value = ins_Base & "-" & i
Next i
Application.EnableEvents = True
End If
End If
ThisWorkbook.MAXROW = sv
End Sub
これを使うと挿入に際して
１/A 項目 ランク ｼｰｹﾝｽNo(BM列をなぞらえたもの)
16 A 14 ★ 17 A 14-1 ★ 18 B 14-2 ★ 19 A 14-3 ★ 20 B 14-3-1 ★ 21 A 14-3-2 ★ 22 A 14-4
のように出力できました. 配布前に、マクロで採番し 配布先で修正や更新を行われて、そこには▼や★がつけられて 返信されてきます
とりあえず 今はシーケンスNoは 8-1-1の形です
比較が容易になるように 8.11などの数値に表現できれば単純化できるのではと
思います。お気づきの点があればコメント願います。
（かず） 2016/04/06(水) 04:54
レス遅れごめんなさい。
なんとなくわかった部分もありますが、もう少し質問させてください。
１．まず、各担当に配布する大元リストでは、もう ★も▼もなく、追加された行という識別も不要で 単純に、そこに100行あるとすれば、それぞれの行は、独立した1件1行で、その識別番号をどう持たせるかは別にして 毎回、先頭から 1,2,3,4,・・・・,99,100 というイメージですね？
２．その100行ある大元リスト ですけど、配布前のデータそのものは、どのように作るんですか？ １）どこかのデータからコピペ？ ２）返送された各ブックを参照しながら かず さんが 手作業で切り貼りして整理したもの？ ３）あらためて、空白の大元リストに かず さんが 100行 手入力？
３．上記とも関連しますが、その 大元リスト って、このマクロが仕込まれたブック？ もし、そうであれば、上記作業 １）、２）、３） いずれであれ、そこで イベントが発生して、 配布先で発生する自動処理が実行されてしまいますが、そこは、どうしているんですか？
このあたり、具体的にどうされているのか、教えてください。
（β） 2016/04/06(水) 09:47
1. そうです。最初は普通に1から順に100まで
ユニークに振るものと思います。
2. 大元のリストは、各担当が提出したものです。
リストのやり取りは月に1回いかのように
しています。
先月各担当から集めた複数のリストを
私が現状のマクロで1つのリストにして
おり、そこには元々の各担当がつけた
▼や★マークが残ってます。当月に
それらのマークは全て私が消します。
これで大元のリストができあがります。
3. 考えていたマクロは、リストを
マクロ(-イベントハンドラ)付きExcelで配布
して、入力時のマークの記載漏れを
なくせばよい、と自分が編集する時のことを
考えていました。
ご指摘でその点の考慮不足を気付くことができ
ました。ありがとうございます。結果、
1)配布先には入力内容を
監視し、かつシーケンシャルNoを
保持するマクロをリスト本体と一体化
して配布する。
2)
回収したリストを私が編集して大元の
リストを作る時は、リストに含まれる
マクロは切っておきます。
そして現状マクロでは、1列目の▼や★を
見つけたら特定の列を複合キーとして
各担当のリストと大元のリストを突き合わせ
ていた部分は、シーケンシャルNoを使うよう
にできると思います。
本来は、シーケンシャル実際はシーケン
シャルではないので管理Noとでも呼ぶ
ことにします。
この管理Noがあれば、理屈上は★とか
▼は不要でしょうが、実際にはマークの
方が利用者は簡単に扱えると思います。
ここまでで現実的でない部分あります
でしょうか。
今度は行の部分的な更新と行全体の挿入
をイベントハンドラのTarget引数で
正しく判断するロジックと、管理Noの
持ち方、処理を検討したいと思います。
お気付きの点がありましたら
アドバイス頂きたくよろしくお願いします。
（かず） 2016/04/06(水) 17:51
処理のシナリオに関する提案というか、このようにしたらいいかなと思っています。
１．各担当サイドの作業を考えると、やはり、大元リストのあるブックに今回のマクロを仕込みます。 ２．げん さんサイドの作業としては １）回収した担当者ブック（マクロ付）を１つのフォルダにいれておいて（したがってブック名は何らかの工夫をしてユニークにしておく必要がありますが） ２）これらを統合して、１つのマクロなしブックを作成。（これは、別途の新規マクロで実行） ３）このブックはマクロなしですから げんさんが、好きなように、次回用大元リストとして変更可能です。 ４）で、げん さんの手元にある マクロ付大元リストブックで、３）で、できあがったシートを マクロ付大元リストブックのシートに上書き。 これも新規マクロで、仕掛けてあるイベントが発生しないような処理をします。 ５）このときに、新たな連番を振ります。 ６）これを配布します。 ７）次の作業としては 各担当再度での作業の結果を回収し １）からの作業の繰り返し。
このシナリオに同意いただければ、２） ４） の新規マクロと 現在取り掛かっている大元リストの担当者側イベント処理コード案をアップします。
（β） 2016/04/06(水) 22:25
かずです。(げんさんじゃないです） それはさておき。
４）の所。
私の手元には、担当者に配布する前の大元のイベントハンドラマクロの付いた大元リストブックがある と、これを前バージョンとする。
で、３）で、新しく皆から集めてできあがったシートを、この付大元リストブックの前バージョン
のシートに上書きすると。
これも新規マクロで、仕掛けてあるイベントが発生しないような処理をします。
なるほどそうか。そうですね。自分ひとりで頭の中で考えていて、このような順序だてた作業のイメージが
理解できていませんでした。納得です。
＞２） ４） の新規マクロと 現在取り掛かっている大元リストの担当者側イベント処理コード案
＞をアップします。
時間が許る範囲で結構です。 仕事の関係で中々時間がなくて、レスポンスが遅いかもしれませんが
ぜひ引続きお知恵をおかりしたいと思います。
（かず） 2016/04/07(木) 01:53
HN取り違え 大変失礼しました。
はい、私も、ちょっと バタバタしそうで、時間がかかると思います。
ところで、シナリオを頭の中でシミュレーションしていると、あれ？ と思ったところがあります。
・大元リストに 1〜100 のデータがあったとします。 ・想像ですけど、たとえばAさん用には 1〜40、Bさん用には 41〜60、Cさん用には 61〜100 といったようなデータなんだろうと思います。 （かならずしも、個人別に連続しているわけではないとは思いますが） ・で、このリストを、それぞれの担当者分のみにして、送付するのか、そのまま 他の人のデータも一緒に送付するのかわかりませんが おそらく後者？ ・だとすると、回収したブックに、同じデータが、それぞれあるわけですね。 で、そのなかで、取り入れなければいけないデータは、Aさん用のデータなら Aさんから送られてきたブックのデータですね。
これを かず さんが、目で見ながら、削除などをしていく運用であればいいのですが・・・ たとえば、Bさんから送られたブックからの抽出は Bさんのデータだけという判定ができれば、回収して統合するときに便利かなと。
そういった個人別の識別ができる情報って、どこかにありますかね？ もしあれば、配布時に大元リストから Aさん用、Bさん用、Cさん用のリストに自動分解してブックを作成することもできます。
（β） 2016/04/07(木) 09:21
・で、このリストを、それぞれの担当者分のみにして、送付するのか、そのまま 他の人のデータも一緒に送付するのかわかりませんがおそらく後者？
はい、そのまま送っています。本当は個人別に送ってあげたほうが良いのですが 面倒なのでしていません。 理由の一つは、このリストはプロジェクトいわゆる案件 売り上や粗利とそれを 構成する商品や担当者、その案件の進捗度などを記録しています。 リストを返信するのはプロジェクトの全体に責任を持つ担当者なのでリストは プロジェクト単位で見たほうが理解しやすいので、同じプロジェクトを構成する 商品やシステム構築の進捗度が不ぞろいになっているのは本来おかしいなどと としてみています。
そんなわけで一般的、全体としてはこのリストにはある
担当者があちこちに出てきていて１〜３０行は Aさんのかかわる案件、３１〜６０
はBさんがかかわる案件とはなっていないです
βさんがいみじくも指摘していただいていますが、Aさんから帰ってくるデータ
はＡさんの関わる案件のデータのことが多いが、Ｂさんの関わるデータも含まれて
います。
イメージは
印 項番 プロジェクト名 商品 担当者 売上 製造原価 利益 ・・・
▼ １ ？？再構築 ・・・ Ａ ・・・
★ ２ ？？再構築 なし Ｂ ・・・
▼ ３ ？？再構築 ・・・ Ａ ・・・
▼ ４ ？？再構築 ・・・ Ａ ・・・
という感じです
これに対しＢさんからの返信されたデータは 、基本的には Ａさんのテリトリ
の行には触っていない ５行目から１０行目まではＢさんの案件ですが、
Ｂさんからの返信にもＡさん担当の案件が混ざっています
混ざっているのはいいのですが、Ａさんの返信の中の１０行目が Ｂさん担当
で、その10行目をＢさんの方でも変更しようとしていることがあり、そうなると
目でどちらの修正を先にやるかは、私が判断することになります。それがとても
面倒です。
各担当は何等かのチームに所属するので、そのチームのリーダがチーム全員の分を
まとめて送ってきます。現状は 担当がAutoFilter で担当者とメンバをフィルタ
情報をAutoFilterのOnjectから取り込み、出力先のファイルにそれを移植して
います
入力元と出力先のウェイドウを 上下に並べて、上の例の Aさん Bさんが同じ
くBさんの10行目などの修正の重複は目で見ており、その点も注意してチェック
しています。 取り急ぎ今日はここまでです
以上
（かず） 2016/04/12(火) 03:05
以下のようなことを考えています。
ブックの構成
Ａ．かずさん作業用の 大元リスト と ワーニングリスト（後述） があるマクロブック。 Ｂ．担当者に配布する イベント処理付マクロブック雛形。
シナリオ（各担当に 各担当以外のデータも送るという場合）
１．各担当から返送されたブック（それぞれ 固定ブック名_担当.xlsm という名前。）が1つの回収用フォルダに格納されている。 ２．Ａブックで、フォルダの各担当ブックから、【各担当分のみ】を抽出して、大元リストを入れ替え。 ３．この時、各担当ブックで担当分以外に ★や▼や 失注 がマークされているものを、別途、ワーニングリストに抽出。 ４．かずさんが、Ａブックの大元リストで、作業。最終的には 1件1行の新大元リストが完成。 ５．Ａブック内の 管理番号セットマクロを実行。 ６．Ａブック内の 配布マクロを実行。 大元リストに存在する担当の数だけ 固定ブック名_担当.xlsm という名前で １つの配布用フォルダにブックが作成される。 ７．これを 各担当に送付 ８．１．に戻る
なお、各担当には各担当分のみ送るという仕様にすることも可能。 その場合は、２．の抽出が少し簡単になる。また、他の担当分はないので、３．が不要。 また、６．では、各担当分だけのそれぞれのブックが出来上がる。
いずれがいいですか？
（β） 2016/04/12(火) 09:48
のような感じのリストです
問題の一つは、プロジェクトのリーダはそのプロジェクト全体の売上や原価の配分
を任されていて、売上や原価を融通しあいます。 なので上の例では山田Ｌ(リーダ)
はＡ社プロジェクト全体及び高橋さん鈴木さんの売上にも責任があり、リソース配分
や進捗具合を評価しながらリストをメンテしているので担当別にバラバラに見ないです
一般艇にいうと、プロジェクトの規模によって担当者一人で完結している場合と中規模
以上のプロジェクトで担当別にバラバラにすると使いづらくなってしまいます。
なので、２、３、６の担当者と別に整理するところ別途ワーニングリストなど考えたい
と思っています
ところで、私が扱っているリストのサンプルの画像や今利用しているマクロの一部を
見て頂いた方が、この掲示板で例示するより正確ではと思っております
ここにメールアドレスを書いていいのか不明ですが、細かい情報お伝えする方法
について教えてください
したら連携の
（かず） 2016/04/13(水) 03:39
リストの大まかなイメージについては了解しました。 であれば、分解せず、すべてを送付、また各人からすべてを回収して かずさんが それらを手作業で一本化せざるを得ないかもしれませんね。 一本化の際の作業支援として ワーニングリスト 等を考えるということになりますかね。 リーダが 他のメンバのデータも変更するということもありえるわけですか？ また、一般のメンバが他のメンバのデータ変更を行うこともありえるのでしょうか？
まぁ、それは、後のこととしたほうがいいですね。
ところで、確かにサンプルやコード、直接現物を見たほうが、理解が深まることは明白ですが これをメール等でやりとりすると、なんだか、業務を請け負っているような気持ちというか、プレッシャーを感じてしまいます。 ちょっと掲示板の意義とも離れてしまうような気がして、βは、好みません。
よく、皆さんが【補助的】につかっておられるのが、フリーのファイルアップロード、ダウンロードサービスですね。
いずれにしても、私が、いろいろ申し上げたシナリオは、いったん忘れ、本題に戻り、 各担当ベースで、メンテした結果の 管理番号の設定部分を仕上げましょう。
（β） 2016/04/13(水) 06:31
リーダが 他のメンバのデータも変更するということもありえるわけですか？ また、一般のメンバが他のメンバのデータ変更を行うこともありえるのでしょうか？
はい。リーダが忙しいので年上の場合が多いので、年下のメンバにデータ入力作業自体 はやらせて、自分はチェックするという分担のチームもあります
これをメール等でやりとりすると、なんだか、業務を請け負っているような気持ち というか、プレッシャーを感じてしまいます。ちょっと掲示板の意義とも離れて しまうような気がして、βは、好みません。
了解です。
メンテした結果の 管理番号の設定部分を仕上げましょう。
はい。まずはこの点を考えてみます。 （かず） 2016/04/14(木) 01:38
いろいろシナリオを書きましたが、コメントしたように、いったん、それらは忘れて。
本件を、 １．配布前に、あとあと参照できるように、管理番号を振る。 ２．配布先で、行挿入した際に、コピー元の管理番号に枝番をつける
この2点に絞って、整理してみました。
１．でいうと、必要な列は 2列（BL,BM)のみでいいと思っています。 配布時は BL列が、上から順に 1,2,3,4,・・・・・ BM列は空白。
２．については
１）コピー元が 100 と 空白 というものなら BL列が100、BM列 が _1 ,_2 ,_3,・・・・ ２）コピー元が 100 と _3 なら BL列が100、BM列が _3_1 ,_3_2,_3_3,・・・・ ３）コピー元が 100 と_5_10 なら BL列が100、BM列が _5_10_1 ,_5_10_2 ,_5_10_3,・・・・ ４）挿入されたものが 空白行 なら BL列は、その時点の BL列内の最大数 + 1 、BM列は空白。
こうしておけば（ＶＢＡとしても、あとあとのかずさんの参照作業としても）制御できると考えています。
作業想定。
回収した各ブックのシートの内容をまず１つのシートにまとめられると思います。 シートそのものをコピーするとシートモジュールもくっついてきますので、新規ブックのシートに 回収した各ブックのシートのセルを、値コピーして貼り付けていき、通常のブックとして保存してもらいます。 （このあたりも必要ならマクロ化できますが、とりあえずは手作業で）
この通常ブックの大元シートを、かずさんのほうで、削除したり変更したり、あるいは別のデータから取り込んで追加するなり 必要な作業を行い、次回配布用の大元リストができあがるという想定です。
で、マクロブックの大元リストシートを、この通常ブックで出来上がった大元シートで書き換えます。 これは、マクロブックと通常ブックの両方が開かれている状態で、マクロブック側の "配布前作業" で実行します。 通常ブックのできあがったシートの任意のセルを選択して取り込みます。
このマクロブックを各担当に配布します。
●ThisWorkbookモジュール
Option Explicit
Private Sub Workbook_Open()
With Sheets(SHNAME)
MAXROW = .Range("A1", .UsedRange).Rows.Count
End With
setDIC
End Sub
●標準モジュール
Option Explicit
Public Const SHNAME = "Sheet1" '★対象シート名 Public MAXROW As Long Dim DIC As Object
Sub 配布前作業()
Dim shW As Worksheet
Dim c As Range
Application.EnableEvents = False
On Error Resume Next
Set c = Application.InputBox("入れ替えるべきブックのシートの任意のセルを選択してください", Type:=8)
On Error GoTo 0
If Not c Is Nothing Then
Set shW = c.Parent
With ThisWorkbook.Sheets(SHNAME)
.UsedRange.ClearContents
shW.Range("A1", shW.UsedRange).Copy .Range("A1")
With .Range("A1", .UsedRange)
.Columns("BL:BM").ClearContents
.Range("BL1:BM1").Value = Array("管理番号", "枝番")
.Range("BL2").Value = 1
.Columns("BL").Resize(.Rows.Count - 1).Offset(1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
End With
.Parent.Activate
End With
End If
Application.EnableEvents = True
End Sub
Sub setDIC()
Dim c As Range
Dim k As String
Dim n As Long
Dim w As Variant
Dim tmp As Variant
Set DIC = CreateObject("Scripting.Dictionary")
With Sheets(SHNAME)
For Each c In .Range("BL2", .Range("BL" & .Rows.Count).End(xlUp))
If Not IsEmpty(c.Offset(, 1)) Then
w = Split(c.Offset(, 1), "_")
n = w(UBound(w))
ReDim Preserve w(LBound(w) To UBound(w) - 1)
k = c.Value & Join(w, "_")
If n > DIC(k) Then DIC(k) = n
End If
Next
End With
tmp = DIC.items
End Sub
Sub 管理番号セット(Target As Range)
Dim c As Range
Dim k As String
Dim n As Long
If DIC Is Nothing Then setDIC 'プロジェクトリセット等の障害対応。念のため。
For Each c In Target.Columns("BL").Cells
k = c.EntireRow.Columns("BL") & c.EntireRow.Columns("BM")
If IsEmpty(c.EntireRow.Columns("BL")) Then
c.EntireRow.Columns("BL").Value = WorksheetFunction.Max(Target.Parent.Columns("BL")) + 1
c.EntireRow.Columns("BM").ClearContents '念のため
Else
DIC(k) = DIC(k) + 1
c.EntireRow.Columns("BM").Value = c.EntireRow.Columns("BM") & "_" & DIC(k)
End If
Next
End Sub
●シートモジュール
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sv As Long
sv = Range("A1", UsedRange).Rows.Count
If sv > MAXROW Then
If Target.Columns.Count = Columns.Count Then
Application.EnableEvents = False
Target.Columns(1).Value = "★"
管理番号セット Target
Application.EnableEvents = True
End If
MAXROW = sv
End If
End Sub
（β） 2016/04/16(土) 15:14
あぁ、もう１つ。
>> 利用者によっては自分の好きな入力方法を選びたい、入力ミスは目でチェック >>すればよいというひ人や、私のマクロが不具合でエラーとなった場合には >>マクロは停止して、マクロの影響をうけない方法で入力する方法も残して
こういった要件がありましたね。
標準モジュールに
Sub 手作業だけ()
Application.EnableEvents = False
End Sub
こんなコードを用意しておいて、希望する人は、ブックを開いた後、これを実行。 このあと、エクセルを終了させるまでは、一切のイベントが発生せず、自動処理は行われません。
回収したブックのまとめ作業を、別ブックで と推奨しましたが、マクロブック直接処理で かずさんの作業前に、このマクロを実行して自動処理を止めてやるという方法も可能です。
（β） 2016/04/16(土) 15:43
アップ済みのコードに関する不具合等があれば、そこは引き続き対応しますが、基本的には本件からは撤収しようと思います。
以下は、所感というか、余談です。
・知恵袋のほうにあった回答、単純に、報告者側で、変更部分を色塗りすればいいのでは という意見、賛成ですね。 ・プロジェクト管理を、ツールを用いて行うことはもちろん悪いことではないのですが、ツールに縛られて 進捗管理のための作業負荷が増加すれば、本末転倒ですので。 ・βが現役時代、もちろん、大型プロジェクトに関しては、プロジェクト全体-->ブロック-->タスク といった開発ロットのレベルがあり まず、タスク内で、メンバがタスクリーダに報告。そこで、タスクチームとしての1枚の進捗報告ができる。 次に、タスクリーダがブロック長に対して報告。ブロック内進捗会議で確認されたものが、ブロックとしての1枚の進捗報告となる。 最終、ブロック長がプロジェクトマネージャーに報告。プロジェクト全体会議で確認されたものが1枚の進捗結果となる。
このように、下から上へ レベルごとに整理されてまとめられた【レポーティングの仕組み】で把握していくというのが やりやすいのかなと思っています。
プロジェクトマネージャの元に、末端の各担当レベルのWBSが届けられて、プロジェクトマネージャが、 これらの細かな項目を、あれはどう、これは、これと一緒だから・・とチェックしていくのはいかがなものかと 思ったりします。
（β） 2016/04/16(土) 16:55
コードをアップして頂き有難うございます。
今また会社におりまして、明日細かく見せて頂こうと
思っています。
Dictionary等のテクニックについて、理論は時々ネット
で見ていたのですが、実際の役にどうたてるか理解できて
いなかったので大変助かります。
プロジェクト管理自体に関するアドバイスあで頂き大変
有難うございます
取り急ぎ お礼まで
（かず） 2016/04/16(土) 19:07
Ｑ１
４）挿入されたものが 空白行 なら BL列は、
その時点の BL列内の最大数 + 1 、BM列は空白。
と前提されているので、当然ながら
仮に１００行があって２行目と３行目の間に、
コピーして挿入されるときＢＬ列は１０１番
ということで、ここは当方が別途修正する前提で
省略して書かれていると思えばいいですね？
Ｑ２ 管理番号セットプロシジャで 枝番 ＢＭ
列は コピーして挿入の場合 元のＢＬ列を
変更しないので、
BL BM
★ ・・・ 18 _1 コピー元
★ ・・・ 19 _1 コピー元 ★ ・・・ 18 _1_1 コピー先 ★ ・・・ 19 _1_1 コピー先
管理番号＆と枝番の扱いが難しくなってしまうので
見たままでは扱いにくいので修正しないといかない
と理解しました
Ｑ３ SetIDのプロシージャは当方の技術力不足ですが
理解しにくかったです。以下確認させてください
ここは管理番号の枝番が空白でない時、つまり
枝番の処理が先にされていて、先のdictionary
の状態を復元しているということですよね？
Ｑ４ Dictionary は
BL BM
★ ・・・18 _1_2 ・・・(a)
・・・
・・・
★ ・・・18 _1_1 ・・・(b)
のような挿入結果の場合
(a) Key を 18_1 として DIC(18_1) に 2を格納
(b) key を 18_1 として BM列に含む枝番数が２ ＝ DIC(18_1)なので
何もしない
というような処理で、キーとしている管理番号を持つひとつ下位の行数
を記憶している ということですね。
dictionary を使っている範囲は、コピーして挿入処理を行った行について。
Dicitionaryのkey が 枝番込みの管理番号で その管理番号に対する
枝番の数を データ として記憶すると理解しています
セルに枝番自体を書き出していくのではなくて、dicitionaryというメモリ
上の機構を使っているのは、セルにアクセスするより高速だからという観点
でしょうか？
Ｑ５ 管理番号セット プロシジャの冒頭のIf DIC Is Nothing Then setDIC
の注釈 のプロジェクトリセット とはどんな状況でしょうか？
お手数ですが理解できておらず教えて頂きたく。
Ｑ６ setDIC プロシジャの最後にある tmp = DIC.Items 意味について理解できて
いません。実機で確認の際、DictionaryのデータがVBEのウォッチ
ウィンドウでは参照できませんでした。tmpにコピーしている意図について
教えてください。
以上
（かず） 2016/04/18(月) 04:03
すいません。他にも少し確認させてください
更新の場合の処理は、Worksheet_Change プロシジャで イベント
プロシジャとしての戻り値 Targetの範囲で処理を分けて記述しないと
いけないと思っております
気にしているのは、更新の場合 イベントが発生するタイミングは
特定セルを修正してEnterキーが押された時だけなのでしょうか
イベントの起きるタイミングが今一つわかっておらず、挿入のイベント
で処理が行われる時と、特定セルのみの更新の時とを切り分けするのが
基本的な考え方でしょうか？
お気づきの点があれば教えてください。
以上
（かず） 2016/04/18(月) 04:44
メモしている間に追加質問がありましたが、まず最初のほうにコメントします。
まず、こちらのコメントがわかりにくいというところは、もちろん指摘いただければ、説明します。 また、当然ですが、申し上げたことが、実際には実現できていないということなら、バグですから ●●であるべきだが、■■になったと連絡もらえればデバッグの上、コード変更します。
以下、そちらの質問にコメントしていきますが、質問そのものが ↑の２つの、どちらなのかがわかりにくいものもあります。
『Ｑ１』
>> 仮に１００行があって２行目と３行目の間にコピーして挿入されるときＢＬ列は１０１番
いえ、【コピー】して挿入される場合は当然、コピー元の番号をベースにして採番します。 １０１になるのは、あくまで、単純な挿入、挿入結果が完全な空白行の場合のみです。
＊1 指摘されて気が付いたことがあります。後述します。
>> ということで、ここは当方が別途修正する前提で 省略して書かれていると思えばいいですね？
意味がよくわかりません。そちらで、別途修正する とは？ こちらが申し上げたことが、実行してみたら違っていたということですか？
『Ｑ２』
>> 管理番号＆と枝番の扱いが難しくなってしまうので 見たままでは扱いにくいので修正しないといかないと理解しました
管理番号の持ち方は、あくまでこちらの【提案】ですから、かずさんのほうで、いかようにも変更されてもＯＫですよ。 こちらの提案は BL列連番とBM列枝番を連結したものが、その行の管理番号（背番号）というものですので。
『Ｑ３』、『Ｑ５』
>> ここは管理番号の枝番が空白でない時、つまり枝番の処理が先にされていて、先のdictionaryの状態を復元しているということですよね？
復元といいましょうか、ブックが開かれたときの管理番号+枝番の状態を読みこんでいます。 で、枝番がふられるたびに 1 アップ。カウンターのようなものです。
>>Ｑ５ プロシジャの冒頭のIf DIC Is Nothing Then setDIC の注釈 のプロジェクトリセット とはどんな状況でしょうか？
ここは、【念のため】というか、ほんらい ないほうがよろしかったかなと思うコードです。 この DIC は ブックが開かれたときに値が書きこまれ、その後、カウントアップが書きこまれているわけですが たとえば、バグがあって実行時エラーになる。デバッグ画面で、いろいろ調べたあと、ＶＢＥメニューの実行->リセットで おわらせますね。このとき【プロジェクトがリセット】されます。 あるいは（本番ではありえないでしょうけど）ブックを読みこんだ後、マクロコードに手を加えると【プロジェクトがリセット】されます。 また、きわめて特殊なコードを使うと（今回は使っていませんが）【プロジェクトがリセット】されます。 【プロジェクトのリセット】状態というのは、ブックが読みこまれた時点に戻る、様々な変数がすべて初期化される状態です。 なので、そのまま継続すると DIC が、からっぽ（Nothing) で参照しようとするとエラーになります。 ですから、そういう状態なら、ブックを開いたときに行った値の生成をやりなおそうというもくろみです。
＊2 指摘されて気が付いたことがあります。後述します。
『Ｑ４』
Dictionaryという基本機能にかかわるもので、なかなか、説明しづらいところですね。
>>キーとしている管理番号を持つひとつ下位の行数を記憶している ということですね。
う〜ん・・ そうではなく、BL列連番+枝番 という単位の背番号に対して、今、枝番が何番まで振られているかという辞書です。
>>dictionary を使っている範囲は、コピーして挿入処理を行った行について。
はい、そうですね。 新規挿入されて新しい連番が振られたものに関しては、それがコピー元として参照されたときに、このDictionaryで扱われます。
>>セルに枝番自体を書き出していくのではなくて、dicitionaryというメモリ上の機構を使っているのは、セルにアクセスするより高速だからという観点
最初は、管理シートをつくり、そこに値を書いておいて管理しようとも思いました。 （そうしておけば【プロジェクトリセット】の心配も不要になりますので） ただ、その場合、たとえば 管理番号 ABC を参照するのに、MATCH なり Find なり、何かしらの 【検索】が必要になります。 Dictionaryにしておけば、ABC を与えるだけで、ピンポイントの直接参照ができますので、採用しました。 （もちろん、操作者の操作に連動したイベント処理ということで、高速 ということも意識していますが）
『Ｑ６』
>>tmpにコピーしている意図について教えてください。
ごめんなさい。テスト中に、生成したDictionary の Item の内容を確認するために、書いたコードを消すのを忘れていました。 （Dictionary の Key は ローカルウィンドウで確認できるのですが、Item の内容は表示されないので）
消しておいて下さい。
＊1 アップした処理では、新規挿入、あるいはコピー挿入されたものを対象にしているはずなんですが、仮に 100行あって 50行目をコピーして、60行目に、上書きペーストした場合、行挿入ではないので、処理対象外になります。 そうすると、BL列、BM列が全く同じものが複数できてしまうことになります。 ある意味、これは【バグ】ですから、ちょっと考えてみます。 （この場合、101行目にペーストすると挿入とみなしますが）
＊2 プロジェクトリセットで初期化されるのは Dictionary のみならず、 Public MAXROW As Long これも消えますね。 Workbook_Open から
With Sheets(SHNAME)
MAXROW = .Range("A1", .UsedRange).Rows.Count
End With
これを消して、setDic の最初にもっていってください。
（β） 2016/04/18(月) 05:42
まず、Changeイベントは、どのようなときにどのようなタイミングで発生するか。 これは、例を列挙するより、自分でいろいろ試してみるのがわかりやすいです。
新規ブックで、
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
End Sub
こんなコードを書いておいて、シート上で
・セルに値を入力 ・そのセルを選択して、右に あるいは 下にフィルコピー ・領域を選択して Ctrl/c --> 別の領域を選択して Ctrl/v ・領域を選択して Deleteキー ・行をコピーして 別の行にペースト（1行のケースと複数行のケース） ・行を単純に挿入（1行のケースと複数行のケース） ・行を削除（1行のケースと複数行のケース） ・列をコピーして 別の列にペースト（1列のケースと複数列のケース） ・列を単純に挿入（1列のケースと複数列のケース） ・列を削除（1列のケースと複数列のケース） ・セルに書式を設定（罫線であったり塗りつぶしであったり）
こんな操作をしてみてください。どういうタイミングで発生するのか（あるいは発生しないのか）、その時のTargetはどんなものなのかがわかると思います。
で、この結果からわかると思いますけど、行や列が挿入されたのかどうかというのは、そういったイベントがあればいいのですが Changeイベントしかないので、なかなかやっかいな判定になります。 今回のコードでは Target の列数がエクセルとしての最大列数、かつ、イベント前より、実際のデータの行数が増えているということを判断基準にしています。 これで充分かどうか、ちょっと不安な面もありますが。
（β） 2016/04/18(月) 06:02
操作の追加で。
・行を選択してDeleteキー（1行のケースと複数行のケース） ・列を選択してDeleteキー（1列のケースと複数列のケース） ・セル結合 ・セル結合解除
そのほか、思いつく操作があればやってみてください。
（β） 2016/04/18(月) 06:17
追伸です。
>> 管理番号＆と枝番の扱いが難しくなってしまうので 見たままでは扱いにくいので
もし、この意味が、行としての管理番号が BL 列 と BM列 に たとえば 3 と _2_5 といったようの分かれているので 扱いにくい（見た目は、くっついているのでわかると思いますが）ということであれば、当初、そちらで用意していた BN列が未使用ですので、BL列,BM列 とは別に、それらの値を 3_2_5 と連結した値を BN列に記入しておくことはできますので そういう要望であれば対応しますよ。
（β） 2016/04/18(月) 07:25
連投失礼。
当初の要件の内、挿入ではない単なる変更の場合に ▼をつけるという対応を忘れています。 必要なら、コード追加しますが、悩ましいのは
挿入 で ★ になった。 この ★ の行を 変更した。（空白挿入の場合は当然、その行のセルに入力しますので変更が発生します） この場合には ▼を付けず、★ のままにしておきたい？
できないことはないですが、ちょっとだけ面倒です。
（β） 2016/04/18(月) 08:05
βさん いろいろとお時間を割いてコメントして頂き 本当ににありがとうございます。恐縮しております
以下 インラインで説明補足させていただきます。
以下、そちらの質問にコメントしていきますが、質問そのものが ↑の２つの、どちらなのかがわかりにくいものもあります。
『Ｑ１』
仮に１００行があって２行目と３行目の間にコピーして挿入されるときＢＬ列は１０１番
いえ、【コピー】して挿入される場合は当然、コピー元の番号をベースにして採番します。 １０１になるのは、あくまで、単純な挿入、挿入結果が完全な空白行の場合のみです。
私の視点からは、２行目と３行目に挿入された行の管理番号が、前後の行の管理番号と 繋がらない最大番号になってしまうのは、不自然と思っています。
＊1 指摘されて気が付いたことがあります。後述します。
ということで、ここは当方が別途修正する前提で 省略して書かれていると思えばいいですね？
意味がよくわかりません。そちらで、別途修正する とは？ こちらが申し上げたことが、実行してみたら違っていたということですか？
わかりにくくてすいません。率直に言いますと
βさんの2016/04/16(土) 16:55 の投稿に
アップ済みのコードに関する不具合等があれば、そこは引き続き対応しますが、基本的には本件からは撤収しようと思います。
とありましたので、βさんは残課題のうち重要なものに限定して対応しようとされていると理解し、私が時間をかければ
何とかできそうなものは自分で考えようと思った次第です。
『Ｑ２』
管理番号＆と枝番の扱いが難しくなってしまうので 見たままでは扱いにくいので修正しないといかないと理解しました
管理番号の持ち方は、あくまでこちらの【提案】ですから、かずさんのほうで、いかようにも変更されてもＯＫですよ。
こちらの提案は BL列連番とBM列枝番を連結したものが、その行の管理番号（背番号）というものですので。
コピーして挿入を実機で動かすと
BL BM 注釈
★ ・・・ 18 _1 コピー元
★ ・・・ 19 _1 コピー元 ★ ・・・ 18 _1_1 コピー先 ★ ・・・ 19 _1_1 コピー先
インデントがおかしかったかかもしれませんが 18_1,19_1 そして 18_1_1 となるのは何を基準に並んでいるのか を説明できない＝扱いにくい と思いました。
各担当に配布したリストに更新や挿入があった場合、管理番号は
一定の規則に従って変更されて戻ることが必要で、それは戻った
管理番号、変更された管理番号によって、その管理番号を持った
行を、大元のリストと突き合わせて並び順も含めて適切にリスト
を反映するためです
そのため、単純挿入行の管理番号が、その時点の最大行であったり
コピーして挿入した行の管理番号のルールがわかりにくいものは適切で
ないと思っています
>> こちらの提案は BL列連番とBM列枝番を連結したものが、その行の管理番号（背番号）というものですので。
管理番号が BL列とBM列をつないだものになっているのは、それほど
問題ではないと思いますが、管理番号の付与ルールが不明なことを気にしています
『Ｑ３』、『Ｑ５』
>> ここは管理番号の枝番が空白でない時、つまり枝番の処理が先にされていて、先のdictionaryの状態を復元しているということですよね？
復元といいましょうか、ブックが開かれたときの管理番号+枝番の状態を読みこんでいます。 で、枝番がふられるたびに 1 アップ。カウンターのようなものです。
>>Ｑ５ プロシジャの冒頭のIf DIC Is Nothing Then setDIC の注釈 のプロジェクトリセット とはどんな状況でしょうか？
ここは、【念のため】というか、ほんらい ないほうがよろしかったかなと思うコードです。 この DIC は ブックが開かれたときに値が書きこまれ、その後、カウントアップが書きこまれているわけですが たとえば、バグがあって実行時エラーになる。デバッグ画面で、いろいろ調べたあと、ＶＢＥメニューの実行->リセットで おわらせますね。このとき【プロジェクトがリセット】されます。 あるいは（本番ではありえないでしょうけど）ブックを読みこんだ後、マクロコードに手を加えると【プロジェクトがリセット】されます。 また、きわめて特殊なコードを使うと（今回は使っていませんが）【プロジェクトがリセット】されます。 【プロジェクトのリセット】状態というのは、ブックが読みこまれた時点に戻る、様々な変数がすべて初期化される状態です。 なので、そのまま継続すると DIC が、からっぽ（Nothing) で参照しようとするとエラーになります。 ですから、そういう状態なら、ブックを開いたときに行った値の生成をやりなおそうというもくろみです。
＊2 指摘されて気が付いたことがあります。後述します。 Ｑ３、Ｑ５ コメント頂き納得できました。
『Ｑ４』
Dictionaryという基本機能にかかわるもので、なかなか、説明しづらいところですね。
キーとしている管理番号を持つひとつ下位の行数を記憶している ということですね。
う〜ん・・ そうではなく、BL列連番+枝番 という単位の背番号に対して、今、枝番が何番まで振られているかという辞書です。
dictionary を使っている範囲は、コピーして挿入処理を行った行について。
はい、そうですね。 新規挿入されて新しい連番が振られたものに関しては、それがコピー元として参照されたときに、このDictionaryで扱われます。
>>セルに枝番自体を書き出していくのではなくて、dicitionaryというメモリ上の機構を使っているのは、セルにアクセスするより高速だからという観点
最初は、管理シートをつくり、そこに値を書いておいて管理しようとも思いました。 （そうしておけば【プロジェクトリセット】の心配も不要になりますので） ただ、その場合、たとえば 管理番号 ABC を参照するのに、MATCH なり Find なり、何かしらの 【検索】が必要になります。 Dictionaryにしておけば、ABC を与えるだけで、ピンポイントの直接参照ができますので、採用しました。 （もちろん、操作者の操作に連動したイベント処理ということで、高速 ということも意識していますが）
『Ｑ６』
tmpにコピーしている意図について教えてください。
ごめんなさい。テスト中に、生成したDictionary の Item の内容を確認するために、書いたコードを消すのを忘れていました。 （Dictionary の Key は ローカルウィンドウで確認できるのですが、Item の内容は表示されないので） 消しておいて下さい。
了解です。実は実機で確認の際、DictionaryのItemがウォッチウィンドウでは表示できないことに気づき、どうすれば確認できるか 質問させて 頂こうかと思っておりました。理解できました。
＊1 アップした処理では、新規挿入、あるいはコピー挿入されたものを対象にしているはずなんですが、仮に 100行あって 50行目をコピーして、60行目に、上書きペーストした場合、行挿入ではないので、処理対象外になります。 そうすると、BL列、BM列が全く同じものが複数できてしまうことになります。 ある意味、これは【バグ】ですから、ちょっと考えてみます。 （この場合、101行目にペーストすると挿入とみなしますが）
この点、もし可能であれば、行に対する変更と一般化して考えて検討していただけないかと思っております
基本的には worksheet_Change イベントの戻り値としてのTargetの列範囲が行全体ではなく、対象としている
表の右端で終わっていれば、それを行に対する変更として扱うということですよね？
ただまだコピーして挿入のケース、単純挿入や更新(行の一部セルのみ更新）のケースをきちんと
場合分けで来ていなくてそこにDicitionaryを使ってし整理できれば、いいろいろ応用できるのではと思って
います
ここから先は、妄想になってしまうかもしれません。申し訳ないです。
材料１)
大村あつしさんのExcel VBAサンプルコレクションに Dictionaryからキー値やItemを配列として取り出すサンプルがあ
りました。そこから発想して 大元のリストと各担当に配布したリストがあり
各担当から戻ったリストには、変更箇所の行に管理番号がセットされていて
key = 変更された行を示す管理番号 例えば
9_1_1 をkey が登録されていれば その行は 9_1 に続く管理番号に持つ行だと決定
できる
ただし 9_2_4 とかではどの列が変更されたか BL列などに登録すれば、更新も管理できると思いました。
★挿入行に対し更新が発生した場合は、▼にせず★のままにしたいです。
大元のリストに対する変更としては挿入だからです。この点コード改修頂けるなら
ば非常にありがたいです
尚、Dictionaryなど少し高度なテクニックを使われる場合に、１行一行でなくとも
何もしようとしているか、書いていただけると、理解が追いつきやすくなるので
そこだけお願いしたいです。
とりあえず今日はここまでです
（かず） 2016/04/19(火) 08:03
コメント拝見。
管理番号をどのように持たせるか、これは、かずさんや担当サイドでわかりやすい採番ルールにすべきであって 私がこうしなさいと申し上げるマターではありません。（もちろんですよね）
コードでやっていることは、かずさんの最初の質問文から、こういうことがやりたいのかなという想像です。 なので、変更・追加のパターンを列挙して、こういう場合は、このように という整理ができれば、それを提示されれば 回答側としては、それを実現するお手伝いをすることができます。
>>２行目と３行目に挿入された行の管理番号が、前後の行の管理番号と繋がらない最大番号になってしまうのは、不自然と思っています。
そういうとらえかたも、もちろんあると思います。ですから、単純挿入の場合は、どういった採番ルールにするのかということを 提示いただければ、そのようなコードにすることができます。
たとえば aaa というデータの下に1行なり複数行なり挿入された。現在のコードでは、それぞれが独立した1件のデータとして、 かつ、番号は、既存データの最大番号の次から採番しているわけですが、これを aaa に対して追加されたと考えるなら aaa の ●番目 という採番もできますね。 このあたりは、かずさんの仕様として決めていただければいいのです。
>> 18_1,19_1 そして 18_1_1 となるのは何を基準に並んでいるのか を説明できない＝扱いにくい と思いました。 >>管理番号の付与ルールが不明なことを気にしています
これも繰り返しになりますが、かずさんのリクエストが、こうなんだと理解（誤解？）して扱っています。 こうしなければいけない ということはありません。
現在のコードでは 18_1_1 は 18_1 が元ネタで、それがコピー挿入された。挿入された場所はどこであっても、元ネタが 18_1 だった。 なので、 18_1_● （● は 18_1 内の連番）にしています。 つまり、18_1_● を見れば これは、元ネタが 18_1 であって、それが変更されているんだなと そういった番号になっています。
そうではなく、こうこうこのように ということであれば、繰り返しになりますが、パターンを列挙して採番ルールを提示いただければいかようにも対応しますということです。
先に書いたレスでふれましたが、挿入ではなく、どこかの行（1行 あるいは 複数行）が 別の行（1行 あるいは 複数行） にコピペされた というケース。 これも、どう考えるかによって、どう扱うべきかが決まってきますので、かずさんが定義されればよろしいんです。 たとえば、ペーストされる前にそこにあったデータはなくなってしまうわけですが、ペースト後だけを見れば、その１つ上の行の下に、コピー挿入された状態とみなすことが できるかもしれません。ならば、その管理番号は、こうこうこうだと、これは かずさんに決めていただくことです。
コード、特に Dictionary関連のものに注釈を付加する件は心がけます。 ただ、コード機能としての注釈はあまり役に立たないかもしれませんよ。 実際には、Dictionaryかどうかにかかわらず、その持ち方含めた構成の中で何をしているのか それが重要ですので。
ここは、なかなかコード内コメントでは説明し切れない というか、説明を入れると、コード1行に対して10行以上の説明文になって コードとしては見づらくなりますので、不明なところは別途質問を投げかけていただき、それにこたえるという形も併用せざるを得ないでしょうね。
（β） 2016/04/19(火) 08:44
追伸です。
このトピ、かなりの分量になっています。閲覧も、どっこいしょ という感じで、スクロールも大変ですし 編集で変更する際も、ちょっとしんどい分量になりました。
このトピを
[[20160327131411]] 『コピーしたセルの挿入時、挿入行の行番号 を把握ａx（かず）
こんなように参照できるようなバナーとともに、新規トピとして NO.2 を立てていただけますか？
（β） 2016/04/19(火) 08:47
とりあえず、新規トピックを立てるそこに買いていきます。
（かず） 2016/04/19(火) 12:10
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.