[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『前任者が作成したVBAを変更して手順書を作成』（ぺけぽん）
[[20091001140757]] 『構文の翻訳』（ぺけぽん）で質問したものです。 前任者が作成した手順書というのが部署内で使用されているのですが、 これまで何度か改変を希望する声がありました。 ですが前任者が既に退職し、しかも親切に構文や意味を記録してくれていたノートを 後任者が紛失するというあり得ないミスが起きてしまい、改変は手つかずになっておりました。 それにこの度手を付けた次第です。上記のスレッドで構文のヘルプの表示方法を教えて頂き、 意味についてはどうにかついて行ったとは思うのですが･･･改変となるとまた止まってしまっております。 とりあえず現状を以下で説明させていただいてますので、御指南よろしくお願いします。
A B C D E F G H I J K
1 加工手順書 工番： 日付：2009.10.22.
2
3
4
5
6
7
8
9
10
11 上 下 ﾃﾞｰﾀNo. ﾃﾞｰﾀNo. エンドミル 文字 種類 深さ 基準径 高さ 速度
12
13
14
：
：
：
：
80 工具昇順 1 φ2.0 250 81 2 φ1.2 150 82 3 φ0.7 150 83 4 φ0.5 100 84 5 φ0.4 100 85 6 φ0.5E2T 100 86 7 0.3RBT 100 87 8 φ0.2-45° 100 ： ：
含んでいる機能としましては、 E80〜,F80〜は速度という名前で定義付けています。 それによってK列12以降には=IF(E12<>"",VLOOKUP(E12,速度,2,0),"")という 関数が入力されているのですが、E列でエンドミルを指定した際に速度(250や100等) が自動表示されるようになっています。 C80はD80〜F87を工具昇順という名前で定義付けしていて、そのタイトルです。 その他にはA12〜K70までを集計という名前で定義づけして、これは後で VBAで並べ替えの範囲として選択したい為です。
作業者は、E12〜K70までをまず入力します。 その後工具別マクロを登録したM20辺りに配置する予定のボタンを ぽちっと押すだけでE列に入力した工具の名称を自動識別し、 工具昇順のリスト通りの優先順位で行ごと並び替えるマクロを作成したいのです。 並び替えた後、E列80以降に入力した工具の種類別に１行開くようにもしたいです。 以下が上記の条件を元に作成したボタンに登録する予定のVBAです。
Sub 工具別()
Application.ScreenUpdating = False
Range("集計").Select
Selection.Sort Key1:=Range("C80"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
これを作動させると、【リストまたはデータベースの真上に行が１行見つかりました。 この行に列ラベルが含まれている場合、選択範囲に列ラベルを含めないと、 フィルタが正しく実行できません。この行を選択範囲に含めますか？】 と出てきます。 データベースの真上に行が１行とはどこの事なのでしょう？ この行を選択範囲に含めますか？に対しては「はい」と返しても「いいえ」と返しても セルに入力した内容が全て飛んでしまいます。
それと、これとは別に元通りというマクロもあり(M列22番あたりに後ほどボタン作成予定)、 これは上記の工具別マクロで並び替え/行開けしたものを、 文字通りマクロ動作前に戻してくれるものです。 これのマクロは以下ですが、工具別マクロが正常動作に至っていないために、元通りマクロに関しては 確認すらできておりません。
Sub 元通り()
Application.ScreenUpdating = False
Range("集計").Select
ActiveWindow.SmallScroll ToRight:=-3
Range("集計").Select
Selection.RemoveSubtotal
Columns("K:R").Select
Selection.EntireColumn.Hidden = False
Selection.RemoveSubtotal
Columns("L:R").Select
Selection.EntireColumn.Hidden = True
End Sub
長々と申し訳ありません。 混乱を防ぐために全て書き出したつもりですが、余計にややこしければ説明を補足させて頂きます。 よろしくお願いします。 WindowsXP Excel2003 です。
話がよく分からないのですが。。。
載せて居られるマクロは 今在るコードをまねて新たに作ったマクロで、動かない と言う事ですか？
それとも、 前任者が作ったマクロで動いていたが ある日突然動かなくなった と言う物ですか？
(HANA)
回答ありがとうございます。 >今在るコードをまねて新たに作ったマクロで、動かない ということです。 前任者が作ったマクロは動いておりましたが 新しく工具の追加や列項目の変更などがありまして、 恐らくそのままでは動かなくなるだろうから変更しようということになりました。 改変前も乗せた方がいいでしょうか？ (ぺけぽん)
そうですね。。。 前任者の方が作ったマクロと それを動かせるシートの状態 （データもいくつか入った物） を教えて貰っておいたほうが良い様な気がします。
(HANA)
ありがとうございます。 なるべく丁寧に表示されるようにしますので、少しお時間をください。 (ぺけぽん)
お世話になっております。 改変前のシートをまとめてみました。
A B C D E F G H I J K L M N O P Q R S T U
1 加工手順書 工番： 日付：2009.10.22.
2 ___________
3 φ2.5 250 1 粗 | 工具別 |
4 φ2.0 250 2 仕上 ~~~~~~~~~~~
5 φ1.2 150 3 角仕上 ___________
6 φ1.0 150 4 面取り | 元通り |
7 φ0.8 150 5 ﾍﾞﾀ ~~~~~~~~~~~
8 φ0.5 100 6 線
9 φ0.4 100 7
10 φ0.3 100 8
11 上 下 文字 エンドミル 種類 深さ 基準径 高さ 速度 ﾃﾞｰﾀNo. ﾃﾞｰﾀNo. 集計 集計 φ0.2 100 9
12 φ0.5-R5 100 10
13 φ1.0-15°150 11
14 φ8E2T 250 12
15 2.5D - 13
16 マーク - 14
17 0.5RBT 150 15
：
：
：
：
上記が前任者が作成したデータを何も入力していない状態のシートです。 L列〜Q列は普段隠されていて非表示になっていますので、STU列をまたぐ【工具別】【元通り】 のボタンが表の横に位置しています。
例えば作業者がデータを入力した場合、下記のようになります。
A B C D E F G H I J K S T U
1 加工手順書 工番： 日付：2009.10.22.
2 ____________
3 | 工具別 |
4 ~~~~~~~~~~~~
5 この部分には業種が絞られる情報が入りますので、 ___________
6 掲載できませんが、殆どテキストですのでマクロには | 元通り |
7 影響しないと思います。申し訳ありません。 ~~~~~~~~~~~
8
9
10
11 上 下 文字 エンドミル 種類 深さ 基準径 高さ 速度 ﾃﾞｰﾀNo. ﾃﾞｰﾀNo.
12 BANANA φ2.0 粗 1.5 R225 50 250
13 BANANA φ0.5 仕上 1.5 R225 50 100
14 APPLE φ1.2 ﾍﾞﾀ 1.0 R225 30 150
15 APPLE φ0.4 仕上 1.0 R225 30 100
16 APPLE φ0.2 線 0.3 R225 30 100
17 GRAPE φ0.4 仕上 0.7 R210 25 100
：
：
含まれている機能を説明しますと、I列12番以降には=IF(D12<>"",VLOOKUP(D12,速度,2,0),"")という関数が 入っています。 N列のφと単位が付いている工具はD列12番以降で簡易リストボックスとして表示され、N列とO列が[速度]という名前で 定義付けられているのでD列を選択して入力すると、I列12番以降にはO列の速度が自動表示されます。 Q列の粗・仕上といった種類も簡易リストボックスを作成していますが、 これはその都度変わってきますのでE列12番以降で作業者が簡易リストボックスから選択します。 文字・深さ・基準径・高さも作業者が都度手入力します。
そして【工具別】を押すと
A B C D E F G H I J K S T U
1 加工手順書 工番： 日付：2009.10.22.
2 ___________
3 | 工具別 |
4 ~~~~~~~~~~~
5 この部分には業種が絞られる情報が入りますので、 ___________
6 掲載できませんが、殆どテキストですのでマクロには | 元通り |
7 影響しないと思います。申し訳ありません。 ~~~~~~~~~~~
8
9
10
11 上 下 文字 エンドミル 種類 深さ 基準径 高さ 速度 ﾃﾞｰﾀNo. ﾃﾞｰﾀNo.
12 BANANA φ2.0 粗 1.5 R225 50 250
13
14 APPLE φ1.2 ﾍﾞﾀ 1.0 R225 30 150
15
16 BANANA φ0.5 仕上 1.5 R225 50 100
17
15 APPLE φ0.4 仕上 1.0 R225 30 100
18 GRAPE φ0.4 仕上 0.7 R210 25 100
19
20 APPLE φ0.2 線 0.3 R225 30 100
21
：
：
となります。 元通りを押すと、
A B C D E F G H I J K S T U
1 加工手順書 工番： 日付：2009.10.22.
2 ____________
3 | 工具別 |
4 ~~~~~~~~~~~~
5 この部分には業種が絞られる情報が入りますので、 ___________
6 掲載できませんが、殆どテキストですのでマクロには | 元通り |
7 影響しないと思います。申し訳ありません。 ~~~~~~~~~~~
8
9
10
11 上 下 文字 エンドミル 種類 深さ 基準径 高さ 速度 ﾃﾞｰﾀNo. ﾃﾞｰﾀNo.
12 BANANA φ2.0 粗 1.5 R225 50 250
13 BANANA φ0.5 仕上 1.5 R225 50 100
14 APPLE φ1.2 ﾍﾞﾀ 1.0 R225 30 150
15 APPLE φ0.4 仕上 1.0 R225 30 100
16 APPLE φ0.2 線 0.3 R225 30 100
17 GRAPE φ0.4 仕上 0.7 R210 25 100
：
：
に戻ります。 【工具別】コードは下記の通りです。
Sub 工具別()
Application.ScreenUpdating = False
Range("集計2").Select
Selection.Sort Key1:=Range("M11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
Selection.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(12), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True End Sub
【元通り】コードは下記の通りです。
Sub 元通り()
Application.ScreenUpdating = False
Range("集計2").Select
ActiveWindow.SmallScroll ToRight:=-3
Range("集計2").Select
Selection.RemoveSubtotal
Columns("K:R").Select
Selection.EntireColumn.Hidden = False
Selection.RemoveSubtotal
Columns("L:R").Select
Selection.EntireColumn.Hidden = True
End Sub
名前の定義は下記の通りです。 『加工』=加工手順書!$Q$3:$Q$16 『工具』=加工手順書!$N$3:$N$32 『手順書』=加工手順書!$A$11:$K$58 『集計』=加工手順書!$N:$P 『集計2』=加工手順書!$A$11:$M$60 『速度』=加工手順書!$N$3:$O$65536
取り急ぎ現在のシート状況のみを記載しました。 私も現在動かないコードをもう少しあれこれいじってみたいと思っていますので、 よろしくお願いします。 (ぺけぽん)
私は、自分が引き継いだ見積書作成業務というVBAをメンテするのに、 [[20090528202449]]『見積マクロの印刷シートの作成』（シュン） で紹介されていた以下のURLに相談したら、非常に丁寧に対応していただけましたよ。 http://www.formzu.net/fgen.ex?ID=P32513425 最近は、お見かけしないので、まだ対応していただけるかどうかはわかりませんが・・・ 一度、相談してみたらいかがでしょうか？
(VBAメンテ担当)
丁寧なご説明有難う御座います。 仕組みは分かりました。
ただ、仕組みが分かるために必要な（重要な）部分を 落とされているように思います。
非表示に成っているL,M列に数式が入っていませんか？ 例えば、 L12に =IF(D12="","",IF(D11=D12,L11,SUM(L11,1))) M12に =IF(D12<>"",VLOOKUP(D12,集計,3,0),"") と言った感じの。
前任の方が作られたコードは、マクロの記録で得られたコードだと思いますので 手作業でぺけぽんさんはその手順を再現する事が出来ます。 分かりやすいように、L列〜Q列を表示させておいてくださいね。
最初のデータの状態（主要部分のみ）です。
[C] [D] ・・・・ [I] ・・・・ [L] [M]
[11] 文字 エンドミル 速度 集計 集計
[12] BANANA φ2.0 250 1 2
[13] BANANA φ0.5 100 2 6
[14] APPLE φ1.2 150 3 3
[15] APPLE φ0.4 100 4 7
[16] APPLE φ0.2 100 5 9
[17] GRAPE φ0.4 100 6 7
[18]
[19]
[20]
１．名前ボックスから「集計2」を選択 集計2と名前を定義された範囲（$A$11:$M$60）が選択されます。
２．メニュー・データ(D)→並べ替え(S) から 最優先されるキーで
(2)集計 を選んで並べ替えます。
(2)集計 は、M列の事です。
↓ここが昇順に並ぶ
[C] [D] ・・・・ [I] ・・・・ [L] [M]
[11] 文字 エンドミル 速度 集計 集計
[12] BANANA φ2.0 250 1 2
[13] APPLE φ1.2 150 2 3
[14] BANANA φ0.5 100 3 6
[15] APPLE φ0.4 100 4 7
[16] GRAPE φ0.4 100 4 7
[17] APPLE φ0.2 100 5 9
[18]
[19]
[20]
３．メニュー・データ(D)→集計(B) で
グループの基準(A)
(2)集計 ▼ ←(2)集計 を選択
集計の方法(U)
合計 ▼ ←合計を選択
集計するフィールド(D)
□(1)データNo. ▲
□(2)データNo. □
■(1)集計 ▼ ←(1)集計にチェックを付ける
■現在の集計表と置き換える(C)
□グループごとに改ページを挿入する(P)
■集計行をデータの下に挿入する(S)
[ OK ]
↓集計が行われ、合計行が挿入される
[C] [D] ・・・・ [I] ・・・・ [L] [M]
[11] 文字 エンドミル 速度 集計 集計
[12] BANANA φ2.0 250 1 2
[13] 1 2 合計
[14] APPLE φ1.2 150 2 3
[15] 2 3 合計
[16] BANANA φ0.5 100 3 6
[17] 3 6 合計
[18] APPLE φ0.4 100 4 7
[19] GRAPE φ0.4 100 4 7
[20] 8 7 合計
[21] APPLE φ0.2 100 5 9
： ： ：
[下の方 32 9 合計
の行] 33 総計
以上が 工具別 マクロが行っている事です。 L列〜Q列が非表示に成っているので 何もないのに間に行が挿入された様に見えますね。
元通り マクロが行っている事ですが １．名前ボックスから「集計2」を選択 ２．メニュー・データ(D)→集計(B) で OKボタンの左にある [全て削除(R)]ボタンを押す ３．K:R列を選択 ４．再表示 ５．メニュー・データ(D)→集計(B) メッセージが表示されても[OK]で進み OKボタンの左にある [全て削除(R)]ボタンを押す ６．L:R列を選択 ７．列を非表示に です。
ちなみに「名前ボックス」というのは、 数式バーの更に左側にある 通常 アクティブセルのセル番地が表示されている部分です。 [ A1 ▼] の様に成っていて、この▼の部分を開くと 名前の付けられている範囲を選ぶことが出来ます。
まずは、元のコードの仕組みをしっかり理解して頂きたいと思います。 その後、現在作っているコードの問題点を 考えていければと思います。
ですから、手順や仕組みで分からない点が有る場合はどんどんお尋ね下さい。
仕組みが分かったら、L,M列に対応する列が 新しく作ろうとしているシートでも必要に成ると思います。
(HANA)
スレがながくなってしまいますが 上で載せておられる 前任者の方が作ったコードに コメントを追加した物を下に載せておきます。
先ほどの手順を、マクロではどの様に書かれているのか 参考にしてみて下さい。
'------
Sub 工具別()
Application.ScreenUpdating = False '画面の更新停止
'★１ 名前ボックスから「集計2」を選択
Range("集計2").Select
'★２ M列で並べ替え
Selection.Sort Key1:=Range("M11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
'★３ 集計
Selection.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
'------
Sub 元通り()
Application.ScreenUpdating = False '画面の更新停止
Range("集計2").Select '集計2 選択・・・・この行と下の行は
ActiveWindow.SmallScroll ToRight:=-3 '画面スクロール・・記録ミスだと思います
'★１ 名前ボックスから「集計2」を選択
Range("集計2").Select
'★２ 選択範囲(集計2の範囲)の集計行の削除
Selection.RemoveSubtotal
'★３ K:R列を選択
Columns("K:R").Select
'★４ 選択範囲(K:R列)を表示
Selection.EntireColumn.Hidden = False
'★５ 選択範囲(K:R列)の集計行の削除
Selection.RemoveSubtotal
'★６ L:R列を選択
Columns("L:R").Select
'★７ 選択範囲(L:R列)を非表示
Selection.EntireColumn.Hidden = True
End Sub
'------
また、記録で出来たコードは無駄な部分も多いので これから作るコードはその辺りも 簡素化出来た物が仕上がると良いと思いますので しばらくのお付き合いを 御願いしたいと思います。
(HANA)
＞非表示に成っているL,M列に数式が入っていませんか？ と、二つ式を載せましたが よく考えると L,M列は同じ式でも良さそうですね。 同じVLOOKUP関数を使った式が入っているかもしれません。
・・・で、全く違ってたりして。（笑） 何が入っているか教えて下さいね。
(HANA)
>HANAさま 夜分遅くまでありがとうございます。 私のエクセルレベルですが、初心者とはあえて書かずにいましたが (でも気づいていると思われますが)初心者に毛すら生えておりません。 なんとか教えていただいたことを理解しつつ進んでいきたいと思っております。 とりあえず、コードに丁寧な解説をいただき誠にありがとうございます。 ヘルプを見ながら追いかけた自分の訳とだいぶ違っており笑ってしまいましたが(泣笑)、 今後の為にも正確な訳をしっかりメモらせていただきます･･･！ ＞記録で出来たコードは無駄な部分も多いのでこれから作るコードはその辺りも 簡素化出来た物が仕上がると良いと思いますので 修正だけでなく簡素化にまでご協力いただけるとは恐縮です！ ＞しばらくのお付き合いを 御願いしたいと思います。 いやもう本当にただただ恐縮するばかりですがこちらこそよろしくお願いします。
L・M列に数式があるか確認してみます。 説明いただいたことの理解にも、申し訳ありませんがまたお時間をください。
無記名が議論されている最中やってしまいましたが、 ↑は(ぺけぽん)です。失礼しました。 （ぺけぽん）
>VBAﾒﾝﾃ担当さま 参考先おしえてくださってありがとうございます。 飛んでみましたが、メールフォームだけはまだ開くようでした。 ですが一旦ここで質問しましたので、力が及ばず諦められる等ない限りは こちらで頑張ってみます。 今後の為に自分で自分がしようとしている事の理解も必要という思惑があるため、 教える側の方に多大なご負担を強いることには変わりありませんので･･･。 ありがとうございます。 (ぺけぽん）
あら〜、ぺけぽんさん 見て居られましたか。 （職場からしかご覧にならない方だと決めつけて居りました。） なのに、衝突☆（笑）
コードの方は、記録で出来た物ですから 並べ替えや集計部分を読み解くのは難しいと思いますし 細かく分かる必要は無いと思います。 （大きな部分や、キーポイントは、分かる必要が有ると思いますが。）
前任者の方が作られた仕組みを踏襲して行くのであれば まずは「どうしてそうなるのか」をご理解頂ければと思います。
エクセルの集計機能（メニュー・データ(D)→集計(B)）を使うと 集計行が挿入されるので、グループ毎に行を挿入出来ます。 この機能を【利用して】居るだけで、集計をしている訳ではありません。 ・・・ってのは、既にお気づきと思いますが。
再度書いておきますが １．関数で、番号を振る ２．番号順に並べ替える ３．グループ毎に空行を挟む （集計の 行が挿入される機能を利用する） と言う仕組みです。
たぶん 実際に手順を行ってもらえれば「ほ〜、本当だぁ」と 納得する事が出来ると思います。
コードの内容で、ポイントと成る部分は そのうち話題に上がると思いますので いまは「こんな操作をすると、こんな事になるんだ」と 読んだ時に、分かる部分の理解をしておいて頂ければと思います。 現在付けているコメントも、どちらかというと 手順を元に付けてあるコメントです。
一応↓ライブラリ「マクロの自動記録」 http://www.excel.studio-kazu.jp/lib/e4b/e4b.html 利用した事がなければ、ご覧に成って頂いておくのが良いと思います。 また、手順を記録にとってもらうと 同じコードが出来ると思いますので 試してみられても良いかもしれません。
(HANA)
>HANAさんへ お待たせしております。 ただいま横から仕事が入ってしまい、マクロの編集にとりかかれないでおります。
L12には何も入っておらず、M12には =IF(D12<>"",VLOOKUP(D12,集計,3,0),"") という関数が入っておりました。 取り急ぎご報告させていただきます。
申し訳ありません。 もう少々お待たせすることになってしまいますが、よろしくお願いします。
ご連絡有難う御座いました。
ごゆっくりどうぞ。 可能な限りお付き合いさせていただきたいと思います。
(HANA)
お世話になっております。遅くなりまして申し訳ありませんでした。 HANAさんに教わったとおりにメニュー・データから並び換え/集計をしてみたところ、 おっしゃった通りになりました！だいぶ感動しました！！ まさに「ほ〜、本当だぁ！！」でした。 いざ自分がしてみて確かになるほど、と思うことしきりでメニューから入る機能だけで 結構色々な事ができるんですね。 全てをマクロに頼っているのかと思っていたので・・・（安易）。
疑問点がいくつか浮上したのでお願いします。 １． =IF(D12<>"",VLOOKUP(D12,集計,3,0),"") この関数の意味を教えてほしいのですが、D12<>""はD12で何か選択されたらという 意味でしょうか？ VLOOKUPの後は、『集計』リストの3番目の列の値を拾ってくるという意味ですか？ 最後の),"")はD12で何も選択されていなければ空白という意味でしょうか？ ２． Ｌ列は何故必要なのでしょうか？Ｍ列があればいいように見えたのですが･･･。
（ぺけぽん）
＞１． おおよそ合っていると思います。
＞D12<>""はD12で何か選択されたらという意味でしょうか？ そうですね。 「D12が""と等しくない場合」と言うのが正確かもしれません。 何か文字が入力（選択）されていたら、D12セルにはその文字が表示され 「""と等しくない」事になると思います。
＞VLOOKUPの後は、『集計』リストの3番目の列の値を拾ってくるという意味ですか？ はい。 集計と名前が付いている範囲の先頭列の中から D12セルと同じデータを探して 見つけた行の3番目の列のデータを返します。
＞最後の),"")はD12で何も選択されていなければ空白という意味でしょうか？ そうです。「""」が返された場合、セルには表示が無くなります。 正確には空白に成るわけでは在りませんが。。。
＞２．Ｌ列は何故必要なのでしょうか？ こちらは実際に試してみられると良いのではないかと思います。
L列が関係してくるのは、先の手順の内 ＞ ３．メニュー・データ(D)→集計(B) で ＞ 集計するフィールド(D) ＞ ■(1)集計 ▼ ←(1)集計にチェックを付ける の部分です。
実際にL列以外の「ここぞ」と思う列にチェックを入れて 集計(B)を行ってみて下さい。
理由が分かると思います。
もしもL列が不要であれば。。。不要なのかもしれません。 ぺけぽんさんがこれからコードを作成される際には 無くして仕舞っても良いかもしれません。
(HANA)
>L列が関係してくるのは、先の手順の内 ＞ ３．メニュー・データ(D)→集計(B) で ＞ 集計するフィールド(D) ＞ ■(1)集計 ▼ ←(1)集計にチェックを付ける の部分です。 実際にL列以外の「ここぞ」と思う列にチェックを入れて 集計(B)を行ってみて下さい。
やってみました。 本当ですね、SUBTOTALの式を元に何やら0が出てきてしまいます。 列をずらそうと思って式の位置を変えたり、 チェックする部分を替えたりしても出てしまうものなのですね。 Ｌ列は不要だと思っていたのですが、 新シートの構成によってはあえて消す必要もなくなるかもしれないので、 ここはＬ／Ｍ列には触れずにやっていこうかと思います。
新シートの構成ですが、 当初「こんな感じでしようと思ってます」みたいに掲載した分では リストを80行以降で作成するようにしていましたが、 少し仕様を替えて、別シートでリストを作りそれを参照させる方向にしていこうかと思います。 →別シートは作業者に絶対いじらせない目的で。 といいますのも、作業者によってはマクロ作動後に行毎で移動や削除を行ってしまうせいか 循環参照が出てしまうことがあります。 その場合Ｍ列の式が=IF(#REF!<>"",VLOOKUP(#REF!,集計,3,0),"")となっていて 修正の仕方も今一分かりませんでしたので放置しております。 作業者が増えてまいりましたので循環参照にならないよう注意を促すよりは、 いっそなりにくいように作り替えてしまおうかなと･･･。
レイアウトの修正希望もあり、現在列の最後尾にありますデータNo.という２列を前に持ってくるので、 列の変更によりＭ列のIF関数など多少変更が必要な部分があります。 とりあえず教えて頂いた内容を参考にして自分で新シートを作成してみます。
（ぺけぽん）
基本的にマクロの記録で出来たコードなので ＞作業者によってはマクロ作動後に行毎で移動や削除を行ってしまうせいか ＞循環参照が出てしまうことがあります。 この辺は【自由にさせない徹底】をして於いた方が良いと思います。
また、行やセルの移動・挿入・削除 等を許可するなら １．名前の定義の範囲 ２．入力されている数式 等も意図した状態と違ってくる可能性が出てくると思います。
現在のコードは 意図した状態で【データが整っている】 と言う条件で、希望する結果が得られています。 入力データを確認しながらデータを整える と言う作業は、コード内で行われていませんので その辺りは念頭に置いておく必要が有ると思います。 まぁ、その処理も入れてしまえば良い と考えるなら、そうかもしれませんが。。。
＞別シートは作業者に絶対いじらせない目的で。 ＞別シートでリストを作りそれを参照させる これに関してはその様にしておかれるのが良いと思います。
(HANA)
>HANAさん、いつもお世話になっております。 返信遅くなりまして本当に申し訳ありません。
お伺いしたいのですが、
>入力データを確認しながらデータを整える >と言う作業は、コード内で行われていませんので >その辺りは念頭に置いておく必要が有ると思います。 >まぁ、その処理も入れてしまえば良い >と考えるなら、そうかもしれませんが。。。
すみません、私の知識不足の為に全く理解できていないのですが、 入力データを確認しながらデータを整えるという作業がコード内で行われていない、 ということは、指示すればそういう機能を追加することも可能という事ですか？ まぁ、その処理も入れてしまえば良いと考えるならそうかもしれませんが･･･ と仰ってるので可能なのかも知れませんが、 ごめんなさい。どういうものなのか全く想像ができません･･･。
とりあえず現在自分なりに新しいシートを作成中で、
【リストシート】
A B C D E F
1
2
3
4
5
6 1 φ2.5 250
7 2 φ2.0 250
8 3 φ1.2 150
9 4 φ1.0 150
10 5 φ0.8 150
11 6 φ0.5 100
12 7 φ0.4 100
13 8 φ0.3 100
14 9 φ0.2 100
15 10 φ0.5-R2 100
16 11 φ1.0-15° 150
17 12 φ8E2T 250
18 13 2.5D -
19 14 マーク -
20 15 0.5RBT 150
：
：
：
【手順書シート】
A B C D E F G H I J K L M N
1 加工手順書 工番： 日付：2009.10.22.
2
3
4
5
6
7
8
9
10
11 上 下 ﾃﾞｰﾀNo. ﾃﾞｰﾀNo. エンドミル 文字 種類 深さ 基準径 高さ 速度
12 φ2.5 250
13 φ1.0 150
14 φ0.5 100
：
：
：
：
上記のような２種類のシートを作成して、「リストシート」をデータベースのように使用するようにしました。 基本的には従来の集計機能を使っていく方向にしようかと思いまして、 「手順書シート」のＫ列１２番に =IF(E12<>"",VLOOKUP(E12,'リスト'!$E$6:$F$40,2,FALSE),"") という関数を入れ、望み通りの数字を引っ張ってくることができました。 後の集計機能に使う為にＭ列１２番以降に優先番号を引っ張ってくるために =IF(E12<>"",VLOOKUP(E12,'リスト'!$D$6:$F$40,1,FALSE),"") という関数を入れたのですが、何故かこちらでは#N/Aのエラーが返ってきてしまいます。 数値を引っ張ってきたい意図は同じな為、同じ関数を入れたのですが どこが違っているのかご指摘お願いします。
>作業者には絶対いじらせない方向で と以前私が言っていたのはこのリストシートの事のつもりでした。 絶対いじらせない方向といっても、私に出来るのはせいぜいシート見出しの色を変えて シート名に（さわるな）と加えることぐらいですが･･･。
とりあえず書きたい事をズラズラと書いてしまいました。 よろしくお願いします。
（ぺけぽん）
１．VLOOKUP関数について理解を深めて下さい。
VLOOKUP関数は、範囲の先頭列を検索します。 ＞=IF(E12<>"",VLOOKUP(E12,'リスト'!$D$6:$F$40,1,FALSE),"") これでは、E12セルと同じ値を D6:D40セルから探すことになりますので 見つからず、#N/Aエラーが表示されます。
前任者の方が作成された表は、「サイズ」「速度」「番号」の順に並んで居ましたね。 ですから、サイズを検索して、番号を取得することが出来ました。
リストシートへ必ず番号が順番に成るように表を作るのなら （並べ替えたい順番で並んでいるなら）MATCH関数を調べてみて下さい。
２．>入力データを確認しながらデータを。。。
どの様に表現しておけば良いのか良く分からないのですが、 例えば、行の挿入削除等を行単位で行わなかった場合 または、参照しているセルが切り取り等で移動されてしまった場合 入力されている数式で希望する結果を得られない可能性が出てきます。
M列の式が出来ていない状況で仮の話で進めると紛らわしいのですが。。。 手順書シートのK12セルに =IF(E12<>"",VLOOKUP(E12,'リスト'!$E$6:$F$40,2,FALSE),"") の式が入っています。 仮にこの式が、M12にも入っているとします。 そして、L,M列は非表示に成っています。 このシートを使う人は、A:K列が一つの表だと思いますね。 そこで、A12:K12を選択して行を挿入します。 K12の式が無くなるので、下からコピーします。
すると、K12の式は E12セルを参照しますが M12の式は E13セルを参照する事になります。 M列の以降の式は、一つ下の行のE列のセルを参照します。
或いは、行全体を選択して挿入を行った場合、K列迄の式や書式などは 目に見えるのでコピーしてもらえると思いますが M列のセルの数式は未入力のままに成るかもしれません。
この様な状況が 意図した結果を得られていない状況 です。
そんな状況を見つけたら（手作業なら）数式をなおしますね。 一つずつ見て確認するのは面倒なので、そんなことはせずに そのセル範囲全体を 正しい数式に書き直してしまうと思います。
ですから、マクロ内にもその作業を追加しておくのが 良いのではないかと思います。 これもおおよそはマクロの記録でコードが得られますので 難しく考えなくても大丈夫です。
現在のコードには無い部分ですし 「もしも○○されてしまったら」 と言う部分なので、全部が完成してから追加する事にしましょう。 それまで忘れずに覚えておいて下さい。
(HANA)
>HANAさん 早速のお返事ありがとうございます。
> VLOOKUP関数は、範囲の先頭列を検索します。 分かってるつもりで分かってませんでした。 以前のコメントでもきっちり「先頭の」と書いて頂いていたのにすみません･･･。 リストシート内のセルを入れ替えて、式の列番号も入れ替えましたら 希望通りの数値が返ってきました。
私が全く分からないと言っていた部分についても丁寧に説明頂いて本当にありがとうございます。 少し分かった気がします。 気にして頂いた通り、私はなんだかものすごく難しい風に考えていたみたいです。 もしかしてＡＩみたいなのができあがるんじゃなかろうか（汗）、 というぐらい脳内が広がっておりました（苦笑）
引き続き、再度マクロの記録で集計機能のマクロを作っていってみます。 今から会議に出るためまた返事が遅くなるかもしれません。 結果を再ＵＰしますので、その時にはまたよろしくお願いします。
（ぺけぽん）
できました！！ いつも長らくお待たせしてすみませんm(_ _)m 新シートを作成しました。
【リストシート】
A B C D E F
1
2
3
4
5
6 φ2.5 250 1
7 φ2.0 250 2
8 φ1.2 150 3
9 φ1.0 150 4
10 φ0.8 150 5
11 φ0.5 100 6
12 φ0.4 100 7
13 φ0.3 100 8
14 φ0.2 100 9
15 φ0.5-R2 100 10
16 φ1.0-15°150 11
17 φ8E2T 250 12
18 2.5D - 13
19 マーク - 14
20 0.5RBT 150 15
：
：
：
【手順書シート】
A B C D E F G H I J K L M N
1 加工手順書 工番： 日付：2009.10.22.
2
3
4
5
6
7
8
9
10
11 上 下 ﾃﾞｰﾀNo. ﾃﾞｰﾀNo. エンドミル 文字 種類 深さ 基準径 高さ 速度
12 φ2.5 250
13 φ1.0 150
14 φ0.5 100
：
：
：
：
M12に=IF(E12<>"",VLOOKUP(E12,'リスト'!$D$6:$F$41,3,0),"")という式を挿入しています。 VBEには、
Sub 工具別()
Application.Goto Reference:="集計"
Selection.Sort Key1:=Range("M12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
Selection.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("K12").Select
End Sub
Sub 元通り()
Application.Goto Reference:="集計"
Selection.RemoveSubtotal
Columns("K:N").Select
Selection.EntireColumn.Hidden = False
Selection.RemoveSubtotal
Columns("L:M").Select
Selection.EntireColumn.Hidden = True
Range("K12").Select
End Sub
上記２種類のコードをマクロの自動記録で作成して、それぞれボタンに登録しました。 今のところ試験的な動作では特に問題はなかったのですが、 HANAさんから見て、まずいコード等ありませんか？ 以前教えて頂いた時には、自動記録でできたコードは無駄な部分も多いと仰っていたのですが 簡素化できますか？
もしあればご教授ください。よろしくお願いします。
(ぺけぽん)
出来ましたね！！良かったです。
今までと同じ様な使い方をするので有れば たぶん、問題ないと思いますが。。。 取り敢えず １．「集計」と名前が付けられた範囲を教えて下さい ２．＞M12に=IF(E12<>"",VLOOKUP(E12,'リスト'!$D$6:$F$41,3,0),"")という式を挿入 を、マクロの記録にとってみて下さい ３．新しいブックで、A1:A10の範囲に「名前の範囲」と言う名前を付けた後 5:10行目を 右クリック→削除 した場合 「名前の範囲」の名前が付いた範囲が A1:A4 に成ることを確認して下さい。
(HANA)
>HANAさん 早速のお返事ありがとうございます！！
１．「集計」はA11:M60です。 ２．↓こんな感じのコードがとれました！
Sub Macro1()
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]<>"""",VLOOKUP(RC[-8],'リスト'!R6C4:R41C6,3,0),"""")"
Range("M13").Select
End Sub
３．やってみたら「名前の範囲」の範囲が A1:A4になりました！
（ぺけぽん）
行を削除した場合、定義した名前の範囲が狭くなりますね。 ですから、気付かない内に大量に削除等されていた場合は 「集計」で名前の定義をしている範囲が、当初期待しているA11:M60 の範囲より、かなり小さく成る可能性があります。
動かしていないので、上手く動くかどうか微妙ですが。。。 こんな感じで
Sub 範囲を決めつけて()
Range("M12:M60").FormulaR1C1 = _
"=IF(RC[-8]<>"""",VLOOKUP(RC[-8],'リスト'!R6C4:R41C6,3,0),"""")"
With Range("A11:M60")
.Sort Key1:=Range("M12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
End Sub
コード内で範囲を決めてしまうと 思いがけず小さく成っていた場合の心配は無くなります。 データが60行より多く入力されていた場合は 範囲から外れてし舞いますし 入力範囲を大きく変更したい場合は、コードを開いて直接 変更する必要が出てきますが。。。。
また、数式を入力するコードも付け加えました。 何かの理由で、M列の数式が消えてしまっていたり 参照先がおかしくなっていても 正しい数式に戻ります。
((元通り))の方は、要するに L:Mに関して「RemoveSubtotal」が出来れば良いので
Sub 元通り()
Columns("L:M").RemoveSubtotal
End Sub
だけで良いと思います。
どちらもSelectしませんので
Range("K12").Select
が不要になると思います。
あとは、
"=IF(RC[-8]<>"""",VLOOKUP(RC[-8],'リスト'!R6C4:R41C6,3,0),"""")"
って、わかりにくいよね。
とか
データが60行より多く入力されていた場合は？
って所がもう少し改善の余地あり。
まずは、ここまでが上手く動くかどうか 確認してみてもらえますか？
(HANA)
HANAさん、いつもお世話になっております。
範囲を決めつけてコードですが、
>データが60行より多く入力されていた場合は
と仰られていた通りで、そうなる可能性が大いにありましたので
「集計」の範囲をA11:M200に変更して、
>Range("M12:M60").FormulaR1C1 = _
>With Range("A11:M60")
の２ヶ所にあるM60をM200に変更しました。
その上で現在、範囲を決めつけてコードをVBEに入れて、 ボタンに登録して使用してますが、今のところ問題なく動作しています☆
範囲を決めつけてコードの解釈について質問させてください。
> Range("M12:M60").FormulaR1C1 = _
"=IF(RC[-8]<>"""",VLOOKUP(RC[-8],'リスト'!R6C4:R41C6,3,0),"""")"
◎M12:M60に、行列を当てはめて下記数式を返す。
> With Range("A11:M60")
.Sort Key1:=Range("M12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
◎A11:M60の範囲を選択
範囲を並べ替え／集計（マクロ自動記録でコードを取得）
で合ってるでしょうか？ﾄﾞｷﾄﾞｷ･･･
> "=IF(RC[-8]<>"""",VLOOKUP(RC[-8],'リスト'!R6C4:R41C6,3,0),"""")" って、わかりにくいよね。 と仰られていて、たしかに見た感じ難しそうでプチパニックだったのですが、 落ち着いて見ていたら基本は従来の数式と同じで、RCという部分は行列を自動で返すという意味でいいでしょうか？
よろしくお願いします。
（ぺけぽん）
＞で合ってるでしょうか？ﾄﾞｷﾄﾞｷ･･･ そうですね、大抵あっていると思いますが
＞◎M12:M60に、行列を当てはめて下記数式を返す。 ここは記録で出来たコードですから そんなに生真面目に考えなくても良いと思います。 記録した時は アクティブセルに「=IF(E12<>"",VLOOKUP(E12,'リスト'!$D$6:$F$41,3,0),"")」の式を入れました。 ですから今回も M12:M60のセルに「=IF(E12<>"",VLOOKUP(E12,'リスト'!$D$6:$F$41,3,0),"")」の式を入れるコードです。
＞◎A11:M60の範囲を選択 この「選択」と言うイメージは少し違っている様に思います。 マクロの記録をとる際は、こちらの意志をエクセルに伝える為には どうしても選択する必要が出来ますが エクセルに意志さえ伝えられれば、選択する必要は有りません。
例えば、A1セルがアクティブな状態から A2セルに「A2セルです」と言う文字を入れる事を マクロの記録でやろうとすると １．A2セルを選択 ２．アクティブセルに文字を入力し、Enter を押す と言う動作をするので、次の様なマクロが出来ます。
Sub Macro1()
Range("A2").Select
ActiveCell.FormulaR1C1 = "A2セルです"
Range("A3").Select
End Sub
しかし、コードを直接書く場合は エクセルに直接意志を伝えれば良いです。 上のコードの「ActiveCell」の部分が アクティブセルじゃなくて、A2セルだよ
Sub 直接()
Range("A2").FormulaR1C1 = "A2セルです"
End Sub
と、単純にはこんなコードになります。
これで、アクティブセルがどこであっても マクロを実行すると、A2セルに「A2セルです」 と入ることになります。
((範囲を決めつけて))の話しに戻ると
With Range("A11:M60")
の下の行は、今回はいきなり「.Sort」で始まっていますね。
今までは「Selection.Sort」でした。
この .の前に省略されているのが Withの後に書いてある「Range("A11:M60")」です。
Range("A11:M60").Sort
と書いてあるのと同じ意味なので
A11:M60セルの範囲をソートしてね
と伝えています。
これまでは、記録で作っていたので １．並べ替えたい範囲を選択 ２．選択した範囲に関して並べ替え と言うコードになっていましたが。
その下の「Subtotal」に関しても同様です。
今までは「Selection.Subtotal」→選択範囲を集計
と成っていましたが、今回は
(Range("A11:M60")が上にまとめて書いてあって)
「.Subtotal」→A11:M60の範囲を集計
です。
マクロの記録で出来たコードは どうしても ○○.Select Selection.×× と成りますが Selectしないコードを書くのが良いと言う事に成っています。 最後にどこを選択したのか、覚えておく必要が無くなりますし 処理速度が速く成ったり、画面のちらつきがおさえられたりしますから。
・・・と、ここまでが前回迄のコードに関してです。 簡単にしか書いていませんので、少し難しいかもしれません。
↓は、さらにもう少し難しいかもしれませんが 後少しですので 頑張ってみて下さい。
課題が二つ残っていますが、まずは簡単な方から。。。
＞＞"=IF(RC[-8]<>"""",VLOOKUP(RC[-8],'リスト'!R6C4:R41C6,3,0),"""")" ＞＞って分かりにくいです。 数式を入れたら、勝手にこの様に記録されます。 まじめに考えるなら、M12セルに 「=IF(E12<>"",VLOOKUP(E12,'リスト'!$D$6:$F$41,3,0),"")」 の式を入れて得られたコードですから RC[-8] が E12 、R6C4 が $D$6 、 R41C6 が $F$41 の事ですね。
＞R6C4 が $D$6 、 R41C6 が $F$41 を見ると Rの後ろに数字があったら、その行 Cの後ろに数字があったら、その列（A列を1から数えた番号） ってのが分かります。
それを踏まえて ＞RC[-8] が E12 を見ると、M12から見て 同じ行の8つ前のセル ですね。 何も無ければ、同じ行（列） [ ]で囲われていたら、入力セルから見て数えた位置。
丁度、絶対参照 $D$6 と、相対参照 E12 ですね。
こうやって数えていけば分かりますが A2セルに「= "A2セルです"」で入れたように M12セルにも「=IF(E12<>"",VLOOKUP(E12,'リスト'!$D$6:$F$41,3,0),"")」と 入れられれば良いのに。。。 と思ったら、もう少しじっくり見てみます。
まず、数式の前後を "" で囲う必要が有りそうです。 それから、「""」の部分は「""""」に変わっていますね。
数式をその様に変更して、差し替えると。。。
Range("M12:M60").FormulaR1C1 = _
"=IF(E12<>"""",VLOOKUP(E12,'リスト'!$D$6:$F$41,3,0),"""")"
エラーが出ちゃうんですよねぇ。
そこでもう少しじっくり見ると 「R1C1って書いてある！！」
ので、当てずっぽうでこれを消してみる。
Range("M12:M60").Formula = _
"=IF(E12<>"""",VLOOKUP(E12,'リスト'!$D$6:$F$41,3,0),"""")"
と、何と上手く行っちゃいます。
まぁここは そんなに頻繁に変わることは無いと思いますし 範囲が変わった時にコードを開かないといけないので 「'リスト'!$D$6:$F$41」の部分は名前の定義をしておいて 数式内で使用することにしても良いと思いますよ。
勿論、これから書く方法で マクロで範囲を取得して 数式内に織り込んでも良いとは思いますが。。。
足早に・・・・ もう一つの課題についてです。
もう一つの課題。。。 ＞データが60行より多く入力されていた場合は ですよ。
まずは確認して貰うために、E11が見出し行ですが 続いて、E12,E13,E14 と３セルで、データを入力（選択）して下さい。
たとえば、E20を選んで Ctrl + ↑ のキー操作をすると アクティブセルは E14 セルに移動します。 これを頼りにすると、E列の入力が有る最終行は 14 行目 と言う事が分かります。
それをマクロの記録にとると
Sub Macro2()
Range("E20").Select
Selection.End(xlUp).Select
End Sub
こんなコードになるのでこれを踏まえて。。。。
Range("E" & Rows.Count).End(xlUp).Row
このコードが
E列の一番下の行のセルから Ctrl + ↑ で移動したセルの 行
を求めるコードになります。
コードの流れを考えると 行の挿入等が行われる事を考えると面倒なので・・・ １．M列の数式が入っている範囲の数式を削除する。 入力がある最終行は End(xlUp).Row で求めて その範囲を [Delete]します。 [Delete]のコードは、マクロの記録で確認して下さい。 ２．E列のデータが入力されている最終行を取得する。 ３．2で得られた範囲に数式を入れる。 ４．2で得られた行の範囲で、ソート＆集計 です。
'------
Sub 範囲を確認して()
Dim max_row_M As Long, max_row_E As Long
'M列の入力がある最終行を求めて
max_row_M = Range("M" & Rows.Count).End(xlUp).Row
'[Delete]
Range("M12:M" & max_row_M).ClearContents
'E列の入力がある最終行を求めて
max_row_E = Range("E" & Rows.Count).End(xlUp).Row
'M列に数式を入れる
Range("M12:M" & max_row_E).FormulaR1C1 = _
"=IF(RC[-8]<>"""",VLOOKUP(RC[-8],'リスト'!R6C4:R41C6,3,0),"""")"
'11行目からE列の入力がある最終行迄の A:M列に関して
With Range("A11:M" & max_row_E)
'ソート
.Sort Key1:=Range("M12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
'集計
.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
End Sub
'------
(HANA)
さっそくですが、心を入れ替えて、少し修正です。
'------
Sub 範囲を確認して_修正版()
Dim max_row_M As Long, max_row_E As Long
'M列の入力がある最終行を求めて
max_row_M = Range("M" & Rows.Count).End(xlUp).Row
'11行より大きい場合
If max_row_M > 11 Then
'[Delete]
Range("M12:M" & max_row_M).ClearContents
End If
'E列の入力がある最終行を求めて
max_row_E = Range("E" & Rows.Count).End(xlUp).Row
'11行より大きい場合
If max_row_E > 11 Then
'M列に数式を入れる
Range("M12:M" & max_row_E).Formula = _
"=IF(E12<>"""",VLOOKUP(E12,'リスト'!$D$6:$F$41,3,0),"""")"
'11行目からE列の入力がある最終行迄の A:M列に関して
With Range("A11:M" & max_row_E)
'ソート(M列で) ↓11行目が・・・・・・・・タイトル行↓
.Sort Key1:=Range("M11"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
'集計
.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
End If
End Sub
'------
Ctrl + ↑ で見出し行まで行ってしまった場合は 処理をしないようにするのと ソートの所で、「M12の列を タイトル行・自動」に成っていたのを 「M11の列を タイトル・有り」に変更 上でも書きましたが、関数部分を分かりやすい物に変更
以上です。
(HANA)
HANAさんへ 丁寧な解説とコード本当にありがとうございます！！ 完璧に理解はできないかもしれませんが、 どこがどうでどうなるぐらいは理解しようかと思いますので また少し時間をくださいm(_ _)m 長く付き合わせてしまって申し訳ないですが、 学校で勉強しているようで、 マンツーでピンポイントの質問に答えて頂いているので すごく楽しいし勉強になります！！
(ぺけぽん)
HANAさんへ ご無沙汰しております。 長らく時間を空けてしまい申し訳ありません。 手順書は年明けから稼働の予定で、とりあえず形だけはできあがった分を みんなには使ってもらって、自分はせっせとDim変数を勉強していました。 未だにさっぱり分かっていないのですが、循環参照エラーが出た従来の手順書に 教えて頂いたコードを挿入すると、一部解消されました！！ 本当にありがとうございます☆ ですが一部というのは･･･途中で結合セルがありましてそこでエラーになるようなのです。 結合セルは作らなければいけない場合が多々あるのですが、 調べた限りでは結構くせ者なのでしょうか･･･？ 結合セルを作る行はその都度違うのですが、列で言えばE〜K列を結合させることは決まっています。 この結合セルだけを避けてコードを動かしたいのですが、 ごめんなさい、もうちょっと考えてみます。。。 (ぺけぽん)
結合セルは曲者ですね。
ただ、ご説明からは 何処がどの様に セルの結合がされるようなパターンが有るのか 良く分かりません。
具体的にはどの範囲のセルを結合して どの様なデータが入っている時に どのコードを実行したら どの行で、どの様なエラーに成るのでしょう？
＞この結合セルだけを避けてコードを動かしたいのですが、 と言う事は、 セルを結合する事は有るが、現在のコードの処理対象外に有る と言う事なのでしょうか？ 手作業で作業をする場合は、どの様に回避するのでしょう？
そもそも、どうしてセルの結合をするのですか？
(HANA)
>HANAさん 早速のお返事ありがとうございます。
>そもそも、どうしてセルの結合をするのですか？ 結合セルは加工する際の注意事項を書き込む欄になります。 大昔、行の中央に文字が位置するようにお願いという注文があった為に それ以降、結合セルをつかって中央に配置されるようになりました。
エラーの様子については見やすいように配置しますので、 また少しお時間を下さい。 いつもいつもレス遅くて恐縮です･･･
(ぺけぽん)
いつもお世話になっております。 お待たせして本っ当に申し訳ありません。 最新版で循環参照を出せなかったので、試したのは前の仕様の手順書です。 コードも前の仕様で適用できるように書き換えてあります。
┌─┬─┬─┬─―───┬─―――─┬───┬──┬――─┬─―┬――┬―――――┬―――――┐ │ │Ａ│Ｂ│ Ｃ │ Ｄ │ Ｅ │ Ｆ │ Ｇ │ Ｈ │ Ｉ │ Ｊ ｜ Ｋ ｜ Ｌ Ｍ ├─┼─┼―┼─────┼─―――─┼─―─┼──┼─―─┼──┼──┼―――――┼―――――┤ 〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜 ├─┼─┼―┼─────┼─―――─┼─―─┼──┼─―─┼──┼──┼―――――┼―――――┤ │11│上│下│ 文字 │エンドミル│ 種類 │深さ│基準径│高さ│速度｜データNo ｜データNo ｜集計 集計 ├─┼─┼─┼─────┼─―───┼───┼―─┼───┼──┼──┼―――――┼―――――┤ │12│ │ │ BANANA │ φ2.0 │ │ │ │ │ │ ｜ ｜ 10 ├─┼─┼─┼─────┼―────┼───┼──┼───┼──┼──┼─――――┼―────┤ │13│ │ │ APPLE │ φ2.0 │ │ │ │ │ │ ｜ ｜ 10 ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤ │14│ │ │ │ │ │ │ │ │ │ ｜ ｜ ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤ │15│ │ │ APPLE │ φ1.2 │ │ │ │ │ │ ｜ ｜ 11 ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤ │16│ │ │ GRAPE │ φ1.2 │ │ │ │ │ │ ｜ ｜ 11 ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤ │17│ │ │ │ │ │ │ │ │ │ ｜ ｜ ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤ │14│ │ │ 結合セル：注意書きを記入にします。 ｜ ｜ ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤ │18│ │ │ BANANA │ φ0.5 │ │ │ │ │ │ ｜ ｜ #N/A ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤ │19│ │ │ APPLE │ φ0.5 │ │ │ │ │ │ ｜ ｜ #N/A ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤ │20│ │ │ GRAPE │ φ0.5 │ │ │ │ │ │ ｜ ｜ #N/A ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤ │21│ │ │ │ │ │ │ │ │ │ ｜ ｜ └─┴─┴─┴─────┴─―───┴───┴──┴───┴──┴─―┴―――――┴――――─┘
【コード】
Sub 範囲を確認して_修正版()
Dim max_row_M As Long, max_row_D As Long
'M列の入力がある最終行を求めて
max_row_M = Range("M" & Rows.Count).End(xlUp).Row
'11行より大きい場合
If max_row_M > 11 Then
'[Delete]
Range("M12:M" & max_row_M).ClearContents
End If
'E列の入力がある最終行を求めて
max_row_D = Range("D" & Rows.Count).End(xlUp).Row
'11行より大きい場合
If max_row_D > 11 Then
'M列に数式を入れる
Range("M12:M" & max_row_D).Formula = _
"=IF(D12<>"""",VLOOKUP(D12,集計,3,0),"""")"
'11行目からE列の入力がある最終行迄の A:M列に関して
With Range("A11:M" & max_row_D)
'ソート(M列で) ↓11行目が・・・・・・・・タイトル行↓
.Sort Key1:=Range("M11"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
'集計
.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
End If
End Sub
【エラーメッセージ】
実行時エラー '1004' この操作には、同じサイズの結合セルが必要です。
以上がエラーの詳細なのですが、情報は足りていますでしょうか？ 色んなトピを巡ってみましたが、私の能力不足というのがあり、あまり有効な手段は得られませんでした。 よろしくお願いします。 （ぺけぽん）
├─┼─┼―┼─────┼─―――─┼─―─┼──┼─―─┼──┼──┼―――――┼―――――┤ │11│上│下│ 文字 │エンドミル│ 種類 │深さ│基準径│高さ│速度｜データNo ｜データNo ｜集計 集計 ├─┼─┼─┼─────┼─―───┼───┼―─┼───┼──┼──┼―――――┼―――――┤ ├─┼─┼―┼─────┼─―――─┼─―─┼──┼─―─┼──┼──┼―――――┼―――――┤ 〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜 ├─┼─┼―┼─────┼─―――─┼─―─┼──┼─―─┼──┼──┼―――――┼―――――┤ │17│ │ │ │ │ │ │ │ │ │ ｜ ｜ ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤ │14│ │ │ 結合セル：注意書きを記入にします。 ｜ ｜ ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤ │18│ │ │ BANANA │ φ0.5 │ │ │ │ │ │ ｜ ｜ #N/A ├─┼─┼─┼─────┼─―───┼───┼──┼─―─┼──┼─―┼―――――┼――――─┤
行番号おかしくないですか？ 11行目の項目名（「データNo」「集計」）が重複しているのは合っていますか？ 14、17行目が空白になっているのは、そういうものですか？ 結合セルの行は、何かの情報で特定できますか？
結合セルは、C14〜J14のセルだけなのですか？ VBAは、結合セルと相性が悪く、その特性を理解しておかないと、うまく動かないことが多いのは、ご存知ですか？ Sort部分でエラーになっているのだと思いますので、手作業で一度並べ替えしてみてください。 たぶん、同様のエラーになると思いますので、とりあえず、結合セルを解除してみて試してみてください。
そこでうまくいくことが確認できれば、次にSubtotalでエラーになると思います。 これは、並べ替えの結果、空白行があるからです。
元データと求めている結果を載せられてはいかがでしょうか？ 本当は、ブックそのものを見たほうが解決は早いような感じもします。
(VBAメンテ担当)
話を整理させて下さい
＞大昔、行の中央に文字が位置するようにお願いという注文があった為に ＞それ以降、結合セルをつかって中央に配置されるようになりました。
と言う事は 前のマクロでは、結合セルが有っても問題無かったが 新しいマクロにしたら、問題が出る と言う事ですか？ あまり考えにくいですが。。。
今回の変更に伴い、どこか変わったところが無いか 確認して見ていただけませんか？ 済みませんが、私はもうすっかり忘れています。
それから、お話としては D列の値でグループを作って間に行を入れていく だったと思いますが ＞結合セルは加工する際の注意事項を書き込む欄 こんな事をすると、処理内容が変わってきませんか？
(HANA)
> 手順や仕組みで分からない点が有る場合はどんどんお尋ね下さい。 > > これから作るコードはその辺りも > 簡素化出来た物が仕上がると良いと思いますので > しばらくのお付き合いを 御願いしたいと思います。 > > 可能な限りお付き合いさせていただきたいと思います
と期待させておいて、
> 済みませんが、私はもうすっかり忘れています。
は、ないだろう（汗）無責任では？
だから、VBAメンテ担当さんが相談窓口みたいなところを紹介したのでは？ ああいう形だと、サンプルファイルで説明できるしねｗｗ
ぺけぽんさんも、個人的に相談を聞いてもらえるところを紹介してもらったのであれば、 考えたほうがいいかもですよ。 ただし、最終的な結果は、掲示板の過去ログという資産に残りますから、後で報告すればいいと思います。
(傍観者)
＞VBAメンテ担当さん、HANAさん 迅速な回答とてもありがとうございます。 すみません、今現在回答を用意中でしたが慌ててでてきてしまいました。
＞傍観者さん ご意見ありがとうございます。 けれどこの経緯には私自身他業務に追われるなどして長くアクセスできなかったことがあり、 決して私以外の方に責任が及ぶところではありません。 誤解を招くこととなってしまい誠に申し訳ないかぎりです。
みなさん、これまで誠にありがとうございました。 特にHANAさん、ご指導の下作らせて頂いた手順書は以前のものより格段に使いやすく、 エラー報告もありません。本当にありがとうございました。 ただ、結合セルでエラーが出たDim変数については、 最初に結合セルがある旨を伝えるのを失念していた私のミスですので、 教えて頂いたもので使えないか再考し、無理そうであれば他の方法を考えます。 新たな御回答に答えていないことが心苦しく感じますが、 一旦ここで閉じさせていただきたく思います。 本当にありがとうございました。
＞ただ、結合セルでエラーが出たDim変数については、 ＞最初に結合セルがある旨を伝えるのを失念していた私のミスですので、 ＞教えて頂いたもので使えないか再考し、無理そうであれば他の方法を考えます。 これは矢張り 「以前のコードではエラーに成らず動いていた」 と言う事ですか？ ・・・同じコードに成っているはずなんですが。
この矛盾点から「私がすっかり忘れている」と書きました。 処理の方法や、コードの内容に付いては覚えているつもりですけどね。
マクロであっても、手作業であっても これまでも同じ処理をしてきていると思いますので その手順は、今回マクロを作る際に役に立つと思います。
まずはどの様な手順だったら良さそうなのか もう少し詰めて考えてみられてはどうでしょう。
＞この結合セルだけを避けてコードを動かしたい と言うのは、単なる思いつきの域を出ていない様に思えます。 しかし、そのイメージをもっと具体的にお伺いすれば すぐにでもコード化出来る所まで充分考えられているのかもしれません。
(HANA)
> 一旦ここで閉じさせていただきたく思います
といっているのに・・・少しくらい配慮できないのでしょうか？
(生徒)
そっか、こっちで更に伸びていくと、 別のところに移動して続けていたら、マルチポストみたいになるからね。 いや、実際はどうか知らんけど。 (ぱん)
＞一旦ここで閉じさせていただきたく思います。 は見ていますが ＞教えて頂いたもので使えないか再考し、無理そうであれば他の方法を考えます。 と書いてありますので、憶測ですがもう少し書いておきます。
詳細が分かりませんので、事例とは異なってくるかもしれませんが おそらく、他の方法を考えるのが良いと思います。
結合セルの有る状態で、再度マクロを実行したいと言う事は おそらく、追加されたデータも含め グループ毎にまとめて間に空行を入れ、正規の状態にしたい と言う事だと思います。
その際に、結合セルの上側だけで順番にし・下側だけで順番にする と言った事をすると、本来下側に有るべきデータで、上側に入力 されてしまったデータは、そのまま上側に残る事に成りますし その逆のパターンも有るかもしれません。
並べ替えが出来ると仮定したら、注釈が並べ替え後のどの位置に来れば良いのか 決定し、希望する位置に来るように数式の変更も必要に成ってきます。
そして、並べ替えには同じサイズの結合セルが必要ですので 結合セルを解除して、再度結合するか 他のセルも結合セルにして、並べ替え後に解除するか どちらかの操作になると思います。
現在のコードは 本来は、マクロで行を挿入して行けば良いが 簡易的に出来る方法として、集計機能を使っている と言うコードです。
この簡易さを残すために、他の部分の処理が煩雑になるのでは 本末転倒です。
ご希望の処理が出来るようなコードを作ろうと思うのなら 現在の 「毎回手作業でやるのは面倒だから、何か良いアイデアは無い物か」 の発想の作業をマクロ化したコード とはお別れして、 「どうせマクロがやるんだから、一つずつ確認させれば良いよね」 と言う考えで、処理の流れから再度考えてみられるのが良いと思います。
もしも、セルの結合が「中央に表示するため」と言うだけの事で有れば 文字の配置の横位置で「選択範囲内で中央」と言うのが有ります。 １．結合は止めて、これを使うことにする ２．並べ替えのキーにする数式を変更する の二つの変更で、現在のコードはそのまま使えるかもしれませんが。。。 一般的でない書式を使うと、その為に混乱が起きるかも知れません。
(HANA)
> 憶測ですがもう少し書いておきます
配慮できない人みたいですね。 たたみかけるようなことしなくても、質問者のレスくらい待てないのでしょうか？
(生徒)
(生徒)さんの見解は「閉じられたスレである」って事じゃないんですか？ だから先の書き込みをされたのでしょう？ いずれにしても、関係ない事だと思いますが。
念のために、ぺけぽんさんに向けて書いておきます。 ぺけぽんさんの最後の書き込み以降の、私の二つの書き込みは 「再考し」と言う文字に対する私の考えですので 読んで頂ければ良いと思いますが 読んでいただけなくても良いですし、返信も不要です。 何かの参考にして頂ければ良いと思って書いているだけですので。
(HANA)
> 関係ない事
そのようなことはないですね。一利用者として、不快ですから。 常連の先生なら、少しは待ってあげるという余裕ももってもらいたいですね。 先生の自己満足をひけらかされても困ってしまいます。
(生徒)
時間が経てば、記憶も薄れますからね。 なお、現段階で「ぺけぽんさんの返信待ち」と言う案件は、私は抱えておりません。 もしもぺけぽんさんが再開されることにして書き込みをされれば別ですが。 それに ＞一旦ここで閉じさせていただきたく思います。 って、書いてあると最初に指摘をしてきたのは(生徒)さんですよ。
まだ何か有りましたら、このスレでやる必要の有る事とも思えませんし 新スレでも立ててください。
(HANA)
> 現段階で「ぺけぽんさんの返信待ち」と言う案件は、私は抱えておりません。
だったら、なぜスレッドを伸ばすのかが理解できないのですよ。 いつまで、このスレッドを見て勉強したらいいの？ もしかして、HANA教授の単なるメモ？
(生徒)
【も・し・も ぺけぽんさんが戻ってこられた時に】 関係ない話で長くなっているといけませんので 返信を期待するのであれば、ご自身のスレを立てるなり 別の私が書き込める掲示板に誘導するなり、して下さい。
(HANA)
> 返信を期待するのであれば
言っていることがわかりません。 私が知りたいのは、
> 「再考し」と言う文字に対する私の考えですので > 読んで頂ければ良いと思いますが > 読んでいただけなくても良いですし、返信も不要です。 > 何かの参考にして頂ければ良いと思って書いているだけですので。
が何のために必要なのかということです。 つまり、何の参考にしたらいいのか、自己満足にしかすぎないような感じがしてなりません。 それだけ、しつこいという感じも否めないわけです。
(生徒)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.