Do~Loopを用いたマクロ

Question

いつもお世話になっております。
下記URLの前回の質問から引き続きの内容になります。

http://oshiete.goo.ne.jp/qa/9628242.html

前回頂いたヒントを元にいろいろと行ってみたのですが行き詰ってしまいました

---------------------------------------------------------------------------

Sub テスト()

Dim i As Long
Dim j As Long
Dim k As Long
Dim SheetA As Worksheet, SheetB As Worksheet
Dim P As Range
Dim Q As Range

Worksheets(1).Select

With Worksheets(1)
i = 1
Do Until Cells(i, 2) = ""
Worksheets(1).Cells(i, 2).Select
ActiveCell.Offset(0, -1).Resize(12, 7).Copy

  Do

  j = 4
  Worksheets(j).Select
  k = 1
    Do
    Set SheetA = ThisWorkbook.Worksheets(1)
    Set SheetB = ThisWorkbook.Worksheets(j)
    Set P = SheetA.Cells(i, 2)
    Set Q = SheetB.Cells(k, 2)
     If P = Q Then
     Worksheets(j).Cells(k, 2).Select
     ActiveCell.Offset(-1, 1).PasteSpecial Paste:=xlPasteValues
     
     GoTo Label1
      
     Else
      k = k + 1

     End If
    Loop Until k = 100
  j = j + 1
  Loop Until j = Worksheets.Count
Label1:
i = i + 12
Loop

End With
End Sub

------------------------------------------------------------------------------

結論から言うと、最初の貼付が終わった段階でマクロがとまってしまいます。
私の目論見としては、貼付が出来た段階でLabel1にとび、
今度は一枚目のワークシートの１２行飛ばした行から、
同じように処理をすると考えていました。
根本的なミスや勘違いなどありますでしょうか？
ご教授いただけたら幸いです。

宜しくお願いいたします。

WindFaller · Accepted Answer

#3の回答者です。

私のコードをみていただき、なおも理解していただいてありがとうございます。あくまでも、九兵衛様が書いていただいコードを見ながら、想像を膨らましたものですので、何ぶんにも行き違いはやむを得ないと思いました。

それと、多くの回答者の皆さんは、サブルーチンを置かない書き方にするのですが、（掲示板の回答者としての)長い間の習慣で、全部を直すのが億劫なので、いつのまにか、そのような書き方になってしまいました。分けて書くと修正し易いのです。

(1) >上記部分は空白が見つかった段階で処理を終えるためのものでしょうか？
そのとおりですが、
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row Step 12
のように、12行ずつ飛んでいくことを考えて、このコードで、A列の最後まで処理するようにはなっているものの、データがない時には、もしかしたら、何か加えるものがあるのとか、以下のようなIf 構文では、なんとなく物足りなさを感じました。

If .Cells(i, 2).Value <> "" Then
　処理
End If

２）>この論理的にというのは２から始まる前提ということでしょうか？
まず、「論理的」というのは、コンピュータで使う用語で、「コード上」という意味ですが、多少の違和感が感じましたら、お許しください。

If Worksheets(j).Cells(k, 2).Value = t Then　
　　　Rng.Copy
　　Worksheets(j).Cells(k - 1, 3).PasteSpecial Paste:=xlPasteValues

Cells の引数は、マイナス値がいれられません。以下のように、(k -1, 3) と、 行が一つ戻ります関係で、k =1 を入れると、k-1 =0 で、Cells(0, 3)となり、エラーが出てしまいます。

今、VBAで、私たちにのしかかる問題として「英語力」というものがあります。私の使っているのは、Excel 2013 ですが、ヘルプはでません。英語版のヘルプもいれましたが、ちゃんと出てこないのです。

sc348253 · Answer

