Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Excel 2013)
56行ずつ"N"と"U"が入力したい
投稿日時: 20/07/29 18:19:50
投稿者: vaioyuki

連投失礼します。
午前中に「112行でワンセットのデータを回数分ワンアップさせたい」で解決させてもらいました。
https://www.moug.net/faq/viewtopic.php?t=79695
 
元のExcelファイルのA列に
=IF(OR(OFFSET(サマリ!$A$2,F2,0)="",ISNA(D2)),"",B2&","&OFFSET(サマリ!$A$2,F2,0)&OFFSET(サマリ!$B$2,F2,0)&",N,"&D2)
という数式が入ってまして、これもまた下記のマクロを考えました。
 

With flowSh
Set summA = summSh.Range("A2")
Set summB = summSh.Range("B2")

    r_cntF = .Range("B1").CurrentRegion.Rows.Count

    For i = 2 To r_cntF
        If summA.Offset(.Cells(i, 6), 0) = "" Or .Cells(i, 4) = "" Then
            FlowB = ""
        Else
            FlowB = .Cells(i, 2)
        End If

        .Cells(i, 1) = FlowB & summA.Offset(.Cells(i, 6), 0) & summB.Offset(.Cells(i, 6), 0) & "N" & .Cells(i, 4)
    Next i
End With

 
ここでまた112行でワンセット問題が出てきまして。。。
今度は半分の56行ごとに"N"と"U"が入れ替わるということを忘れてました。。。
 
私なりに別列で56行ごとに"N"と"U"が出ないか考えてみましたが、
 
    r_cntNU = .Cells(Rows.Count, "I").End(xlUp).Row
    
    
    For i = 1 To countD
        For j = 2 To r_cntF Step setHRows
            If i = 1 Then
                .Cells(j, 9).Resize(setRows).Value = "N"
            Else
                .Cells(j, 9).Resize(setRows).Value = "U"
            End If
        Next j
    Next i

 
全て"U"になりました。泣
思いついたのは常に最終行を求めて回数分繰り返す。。。
 
何度も申し訳ありませんが、
またまたよろしくお願いします。

回答
投稿日時: 20/07/30 08:09:16
投稿者: simple

前のスレッドでの肝心な

    j = 0
    For i = 2 To r_cnt Step setRows
        .Cells(i, 6).Resize(setRows).Value = j
        j = j + 1
    Next
の話をすっ飛ばすのは何故ですか?
 
これのアナロジーで、
・繰り返しのステップを考慮するのと
・j=j+1を、 UとNの繰り返しに変更する
だけだと思いますが。
簡単なIfステートメントで書けるはずですね。

回答
投稿日時: 20/07/30 09:36:01
投稿者: Suzu

前回のスレッドの当方の意図としては
 

Sub 実行()
    Const cmyRows As Long = 112

    Dim countD As Long
    Dim r_cnt As Long
    Dim i As Long

    countD = WorksheetFunction.CountA(Worksheets("サマリ").Columns(1)) - 1

    With Worksheets("フロー")
        For i = 1 To countD
            r_cnt = .Range("B1").CurrentRegion.Rows.Count
            .Range("B2").Resize(cmyRows).Copy .Cells(r_cnt + 1, 2)
            .Cells(r_cnt + 1, 2).Resize(cmyRows).Offset(0, 4) = i - 1
        Next i
    End With

End Sub

 
この様なイメージでした。
 
 
A列の件に関して、別プロシージャにせずにひとつにしてはいかがですか?
 
また、A2〜A113 では、NとUが入れ替わった数式が既に入っているのではありませんか?
そうであれば、
.Range("B2").Resize(cmyRows).Copy .Cells(r_cnt + 1, 2)
の応用で出来ますよね?
 
A2〜A113 に数式が、入っていないとしても 「112」が固定であるならば、
VBAで「数式」を 与えるのであれば VBA内 で 「N」と「U」を判断し処理する必要はありません。
「数式内」で その行を取得し 偶数行/奇数行で、「N」と「U」を分岐すればよいのでは?
 
関数だと、=IF(MOD(ROW(),2)=0,",N,",",U,")
 
これをVBAで数式として代入します。
 
