[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ユーザーフォームの転記がうまくいきません・その後』（peridot）
[[20120410105511]]
[[20120411160743]]
[[20120413102254]]
すみません、上の質問の関連で、どうしてもうまくいかない出来事があります。
ユーザーフォームの内容を「箱サンプル」シートに転記するコードを作っています。
★「箱サンプル」シートレイアウト
A B C D E F G H I J K L 1 作業簿_SK 日付 合計 2 記号 媒体名 件数 記号 媒体名 件数 記号 媒体名 件数 3 SK1 HY 82 SK2 KZ 2 SK KZ 2 4 SK1 FR 11 SK2 HY 4 SK HY 86 5 SK FR 11 ： 19 20 21 作業簿_KK 22 記号 媒体名 件数 記号 媒体名 件数 記号 媒体名 件数 23 VA1 KK 1516 VA2 KK 224 VA KK 1740 24 VE1 KK 9 VE2 KK 4 VE KK 13 ： 35 36 37 作業簿_その他 38 記号 媒体名 件数 記号 媒体名 件数 記号 媒体名 件数 39 VS1 10P 6192 VS2 10P 672 VS 10P 7872 40 VS3 10P 1008 ： 49
(最終行のA列) データ行終了
上記のようなシートがあり、「作業簿_SK」「作業簿_KK」「作業簿_その他」の3つの表に分かれています。 A～G列に当日のデータを入力し、J～L列にその値の合計を出します。 「記号」の最後1文字（数字）は枝番で、合計の際は枝番を除外した「記号」で集計します。 また、「合計」欄L列の各表の最後に、作業簿毎の件数の合計が関数で入力されています。
前の質問で、 ・ユーザーフォームからA～G列を入力 ・ユーザーフォームのコマンドボタンを押した時点で「合計」J～L列の値を集計 ・手入力でデータを変更した場合に備えて、シートモジュールのチェンジイベントで、 A～G列のセルの値が変更されるたびにJ～L列の値を再集計
という仕様にしました。
「箱サンプル」シートモジュール
Private Sub Worksheet_Change(ByVal Target As Range)
Dim w As Variant
Dim f As Long
Dim e As Long
Dim myCls As String
If skipChange Then Exit Sub
skipChange = True
For Each w In dicSample
If Right(w, 1) = "0" Then
myCls = Left(w, Len(w) - 1)
f = dicSample(w)(0)
e = dicSample(w)(1)
If Not Intersect(Target, Range("A" & f & ":G" & e)) Is Nothing Then
Call getSample
Call GetQtyInfo
Call QtyTotal(myCls)
Call getSample
Call GetQtyInfo
MsgBox myCls & " についての箱サンプルの合計処理を自動実行しました"
End If
End If
Next
skipChange = False
End Sub
それで、入力した後のA～G列で、「作業_**」や見出しの「転送記号」「媒体名」「件数」が 入っている行以外の、"SK1" "HY" "82" などの入力した値をクリアするコードを作っていたのですが、 最初に書いたコードがこれでした
Sub 件数クリア()
With Sheets("箱サンプル")
.Range("A3:C19").ClearContents
.Range("A23:C35").ClearContents
.Range("A39:C49").ClearContents
.Range("E3:G19").ClearContents
.Range("E23:G35").ClearContents
.Range("E39:G49").ClearContents
.Range("J3:L19").ClearContents
.Range("J23:L35").ClearContents
.Range("J39:L49").ClearContents
End With
End Sub
この時は、「○○についての箱サンプルの合計処理を自動実行しました」のメッセージがClearContentsの回数分 表示されますが、一応クリアできました。
しかし、上記のコードは3つの表の行数が固定されていた場合で、先日「行が足りない」ということになったので「作業簿_KK」の行数を2行増やしました。 それで
Sub 件数クリア2()
With Sheets("箱サンプル")
.Range("A3:C19").ClearContents
.Range("A23:C37").ClearContents
.Range("A39:C49").ClearContents
.Range("E3:G19").ClearContents
.Range("E23:G37").ClearContents
.Range("E39:G49").ClearContents
.Range("J3:L19").ClearContents
.Range("J23:L37").ClearContents
.Range("J39:L49").ClearContents
End With
End Sub
こうするとシートモジュールの
For Each w In dicSample
で「オブジェクトが必要です」のエラーになりました。
そもそも行数が変わるたびにコードの書き換えをするというのは運用的にどうかと思い、 下記のコードで試してみました。
Sub 箱サンプルシートクリア()
Dim i As Long Dim z As Long
With Sheets("箱サンプル")
z = .Cells(Rows.Count, 1).End(xlUp).Row - 1
For i = 3 To z
If .Cells(i, 1).Value <> "作業簿_KK" And .Cells(i, 1).Value <> "転送記号" And .Cells(i, 1).Value <> "作業簿_その他" Then
.Range("A3:G" & z).ClearContents
End If
Next i
End With
End Sub
こうすると「○○の合計処理を実行しました」のメッセージがセルの個数分出続ける上に、 何故かL列の合計関数まで消えてしまいます。 (G列までを指定したつもりなんですが…）
「○○についての合計処理を実行しました」のメッセージが出るのは仕方ないとして、せめて1回だけにして、 データの「値」（見出しや関数を除いて）をクリアするにはどうしたらいいでしょうか。
前回（ぶらっと）様にお教えいただいたdictionaryのメモを見ながら試してもダメでした…
申し訳ございません、よろしくお願いします。
ちょっと、今日は眠いので明日、じっくり読んでみるけど、提供したコード自体は、 箱サンプルに各分類コードごとに用意してある行数は自動的に判断している。
なので、 ・マクロを無効にしてブックを開き ・箱サンプルシートをメンテ（マクロ無効なので、実行されず、結果としてメッセージもでない） ・閉じる
これで、あらためて、マクロを有効にして開く。
こうすればいいと思うんだけどな。
(ぶらっと)
あるいは、マクロが有効になって開かれたとしても ・ＶＢＥ画面を表示 ・表示->イミディエイトウィンドウ ここで、Application.EnableEvents = False とタイプしてエンター ・で、箱サンプルシートをメンテ。（イベントが発生しないのでマクロも動かない） ・メンテ後、イミディエイトウィンドウ で Application.EnableEvents = True とタイプしてエンター。
こうしてから、通常の処理を開始してもいい。
(ぶらっと)
（ぶらっと）様
ありがとうございます。
「マクロを無効にしてブックを開き～」の状態で、下記のコードを書いて保存、再度ブックを開いて実行しました
Sub 箱サンプルクリア()
Dim i As Long Dim j As Long Dim x As Long Dim y As Long Dim z As Long Dim s1 As Long Dim s2 As Long Dim s3 As Long
With Sheets("箱サンプル")
z = .Cells(Rows.Count, 1).End(xlUp).Row - 1 '作業簿_その他最終行
For i = 1 To z
If .Cells(i, 1).Value = "作業簿_SK" Then
s1 = i + 2 '作業簿_SK開始行
ElseIf .Cells(i, 1).Value = "作業簿_KK" Then
s2 = i + 2 '作業簿_KK開始行
x = i - 2 '作業簿_SK最終行
ElseIf .Cells(i, 1).Value = "作業簿_その他" Then
s3 = i + 2 '作業簿_その他開始行
y = i - 2 '作業簿_KK最終行
End If
Next i
.Range("A" & s1 & ":G" & x).ClearContents
.Range("A" & s2 & ":G" & y).ClearContents
.Range("A" & s3 & ":G" & z).ClearContents
End With
End Sub
「合計処理を～」のメッセージが3回出ますが、これなら運用で大丈夫そうです。 ありがとうございました！
(peridot)
解決ということだけど、まだまだ気になるところがあるので。
あらためて今回の質問を読んでみた。
まず、今までもそうだったけど、 >For Each w In dicSample で「オブジェクトが必要です」のエラーになりました。 この種のコメントがたびたびでているね。 説明しているけど、共通で使うDictionary他、ブックが開かれた時に、Preparation で 変数をセットしている。なので、プロジェクトがリセットされた状況で再開するには ブックをとして開きなおす または 手動で Preparation を実行してから処理を継続する。 これが必要。 (なので、今回のようにシートをメンテナンスする場合、マクロ無効でメンテ->閉じて開きなおす これはいいんだけど、２番目に提示した、マクロ有効のままの処理の場合は、メンテ後、必ず Preparation を実行してね。（新レイアウト情報を取り込む必要があるので）
次に、３回メッセージがでる件。これからの追加でもあるかもしれないので。 手作業であれ、マクロからの更新であれ、該当の領域のセルが変更されれば合計処理が自動的に走り、メッセージもでる。 コードからの変更時、これを「意図的に止める」ためには、変更前に skipChange = True、変更後に skipChange = False。 こうしてね。
で、そのクリアだけど、レイアウト情報はコード内に持っているので、以下のコードにしておけば、自動的にクリア されるよ。D列やF列も、一緒にクリアしていいなら 箱サンプルクリアA でＯＫ。D,F列はさわりたくないなら 箱サンプルクリアB を。（テストしてないけど、たぶん大丈夫）
Sub 箱サンプルクリアA()
Dim w As Variant
Dim f As Long
Dim e As Long
skipChange = True
For Each w In dicSample
If Right(w, 1) = "0" Then
f = dicSample(w)(0)
e = dicSample(w)(1)
Sheets("箱サンプル").Range("A" & f & ":L" & e).ClearContents
End If
Next
skipChange = False
End Sub
Sub 箱サンプルクリアB()
Dim w As Variant
Dim f As Long
Dim e As Long
skipChange = True
For Each w In dicSample
If Right(w, 1) = "0" Then
f = dicSample(w)(0)
e = dicSample(w)(1)
Sheets("箱サンプル").Range("A" & f & ":C" & e).ClearContents
Sheets("箱サンプル").Range("E" & f & ":G" & e).ClearContents
Sheets("箱サンプル").Range("J" & f & ":L" & e).ClearContents
End If
Next
skipChange = False
End Sub
(ぶらっと)
（ぶらっと）様
引き続きのフォローありがとうございます。 Preparation を実行する、今までも何度か言われていたのに理解していませんでした…申し訳ございません…
ご提示いただいたコード、箱サンプルクリアAでほぼ良いのですが、L列の「作業簿_KK」の表の最後にある SUM関数が消えてしまいます…
SUM関数はL列の「作業簿_SK」「作業簿_KK」「作業簿_その他」の表（最終行ではない）の下のセルにあります。 あえて指定するなら、 ・「作業簿_SK」のSUM関数の行＝A列「作業簿_KK」の見出しがある1行上 ・「作業簿_KK」のSUM関数の行＝A列「作業簿_その他」の見出しがある1行上 ・「作業簿_その他」のSUM関数の行＝A列「データ最終行」の2行上
になります。
★追加 すみません、私の操作ミスでした… SUM関数ちゃんと残ります。 ありがとうございました
(peridot)
>SUM関数ちゃんと残ります。
それは祝着・・・・なんだけど、
>「作業簿_その他」のSUM関数の行＝A列「データ最終行」の2行上
確か、ここはデータ行だった記憶が。少なくともコードとしては、そう理解して処理しているよ。 もし、ここに計算式があるならクリアされるはず。 計算式が「データ最終行」の【１行上】ならいいんだけど？
もし、２行上ということなら、Preparation のロジックを直さなきゃいけないんだけど。
(ぶらっと)
（ぶらっと）様
たびたび申し訳ございません…ご指摘の通り、「データ最終行」の１行上でした… （私の見間違えです…）
本当に申し訳ございませんでした
(peridot)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.