[[20110828125314]]　『if・left関数をマクロ化』（ふるふる）　ページの最後に飛ぶ

『if・left関数をマクロ化』（ふるふる）

 winXP、Excel2003

 エクセルの「確認」シートに表があり、現在 1列目（区分列）に下記if関数が入れてあります。

 IF(LEFT(F2,3)="ＡＢＣ","ＡＢＣ",IF(LEFT(F2,2)="ＲＲ","ＲＲ","その他"))

 これをマクロ化したいと思い、見よう見まねで作成したものの
 A2セル以下すべてに｢その他」が入ってしまいます。
 お恥ずかしいのですが、マクロについて詳しくないので
 どこを直して良いか分かりません。

 ちなみに、行数は全部で12000行以上あり、今後増える可能性があります。

 どなたか、教えていただけませんでしょうか？

 どうぞよろしくお願い致します。

 【実行前】
 区分	　	メモ		作業番号	　　　　管理番号	　　　　タイトル	　　　　　内容
	　					-		あああ		ＡＢＣDD
	　	対象外				-		いいい		ＡＢＣGH
	　	対象外		26072		C1001		ううう		ＥＦＧGAA
	　			24869				えええ		ＫＬＭBB
		対象外		24887				えええ		ＲＲPLL
	　			24481				えええ		ＲＲKOL

 【実行後】
 区分	　	メモ		作業番号	　　　　管理番号	　　　　タイトル	　　　　　内容
 ＡＢＣ					　　　　 -		あああ		ＡＢＣDD
 ＡＢＣ	　　　　 対象外				-		いいい		ＡＢＣGH
 その他	　　　　 対象外		26072		C1001		ううう		ＥＦＧGAA
 その他			　　　　 24869				えええ		ＫＬＭBB
 ＲＲ		対象外		24887				えええ		ＲＲPLL
 ＲＲ				24481				えええ		ＲＲKOL

 Sub 区分入力マクロ()

 Dim i As Integer
 Dim CellData As Variant

 i = 1

 Application.ScreenUpdating = False

 Worksheets("確認").Range(Cells(2, 1), Cells(20000, 1)).Select
 Selection.ClearContents

 Do
 i = i + 1
 CellData = Worksheets("確認").Cells(i, 8)
 If CellData = "" Then
 Exit Do
 End If

 If CellData = Left(Cells(i, 6), 3) = "ＡＢＣ" Then
 Worksheets("確認").Cells(i, 1) = "ＡＢＣ"
 ElseIf CellData = Left(Cells(i, 6), 2) = "ＲＲ" Then
 Worksheets("確認").Cells(i, 1) = "ＲＲ"
 Else
 Worksheets("確認").Cells(i, 1) = "その他"
 End If

 Cells(i, 8)　と言うのは、何の列ですか？

 iが１から始まって
 CellData = Worksheets("確認").Cells(i, 8)
 これで、最初のH列の値が　CellData に入ります。

 If CellData = Left(Cells(i, 6), 3) = "ＡＢＣ" Then
 これは何が確認したいですか？
 Cells(i,6)の左から３文字が "ＡＢＣ"かどうかを確認するだけなら
 If Left(Cells(i, 6), 3) = "ＡＢＣ" Then
 ですよね。

 IF関数だったら =IF(H2=LEFT(F2,3)="ABC","ABC",・・・
 こんな事が書いてある事になります。

 IF(H2<>"",IF(LEFT(F2,3)="ABC","ABC",・・・
 ということか
 IF(AND(H2=LEFT(F2,3),LEFT(F2,3)="ABC"),"ABC",・・・
 と言うことか？

 最初の式からだとこんな感じ？
 '------
Sub Sample()
Dim i As Integer
i = 1
Application.ScreenUpdating = False
    With Worksheets("確認")
        .Range(Cells(2, 1), Cells(20000, 1)).ClearContents
        Do Until .Cells(i + 1, 8).Value = ""
            i = i + 1
            If Left(Cells(i, 6).Value, 3) = "ＡＢＣ" Then
                .Cells(i, 1).Value = "ＡＢＣ"
            ElseIf Left(Cells(i, 6).Value, 2) = "ＲＲ" Then
                .Cells(i, 1).Value = "ＲＲ"
            Else
                .Cells(i, 1).Value = "その他"
            End If
        Loop
    End With
Application.ScreenUpdating = True
End Sub
 '------

 Do Until .Cells(i + 1, 8).Value = ""
 H列のセルの値が「""」になるまで、一行ずつループ処理を繰り返します。

 (HANA)

 HANAさん

 ありがとうございました！
 HANAさんのおかげで処理が出来ました。
 あまりに初心者すぎて本当に申し訳ないです。

 ちなみに、Cells(i, 8)ですが、H列が空白になるまで処理を続ける
 というという意味で記載しましたが、見よう見まねだったため、
 分かり難かったですよね…すみません。

 この度はありがとうございました！

 いや、そういう事で良かったのなら
 作成されたコードは、IFの所が問題だっただけで
 CellData = Left(Cells(i, 6)～　の
 「CellData = 」を消してもらえれば、希望する結果が出たと思います。
 　　　途中までしか載っていない様なので、続きの記述によっては
 　　　うまくいかないのかもしれませんが。

 すると、CellData は
 　CellData = Worksheets("確認").Cells(i, 8)
 　If CellData = "" Then
 　　　Exit Do
 　End If
 の所で１回しか使わない事になるので、わざわざ変数に入れなくても良かったかもしれません。
 　If Worksheets("確認").Cells(i, 8) = "" Then
 　　　Exit Do
 　End If

 その他の点としては、Cells(i, 8)の何を確認したいのか明記しておくのが良いと思います。
 セルの値を見たいのか、セルの背景色を見たいのか、フォントを見たいのか？
 .Value とつけると「値」と言う事になります。
 　If Worksheets("確認").Cells(i, 8).Value = "" Then
 　　　Exit Do
 　End If

 それから、コードにはインデントをつける様にしておくと良いと思います。
 　If Worksheets("確認").Cells(i, 8).Value = "" Then
 　　　Exit Do
 　End If
       ↑ Exit Do は Ifの条件が成立した時に実行される。
 何処から何処までが一つのまとまりなのか　分かりやすく成ると思います。

 (HANA)

 HANAさん

 ありがとうございます！
 HANAさんのご説明がわかりやすくて本当に助かります。
 先生になって頂きたいぐらいです（T_T)

 忘れないように早速メモに残しておきました。

 本当に助かりました。ありがとうございました。

（ふるふる）

 そう言えば
 ＞行数は全部で12000行以上あり、今後増える可能性があります。
 と言う事ですが、処理速度はどうでしょうか？

 それに関してもう少し欲張ってしまって良いでしょうか？

 現在のコードは
 ＞Application.ScreenUpdating = False
 が入っています。
 これは、「その都度画面の更新」と言う無駄な処理を停止させるのが目的で
 エクセル君は画面の状態を気にしなくて良いので処理速度が上がります。

 それから、「セルへのアクセス」も時間がかかる処理の一つです。
 具体的な例としては、値を読みに行ったり、値を書き込みに行ったり。。。です。

 ふるふるさんの作業机と、データの表が　少し離れた所に有ると想定してみて下さい。
 H列の最初のデータが何かを確認して、机まで戻ってきます。
 空白でなかったので、F列の最初のデータが何かを確認しに行って、机まで戻って来ます。
 A列に何と書けば良いのか判定して、表の所まで行ってその値を書き込み戻ります。
 H列の次のデータ～H列の入力が無くなるまで、机と表の間を往復。。。
 。。。なんて、絶対イヤです。

 紙を持っていって、元の表と同じ様に写し取って、机まで戻ったら
 机の上でA列の値をどんどん判定して、写し取って来た表のA列に書き込んで行く。
 最後まで出来たら、表の所に行って　最初の列の所だけ切り取って、糊でペタリ。
 。。。これだと、2往復で済みます。

 データを写し取る紙を　tbl
 とした、サンプルコードを載せてみます。

 作製されたコードは、Do ～ Loop を使ったループ処理でしたが
 今回は For i ～ Next を使ってみます。

 '------
Sub Sample2()
Dim i As Long, mxr As Long
Dim tbl As Variant
    With Worksheets("確認")
    '===過去のA列の値を削除===
        .Range("A2", .Range("A2").End(xlDown)).ClearContents    'A2セル以降連続してデータが有る範囲のデータを削除
    '===今回のデータ量を調査===
        mxr = .Range("H" & Rows.Count).End(xlUp).Row    'H列の入力が有る最後の行番号を求める
    '===tblにデータを写し取る===
        tbl = .Range("A1:H" & mxr).Value
    '===判定して、tblの1列目に書き込む
        For i = 2 To mxr    'tblの2行目からmxr行目迄ループ
            If Left(tbl(i, 6), 3) = "ＡＢＣ" Then       '||　Cells(i, 6) で i行目の6列目　を表した様に
                tbl(i, 1) = "ＡＢＣ"                    '||　tbl(i, 6) で tblの中の i行目の6列目　を表す。
            ElseIf Left(tbl(i, 6), 2) = "ＲＲ" Then     '||　今回 tblには、A1セルからデータを写し取っているので
                tbl(i, 1) = "ＲＲ"                      '||　tbl(2, 6)には、F2セルと同じ値が入っている
            Else                                        '||　セルに書き出す時も、A1セルから書き出す予定なので
                tbl(i, 1) = "その他"                    '||　tbl(2, 1)は、A2セルの位置に成る。
            End If                                      '||
        Next
    '===セルに書き出す
        .Range("A1:A" & mxr).Value = tbl    'tblには 1～8列分データが入っているが、その内
    End With                                '1列目だけ書き出す。（左辺で1列分しか範囲が無いので。）
End Sub
 '------

 (HANA)

 HANAさん

 お礼が遅くなってしまい申し訳ありません。
 早速試したところ処理が早くなりました！！！
 前回のマクロだけでも十分便利になったのですが、
 今回のマクロで更に時間短縮できました。

 しかも、分かりやすい説明をつけて下さるので、本当に
 ありがたいです。

 ありがとうございました。

(ふるふる)

コメント返信：

[ 一覧(最新更新順) ]

[[20110828125314]] 『if・left関数をマクロ化』（ふるふる） ページの最後に飛ぶ

[[20110828125314]]　『if・left関数をマクロ化』（ふるふる）　ページの最後に飛ぶ