[[20171213102223]] 『リストボックスからセル操作』(ナナ) ページの最後に飛ぶ

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

 

『リストボックスからセル操作』(ナナ)

いつもお世話になっております。
リストボックスを使ったループ処理がうまくいかないのでご教示お願いします。

sheet1のデータをsheet2に写す処理をリストボックスを使って行おうとしています。
リストボックスにsheet1のデータを表示させて、チェックボックスを使ってチェックされた項目の行をsheet2に写すマクロを組みました。

    For i = 0 To ListBox1.ListCount - 1

        If ListBox1.Selected(i) = True Then
        Sheets("Sheet1").Rows(i+2).Cut
        Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)  
        End If
    Next i

このコードですと最初にチェックされた項目は切り取ることができるのですが、それ以降のチェック項目には反応しません。逆方向からのループに変えてもうまく反応しないのですが、なぜでしょうか。ちなみにコピーペーストだとうまくループします。
よろしくお願いします。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


上の行から順番に処理しているため、Cutによって行が削除されてしまうと、行番号が詰まりますよね? そのため、以降は行位置がずれてしまうのです。連続削除のコーディングで、よく見るバグです。

対応方法は、下の行からループさせる事です。 これなら、行を削除しても後の処理はそこを使わないので、問題が起こりません。 ただし、貼り付け先は上から順のままなので、並びが逆転してしまいます。 全て移動した後に、データを並び替える処理も追加すると良いでしょう。
(???) 2017/12/13(水) 10:38


別案も書いておくと、Cutを止めてCopyにしておき、全てコピー後に、逆順でループしつつ、行削除する、という手もあります。この場合は、コピー後の順番は元のままになります。
(???) 2017/12/13(水) 10:41

早速の返信ありがとうございます。

>対応方法は、下の行からループさせる事です。
For i = ListBox1.ListCount - 1 To 0 Step -1

こういうことでしょうか?逆方向からループさせても一つのチェックボックスしか反応しません。
(ナナ) 2017/12/13(水) 10:46


 Cutは値を切り取って張り付けるものなので貼り付け後はそのセルそのものが削除されるのではなく値がクリアされるだけだと思うのだが。
 実際、私のところ(Windows7+Excel2010)ではきちんと動作しているが。
 (リストボックスで指定したセルはクリアされSheet2にコピーされている)
(ねむねむ) 2017/12/13(水) 10:47

 できていると思ったら1回目はうまくいったが2回目以降で正しく動かないようだ。
(ねむねむ) 2017/12/13(水) 10:50

ねむねむ様
cutは値を切り取って張り付けるものなので貼り付け後はそのセルそのものが削除されるのではなく値がクリアされるだけだと思うのだが。 おっしゃる通りです。行削除のループ行番号が詰まる関係でうまくいかないのだと思い、一旦値をクリアしてから空白のセルを判定させて行削除しようと考えて試行錯誤の状態のコードです。
(ナナ) 2017/12/13(水) 10:52

 Sheet1でデータは何行目から何行目までにあるのだろうか?
 またリストボックスに表示させるのにはどうやって指定しているのだろうか?
(ねむねむ) 2017/12/13(水) 10:56

あ、そうか、Cutなので、行は詰まらないですね。Copyはうまくいくとの事で、勘違いしてしまいました。私の案は、見なかった事にしてください。

そうなると、チェックボックスと言っている部分が問題でしょうか。 コードでは、ListBoxの項目が選択されている事をチェックして処理していますが、チェックボックスは使っていませんよね?
(???) 2017/12/13(水) 11:00


 もし、リストボックスのプロパティのListFillRangeで指定している場合、先頭のセルを選択していると、先頭のセルの異動後、
 ListFillRangeの範囲からその先頭のセルが外されてしまうようだ。
 それがなにか影響しているということは無いだろうか?
(ねむねむ) 2017/12/13(水) 11:01

ねむねむ様
>Sheet1でデータは何行目から何行目までにあるのだろうか?
これはその都度データ量が変わりますが数百行を想定しています。

>またリストボックスに表示させるのにはどうやって指定しているのだろうか?
.RowSource = "Sheet1!A1:C" & Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

こんな感じです。 
(ナナ) 2017/12/13(水) 11:04


???様
>そうなると、チェックボックスと言っている部分が問題でしょうか。 コードでは、ListBoxの項目が選択されている事をチェックして処理していますが、チェックボックスは使っていませんよね?
チェックボックスは
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption

で表示しています。
(ナナ) 2017/12/13(水) 11:06


ねむねむ様
ListFillRangeはちょっと使ったことがないのでわからないのですが、RowSourceで表示させてるので問題ないですよね?
(ナナ) 2017/12/13(水) 11:08

 ちょっとそこらへんは不安なので???さんの提案した
 >別案も書いておくと、Cutを止めてCopyにしておき、全てコピー後に、逆順でループしつつ、行削除する、という手もあります。この場合は、コピー後の順番は元のままになります。
 でおこなってはどうか?