ご指摘の通り、自分の質問を見返し、伝え方がとても下手だと思いました
どのように相手に伝えるかということを勉強すべきかもしれません。
大きな反省点です。
→国語力ですね！国語の読解力とか表現力ですね！好きな本とか新聞とかの活字に触れる事、表現を真似る事、漢字(今でしょう！と言う東大出の林先生の出てる番組(ネオ…⁇)いいね！)を覚える事かな！ (私は、現代国語好きです。論理性とかね！)

WindFaller · Answer

返事をいただきありがとうございます。
>今回のケースではどのようにするのがベストなのか
>もし可能であればご教授願いたいです。

ベストというものは、言えないけれども、ある程度のルールというものがありますから、それには従ったほうがよいと思います。

「エクセルExcel大事典」ここの内容は古いです。
http://home.att.ne.jp/zeta/gen/excel/
後は、最後に載せているアドレスを調べてみてください。

それと、元のコード自体直すことは、申し訳ないのですが、、私には無理です。時々、そういうことができる人がいますが、単に、元のコードに想像を膨らませて、サンプルコードを作るだけです。

私自身の書き方の習慣で、こういう時は、サブルーチンにしてしまいます。ループを3つも重ねると、何がなんだか、分からなくなりそうです。

'//これは、同じブックでの処理を元にしたもので、あくまでも、基本データは、左端のシート(Worksheets(1))にあるものとします。
そうでない時は、サブルーチン側のループで、同じシート名は除外するように作らなければなりません。

'//標準モジュール
Sub テスト1r()　'あくまでもこちらの想像です
　Dim Rng As Range
　Dim SheetA As Worksheet
　Dim t As Variant, i As Long
　
　Set SheetA = Worksheets("基本データ")
　With SheetA
　　'1行目からか？
　　For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row Step 12
　　　If .Cells(i, 2).Value <> "" Then
　　　　t = .Cells(i, 2).Value　'比較データを確保
　　　　Set Rng = .Cells(i, 3).Resize(12, 7)　'コピーの範囲（コピーはしていません)
　　　　Call PasteMacro(Rng, t)　'サブルーチン
　　　　Set Rng = Nothing
　　　Else
　　　'　Exit Sub  'コメントブロックを外すと、データのないところで止まります。
　　　End If
　　Next i
　 End With
End Sub
Sub PasteMacro(Rng As Range, t As Variant) 'サブルーチンマクロ
Dim k As Long, j As Long
　Application.ScreenUpdating = False
　For j = 4 To Worksheets.Count 
　　'kは、論理的に2から始まります。
　　For k = 2 To 100
        'データの比較
　　　If Worksheets(j).Cells(k, 2).Value = t Then　
　　　　Rng.Copy
　　　　Worksheets(j).Cells(k - 1, 3).PasteSpecial Paste:=xlPasteValues
　　　End If
　　Next k
　Next j
　Application.ScreenUpdating = True
End Sub

'//

「VBA コードのパフォーマンスを向上させる」
という検索語で、以前、msdn が出てきましたが、今はないようです。

今、ネットで調べてみましたら、これは新しい書き方です。
それも、効率的だと思います。
http://mousouprogrammer.blogspot.jp/2012/11/vbavba.html
（何が新しいとか古いとかいうと、ひとつは、変数名の自由度と、変数の位置。VB.Net によって、VBAも何らかの影響を受けています。そういう現れです)

前回書いたことは、ここにありました。
VBA コードを最適化する
https://msdn.microsoft.com/ja-jp/library/Aa189065

「•ループを検討して、メモリの消費量が多い演算が必要以上に繰り返されていないかどうかを調べます。たとえば、ループ外に移動できる変数がないか。また、ループ外で実行可能な変換プロシージャを毎回ループで実行していないか、などです。」

難しい話をしてすみません。私は、また関係のない話を書くと怒られそうですが、少しメッセージを残します。

私は、いろんな人のコードをみていますが、ご質問者さんは、ふつうの人なら、もうとうにギブアップしているようなコードを完成まで持っていけるというのは、お世辞なく、かなりの才能のある方だと思っています。たぶん、私の書いたものは、すぐに解読できるだろうと思います。