ただ、数式内で参照する「行」を変えないといけないので。。。数式を、R1C1形式に変えます。
 
 
Sub 実行()
    Const cmyRows As Long = 112

    Dim countD As Long
    Dim r_cnt As Long
    Dim i As Long

    countD = WorksheetFunction.CountA(Worksheets("サマリ").Columns(1)) - 1

    With Worksheets("フロー")
        For i = 1 To countD
            r_cnt = .Range("B1").CurrentRegion.Rows.Count
            .Range("B2").Resize(cmyRows).Copy .Cells(r_cnt + 1, 2)

            .Cells(r_cnt + 1, 1).Resize(cmyRows).FormulaR1C1 = _
                "=IF(OR(OFFSET(サマリ!R2C1,RC[5],0)="""",ISNA(RC[2])),"""",RC2&"",""&OFFSET(サマリ!R2C1,RC[5],0)&OFFSET(サマリ!R2C2,RC[5],0)&IF(MOD(ROW(),2)=0,"",N,"","",U,"")&RC[3])"
            .Cells(r_cnt + 1, 6).Resize(cmyRows) = i - 1
        Next i
    End With

End Sub

 ※ .Offset(0, 4) は、Cells の Column を4→6)に変えています

投稿日時: 20/07/30 10:52:45
投稿者: vaioyuki

ありがとうございます。
 
すっ飛ばしたつもりは全くありません。苦笑
ただ、「j=0」でひとつずつカウントアップしてくる仕組みはわかったのですが、
これをNとUに置き換えることがわからない、
そしてNとUを交互に繰り返す処理が思いつきませんでした。
 
考えてもらった、0からワンアップする列を利用して「2で割れるもの」はUにする。。。のような考えも思いついたのですが、
あまりにも式が長くなってしまうんじゃないかと悩んでいます。
 
教えていただいたものを私なりに考えて、
 

    For i = 1 To countD 'データ数
        For j = 2 To r_cntF Step setHRows '最終行までを52ごとに ←ここで悩む
            If i = 1 Then 'ここで前回教えていただいた0からワンアップしてくる列を参照し、2で割り切れるものはUにする?
                .Cells(j, 9).Resize(setRows).Value = "N"
            Else
                .Cells(j, 9).Resize(setRows).Value = "U"
            End If
        Next j
    Next i

 
こんな感じで悩んでいています。。。と書き込もうとしたらSuzuさんのコメントを頂いたので、少し自分で咀嚼してみます。
またご教授お願いします。

投稿日時: 20/07/30 11:24:53
投稿者: vaioyuki

今さら感があるかもしれませんが、
現在のシートのコードです。
 

With flowSh

 	'B列は112行は固定にしています。それをコピーしてデータ分繰り返す処理
    For i = 1 To countD
        r_cnt = .Range("B1").CurrentRegion.Rows.Count
        .Range("B2:B113").Copy
        .Cells(r_cnt + 1, 2).PasteSpecial xlPasteValues
    Next i

    r_cntF = .Range("B1").CurrentRegion.Rows.Count
    r_cntNU = .Cells(Rows.Count, "I").End(xlUp).Row
    
    'A列の処理で迷っているところ
    For i = 1 To countD
        For j = 2 To r_cntF Step setHRowsF
            If i = 1 Then
                .Cells(j, 9).Resize(setRowsF).Value = "N"
            Else
                .Cells(j, 9).Resize(setRowsF).Value = "U"
            End If
        Next j
    Next i

	'教えていただいたF列の処理
    j = 0
    For i = 2 To r_cntF Step setRowsF
        .Cells(i, 6).Resize(setRowsF).Value = j
        j = j + 1
    Next

	'C列の処理。実際は行によってバラバラで書かれていて、おそらくB列とC列を毎回コピーしてたのかな?(結果はあえて伏せています)
    For i = 1 To r_cntF
        If Left(.Cells(i, 2), 2) = "05" Or Left(.Cells(i, 2), 2) = "07" Then
            If summSh.Range("C2").Offset(.Cells(i, 6), 0) = "Y" Then
                kemY = "○"
            ElseIf summSh.Range("D2").Offset(.Cells(i, 6), 0) = "Y" Then
                sangY = "■"
            ElseIf summSh.Range("E2").Offset(.Cells(i, 6), 0) = "Y" Then
                seikY = "▲"
            ElseIf summSh.Range("F2").Offset(.Cells(i, 6), 0) = "Y" Then
                iryoY = "●"
            ElseIf summSh.Range("G2").Offset(.Cells(i, 6), 0) = "Y" Then
                nogY = "□"
            ElseIf summSh.Range("H2").Offset(.Cells(i, 6), 0) = "Y" Then
                etcY = "上記以外の△"
            End If

            .Cells(i, 3) = .Cells(i, 2) & summSh.Range("A2").Offset(.Cells(i, 6), 0) & kemY & sangY & seikY & iryoY & nogY & etcY
        Else
            .Cells(i, 3) = .Cells(i, 2)
        End If

	'D列の処理。
    For i = 2 To r_cntF
        On Error Resume Next
        ix = WorksheetFunction.Match(.Cells(i, 3), SerchM, 0)
        If Err.Number = 0 Then
            .Cells(i, 4).Value = WorksheetFunction.Index(SerchI, ix, 12)
        Else
            .Cells(i, 4).Value = ""
        End If
        On Error GoTo 0

    Next i

	'最初の思いついたA列の処理だけど、実際は58行目から(1行目は項目)"U"に変わってたから今回のトピ事項
    For i = 2 To r_cntF
        If summA.Offset(.Cells(i, 6), 0) = "" Or .Cells(i, 4) = "" Then
            FlowB = ""
        Else
            FlowB = .Cells(i, 2)
        End If

        .Cells(i, 1) = FlowB & summA.Offset(.Cells(i, 6), 0) & summB.Offset(.Cells(i, 6), 0) & "N" & .Cells(i, 4)
    Next i
End With

投稿日時: 20/07/30 11:35:36
投稿者: vaioyuki

実際には
A2〜A57までは、
=IF(OR(OFFSET(サマリ!$A$15,F2,0)="",ISNA(D2)),"",B2&","&OFFSET(サマリ!$A$15,F2,0)&OFFSET(サマリ!$B$15,F2,0)&",N,"&D2)
A58〜A113までは、
=IF(OR(OFFSET(サマリ!$A$15,F58,0)="",ISNA(D58)),"",B58&","&OFFSET(サマリ!$A$15,F58,0)&OFFSET(サマリ!$B$15,F58,0)&",U,"&D58)
になっています。

投稿日時: 20/07/30 13:34:49
投稿者: vaioyuki

度々の言葉足らずで申し訳ありません。
NとUが交互というのは、56行ごとにNとUが変わるものでした。
 
先ほど以下の文を作成したのですが、
これまた私の勘違いというか大失敗で、
F列(OFFSET用の0からカウントアップしていく列)は112行ごとにカウントアップしていくので下のも当たり前ですが112行ごとにNとUが表示されました。泣
 

    For i = 2 To r_cntF
        If summA.Offset(.Cells(i, 6), 0) = "" Or .Cells(i, 4) = "" Then
            FlowB = ""
        Else
            FlowB = .Cells(i, 2)
        End If

        If .Cells(i, 6) Mod 2 = 0 Then
            NU = ",N,"
        Else
            NU = ",U,"
        End If
        
        .Cells(i, 1) = FlowB & summA.Offset(.Cells(i, 6), 0) & summB.Offset(.Cells(i, 6), 0) & NU & .Cells(i, 4)
    Next i

回答
投稿日時: 20/07/30 14:49:09
投稿者: Suzu

すみません。交互ではなかったのですね。
56行毎であれば、もっと簡単ですよ。
 
 
コピーも、値の代入も、112行を一括で 行っており、
 
処理先 の 起点 の指定  : .Cells(r_cnt + 1, 2) の様に 行は、r_cnt+1
処理範囲 112行 の指定  : .Resize(cmyRows)
 
ですから
 
起点をそのままで、 112行の半分 cmyRows/2 の範囲に対し N を指定
起点に cmyRows/2 を加えた位置 を基点に、112行の半分 cmyRows/2 の範囲に対し U を指定
 
で処理できますよね。
 
----------------------------------------------------------------------
提示いただいたコードは細部まで見ておりません。
ただ、セル範囲に対しての処理と、1セルづつの処理が混在していますよね。
 
あるセル範囲に対し一括で処理できるならその方が処理速度は向上しますよね。
相対セル指定で簡素化できる部分があれば、セルに数式を与え計算させます。
そういう意味で、セル内で R1C1形式の数式を与える方法を提示しています。
 
 
一連の処理を、処理した後、再び 同じ条件のデータ を使用する事がある
 
つまり、マクロ処理後に保存し、同じファイルを開くことが頻繁にあるのであれば
セルに入っているのは、数式より値の方が 開く際に掛かる時間は少なくてすみますから
処理の中で使用されている様に、処理した範囲を コピーし、値貼りつけを行って保存すれば良いです。

回答
投稿日時: 20/08/01 15:38:02
投稿者: mattuwan44

Option Explicit

Sub test()
    Dim rngサマリ As Range
    Dim r As Range
    Dim ixRow As Long
    
    With Worksheets("サマリ").UsedRange
        Set rngサマリ = Intersect(.Cells, .Offset(1))
    End With
    
    For Each r In rngサマリ.Rows
        If IsError(r.Cells(4).Value) _
        Or Len(r.Cells(6).Value) = 0 Then
        Else
            With Worksheets("フロー").Range(r.Address)
                ixRow = .Worksheet.Range("F2").Value
                .Cells(1).Value = .Cells(2).Value & "," & _
                                  .Cells(ixRow, 1).Value & _
                                  .Cells(ixRow, 2).Value & _
                                  ",N," & .Cells(4).Value
                .Cells(2).Value = (r.Row - 1) \ 112
                .Cells(3).Value = IIf((r.Row - 1) \ 56 Mod 2, "N", "U")
            End If
        End If
    Next
End Sub

 
こういうことがしたいのかなぁ。。。
回答者が数式やコードを読み解いてくれることを期待しているなら、間違いです。
速やかに解決にしたいなら、日本語でやりたいことをきちんと説明しましょう。
そうすることで、コードを書く前に頭が整理されると思います。
 
A列は数式でB列以降はマクロで結果を入力するんですか?
どっちかに揃えてはいかがでしょうか?
処理的には、
数式は複数セルに一括で入力できるので、
VBAで数式を一括入力するのが一番速いかと思います。
当然、保存時にはマクロで数式を値に変換し、再計算が起きないようにします。

投稿日時: 20/08/03 17:22:52
投稿者: vaioyuki

ありがとうございます。
 
別件で忙しかった上に、
社内でPCR検査結果待ちの人が出てあわただしくこちらもじっくり検証できていません。
 
少しずつ皆さんの意見をかみ砕いていこうと思います。
 
 
 
結果的には現在数式として入力されているものをマクロ化しようと思っています。
これだけは数式、これはマクロとは考えておりません。
最終的にはこちらで出たものをSQL文として新たに作成しなおします。
 
色々わかりにくい説明で申し訳ありませんが、もう少しお付き合いください。
よろしくお願いします。

回答
投稿日時: 20/08/04 00:40:04
投稿者: simple

すでに適切なコメントがあるところ、なぜ途中で止まったままになっているのか
よく分かりません。
何を「よろしくお願いします。」なのかが不明です。
 
(1)"N","U"を上から順次設定していくなら、次のような考え方にします。
前回スレッドの

   j = 0
    For i = 2 To r_cnt Step setRows
        .Cells(i, 6).Resize(setRows).Value = j
        j = j + 1
    Next
とまったく同様の考え方で、例えば、どこかの作業列を使って、
 
   j = "N"
    For i = 2 To r_cnt Step setRows/2 
        .Cells(i, XX).Resize(setRows).Value = j
        If j = "N" Then j = "U" Else j = "N"
    Next
といった風にすれば良いのではないですか?
(2)
そうした順次設定ではなく、
指定した行に対して"U"か"N"を判定するなら、
   r を行番号として、
   もし (( r - 2 ) \ 56 ) Mod 2 = 0 なら "N"ですし、
   もし (( r - 2 ) \ 56 ) Mod 2 = 1 なら "U"にするといった方法もあります。
上記の(1)(2)のいずれかを使えば良いでしょう。
 
基本的な考え方は書きましたので、
あとは、ご自分でコードを作成してください。
それはさほど難しいことではないと思います。
ご自分で考えながらコードを書いた方が、内容の理解につながるはずです。
(もし、前に進まないとしたら、他人に頼ろうとしているからではないですか。
  自分で取り組むんだ、と腹を決めて考えれば、そんなに難しい話ではないはずです。)
# なお、皆さんからも同様の指摘があるのだと思いますが、詳細読んでいませんので、
# まったく同じコメントでしたら済みません。

回答
投稿日時: 20/08/04 10:21:09
投稿者: Suzu

引用:
結果的には現在数式として入力されているものをマクロ化しようと思っています。
これだけは数式、これはマクロとは考えておりません。
最終的にはこちらで出たものをSQL文として新たに作成しなおします。

 
SQL? どうしてSQL が出てくるのでしょうか。
 
Excelですと、OFFSETで考える事ができても
データベースでは、並び順を 考慮し SQLを組み立てます。
 
考え方を変える必要があります。
安易に移行できる物ではないですよ。
 
(今回の事案は、ラベル印刷のデータの作成の様に見えますので
 Accessの レポートが使用できるなら、もっと別の考え方もできるでしょうね。)

投稿日時: 20/08/04 14:32:18
投稿者: vaioyuki

Suzuさま
 
別件の仕事が入り、こちらの作業に集中できなくなったために検証できていないと書きました。
なにぶん吸収するのが遅いのでみなさんのコメントを理解するのに時間を要するため、
このスレをほったらかしにするのは申し訳ないのでそう書きました。
ご気分を害したならばすいません。
 
こちらのファイルは全部で8つのシートに分かれており、質問させていただいたシートはその一部になります。
別のシートではこちらで表示したものをSQL文として表示しているものもあります。
 

    For j = 2 To r_cnt
        .Cells(j, 12) = "select * from ○ where department_cd='" & .Cells(j, 1) & .Cells(j, 2) & "' "
        .Cells(j, 14) = "select * from △ Where department_cd='" & .Cells(j, 1) & .Cells(j, 2) & "' and (substr(business_category_cd,1,3)!='132' or (substr(business_category_cd,1,3)='132' and substr(business_category_cd,5,6)='" & Left(.Cells(j, 1) & .Cells(j, 2), 6) & "')) "
        .Cells(j, 16) = "select * from □ where department_cd='" & .Cells(j, 1) & .Cells(j, 2) & "'"
    Next j

 
そういった意味でSQL文にすると書きました。
こちらのSQL文をDeveloperで実行させています。
ややこしくてすいません。
 
「よろしくお願いします。」と書いたのも最終的には解決したのをご報告させていただきたいので、
少し時間がかかるかもしれませんが「よろしくお願いします。」の意味合いで書きました。
みなさんが簡単なことでも私にとっては難しいこと、新しく覚えることになるので自分なりに調べて前に進もうとしています。
頼りきりにしようとは思っていません。
 
時間がかかるかと思いますが頑張ってみます。
 

回答
投稿日時: 20/08/04 16:00:15
投稿者: Suzu

今回のご質問では、「52件毎」については、論理の組立になります。
 
既にいろいろなアプローチの方法が提案されていますから
その中でお気に召す方法を採れば良いと思います。
 
 
 
 
 
掲示板のやりとりですので、相手の方の状況が見えないで進みます。
 
他の事に注力し時間が取れない という 状況ももちろんそうですが
回答者さんのスキルを推測して 回答していきます。
 
質問の内容の根本的な部分が伏せられており 深く聞いていくと 「そんなことがあったのね!」とか
 
なので回答者としては、話の端々に引っ掛かった事を掘り下げていく事があります。
その事で、解決が早まる事もありますが、逆にドツボにハマってしまう事もあります。
回答者の余計なお世話の部分で、質問外の部分に触れて時間が掛かる事もあります。
 (ご質問の内容とは直接関係の無い部分 効率的な処理とか、コードの書き方とか)
 
それらは、問題が解決してから改めて取り組む事が確実なのですが
解決した部分を組みなおす必要があり、費やした時間を無駄にする事あります。
 
例えば

引用:
    'B列は112行は固定にしています。それをコピーしてデータ分繰り返す処理
    For i = 1 To countD
     (中略)
    Next i
 
    r_cntF = .Range("B1").CurrentRegion.Rows.Count
    r_cntNU = .Cells(Rows.Count, "I").End(xlUp).Row
     
    'A列の処理で迷っているところ
    For i = 1 To countD
     (中略)
    Next i

 
この部分に関して
    For i = 1 To countD
を 2回 ループ処理を行っています。 1回で済んじゃうよね〜とか。。
 
計算で事前に算出できそうな事でも、貼りつけ後に、最終行を取得して得ていて
コードの寄せ集め感が大きく、改善できればコードをスッキリさせたり高速化に繋がるのですが、
 
それを行うためには、全体感や表のレイアウトも含め把握する必要があるのですが
挫折し「提示いただいたコードは細部まで見ておりません。」と発言しています。
 
 
52件 についても、変数が多い事も、ご自身の中で、論理組立がうまくいっていないのではないでしょうか。
 
時間は掛かりますが、他の方の作ったコードをの変数の中身をローカルウィンドに表示させ、
追っていくとわかり易いと思います。 がんばってください。

トピックに返信