(ねむねむ) 2017/12/13(水) 11:09

ユーザーフォームに貼った、ActiveXコントロールだったのですね。 RowSourceの対象が、行削除の範囲内になっていて、問題発生してそう。
(???) 2017/12/13(水) 11:15

ねむねむ様
>ちょっとそこらへんは不安なので???さんの提案した
 >別案も書いておくと、Cutを止めてCopyにしておき、全てコピー後に、逆順でループしつつ、行削除する、という手もあります。この場合は、コピー後の順番は元のままになります。
 でおこなってはどうか?

もちろん試しました。行を削除する逆回転ループでも反応するのは一つ目のチェックボックスのみとなります。

(ナナ) 2017/12/13(水) 11:16


 あと、ちょっと気になったのはリストボックスのインデックスは0始まりということは
 >Sheets("Sheet1").Rows(i+2).Cut
 の部分iに足すのは1ではないのだろうか?
(ねむねむ) 2017/12/13(水) 11:18

???様
>ユーザーフォームに貼った、ActiveXコントロールだったのですね。 RowSourceの対象が、行削除の範囲内になっていて、問題発生してそう。

行削除で問題発生するのはなんとなく想定していましたが、値がクリアされるだけでループがうまくいかないとは思いませんでしたが、RowSourceの対象の関係でしょ
うか。
(ナナ) 2017/12/13(水) 11:19


ねむねむ様
>あと、ちょっと気になったのはリストボックスのインデックスは0始まりということは
 >Sheets("Sheet1").Rows(i+2).Cut
 の部分iに足すのは1ではないのだろうか?

説明不足で申し訳ありませんが、一行目は見出しのため+1インデックスの始まりは0で+1
合計+2としています。
(ナナ) 2017/12/13(水) 11:21


 そもそもリストボックスに表示するのが
 >.RowSource = "Sheet1!A1:C" & Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row 
 と1行目からになっているということはリストボックスに見出し行も表示されていないか?
(ねむねむ) 2017/12/13(水) 11:29

>そもそもリストボックスに表示するのが
 >.RowSource = "Sheet1!A1:C" & Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row 
 と1行目からになっているということはリストボックスに見出し行も表示されていないか?

.ColumnHeads = True
で見出しを作っています。
ステップインでiの変数値を確認しても値自体にはおかしいところが見当たらないんですよね。
一つ目のチェックボックスはうまく作動しても、それ以降のループ時のiの値は正常です。
(ナナ) 2017/12/13(水) 11:35


 状況がわかったような気がする。
 コピーを行うようにしてさらにその時に行数を配列変数にでも記録しておき2回目のループでは
 記録した行を削除するようにしてみてくれ。
 どうもRowSourceの対象のセルの内容に変化があるとリストが更新されチェックが外れてしまうようだ。
(ねむねむ) 2017/12/13(水) 11:47

 あ、行の削除は逆順で。
(ねむねむ) 2017/12/13(水) 11:50

ねむねむ様
 >どうもRowSourceの対象のセルの内容に変化があるとリストが更新されチェックが外れてしまうようだ。

チェックが外れてしまうんですね…

>コピーを行うようにしてさらにその時に行数を配列変数にでも記録しておき2回目のループでは

 記録した行を削除するようにしてみてくれ。

非常に申し訳ないのですが、配列変数に記録するというコードの書き方がわからないのですが、どのようにすればいいですか?

(ナナ) 2017/12/13(水) 11:54


RowSourceを使わずにアイテムセットするか、現状のままにして、Cut処理前にどれがチェックされたか調べて、この情報を元にCutするよう直すか、ですかねぇ。 以下に、後者の例を書きます。

 Private Sub CommandButton1_Click()
    Dim AR As Object
    Dim i As Long

    Set AR = CreateObject("System.Collections.ArrayList")

    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            AR.Add i
        End If
    Next i

    For i = 0 To AR.Count - 1
        Sheets("Sheet1").Rows(AR(i) + 2).Cut Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i

    For i = AR.Count - 1 To 0 Step -1
        Sheets("Sheet1").Rows(AR(i) + 2).Delete
    Next i

    ListBox1.RowSource = "Sheet1!A2:C" & Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

    Set AR = Nothing
 End Sub
(???) 2017/12/13(水) 11:59

???様
すっすごい!行番号の変数をARに格納して行削除するんですね。
素人には難しく感じます。
これでうまくいきました。ありがとうございます。

(ナナ) 2017/12/13(水) 12:07


コメント返信:

[ 一覧(最新更新順) ]


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