今、ExcelVBAも、新しい技術が、江戸時代末期の黒船のように押し迫ってきています。XML(2007以上), XLL, COMと、VS2015から、VSTOが無料化というのは、とんでもなく大変な時代に突入したと思っています。

PCを扱える人生が20年ぐらいあるのでしたら、ぜひ挑戦してみてください。私のPC師匠（OSとハードが中心)は、会社を定年でやめてから、プロの講師になりました。70歳過ぎても、まったく体力の衰えなくやっています。そこにいくと、私は、プログラミングというよりも、集中力がどんどん落ちてきていて、一日の内で本当に使える時間が数時間です。

頑張ってみてください。

sc348253 · Answer

提案ですが、プログラムのひと固まりには、作成してから時間が経つとわからなくなりますので、コメントを挿入してください！

プログラムは、書いたものが、ベストとは限りません！短くても時間かかる場合ありますし、長くても、処理時間が短い場合もあります。

また、同じプログラムでも、パソコンによって動かなかったりする場合もありますので、その場合は、微妙に変えてください！

以上 私の体験です。

WindFaller · Answer

こんにちは。

コードが通る通らないというよりも、本当は、まず、基本的な書き方から学ばなくてはならないかもしれません。今のままでは、ほとんどの人は理解できません。前回も同様だったと思います。

ご質問者さんは、VBAのセンスはなかなかおありのようで、テクニックもご存知のようですが、今は自分で自分のコードを分からなくさせているように思えます。

端的にいうと、「これ」「あれ」「それ」という言葉では、他人には分からないということ。したがって、その状態で書かれたマクロは非常に不安定だと思います。

具体的な内容を、まとめておきます。
まず、Workbook 1つで作業するなら、暗黙的に、Worksheet から書いて構いません。もし複数あるなら、かならず、wb1, wb2 などの変数に置き換えた方が分かりすいです。With ステートメントなどを利用すると良いです。

次に、Worksheets(1) というのは、左から1番目のワークシートという意味ですから、順番が変わってしまったら、分からなくなってしまいますので、VBAでは、名称をいれることをお勧めします。ただ、一律同じようなシートの処理の場合は不要な場合もあります。

オブジェクトを計数的なコントロールできるものは、Do - Loop よりも、For i =数字　To 最後の数のほうが管理しやすいと思います。

後、基本的には、ループの中で、同じものを繰り返しオブジェクトを変数に入れるというのは、例えば、Internetサイトのように、相手側が動的に変化するものでしかしません。今回、シートでは同じものが繰り返しオブジェクトに登録されています。

言うまでもなく、Select は、初期のオブジェクトの選択する時だけぐらいで、Copy などには、Select を用いることはありません。

Worksheets(1).Activate
Cells(i, 2).Select
ActiveCell.Offset(0, -1).Resize(12, 7).Copy
は、おそらくは、以下で通るのではないかと思われます。（Worksheets(1)が明確ならばですが)
Worksheets(1).Cells(i,1).Resize(12, 7).Copy

もし、自分なりの決め事を作った時に、自分ではマクロで表現することが出来ない場合は、その内容をコメントにして残しておくのも、ひとつの手なのです。
例えば、
　'このマクロは、「『会計ブック』のシート1を最初に出しておく」
と但書を書いておきます。

別の回答でも書きましたが、マクロコードは、半年経てば、他人が書いたもの同然になってしまいます。まして、1年、2年になると、さっぱり書いたことさえ忘れていることもあります。だから、分かる手立てを残しておくことも大事です。

Do~Loopを用いたマクロ

#3の回答者です。

ご指摘の通り、自分の質問を見返し、伝え方がとても下手だと思いました

返事をいただきありがとうございます。

提案ですが、プログラムのひと固まりには、作成してから時間が経つとわからなくなりますので、コメントを挿入してください！

こんにちは。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング