Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


6 / 12962 ツリー ←次へ | 前へ→

【78946】vlookup,index関数他について vba勉強中 17/3/14(火) 11:44 質問[未読]
【78949】Re:vlookup,index関数他について γ 17/3/14(火) 20:08 発言[未読]
【78954】Re:vlookup,index関数他について vba勉強中 17/3/15(水) 10:33 発言[未読]
【78955】Re:vlookup,index関数他について vba勉強中 17/3/15(水) 10:37 発言[未読]
【78957】仕様の追記について vba勉強中 17/3/15(水) 10:55 発言[未読]
【78959】得たい結果について vba勉強中 17/3/15(水) 11:41 発言[未読]
【78953】Re:vlookup,index関数他について γ 17/3/15(水) 7:21 発言[未読]
【78956】Re:vlookup,index関数他について vba勉強中 17/3/15(水) 10:48 発言[未読]
【78958】可能な限り正確な仕様を書きます vba勉強中 17/3/15(水) 11:15 発言[未読]
【78960】Re:vlookup,index関数他について vba勉強中 17/3/15(水) 15:17 お礼[未読]
【78962】Re:vlookup,index関数他について γ 17/3/15(水) 20:53 発言[未読]

【78946】vlookup,index関数他について
質問  vba勉強中  - 17/3/14(火) 11:44 -

引用なし
パスワード
   いつもありがとうございます。簡単そうだと思って作っていたものが思いのほか複雑になってしまってわからなくなってしまいました。
タイトルはこれ使ったらいいのかな?というものを書いてみました。

やりたいことですが、シートA,Bがあります。
シートAはデータベース、シートBは挿入先となっていてBは複数のページに渡ります。
シートAの3列目には1,2,3,1,2,1,1,1,2,3,4,5,1,2,3,....と不規則に連続した自然数が並んでいて、[1]が現れるごとにシートBは次のページに進みます。自然数の最大値は1~99になります。

1,2,3,1,2,1,1,1,2,3,4,5,1,2,3,....(数列n)
(。)シートAの5列目には3パターンの文字列(K,L,M)があります。
(「)シートAの14列目には特定の文字列とその後に数字が含まれることがあります。
(数字の位置は不定です)
(」)シートAの15列目には5パターンの文字列があります。(a,b,c,d,e)(a<b<c<d<e)

(。)のKに関してはほとんど不要であるので、L,Mとa,b,c,d,eの計10パターンの挿入先が数列nの各項に対して存在します。ただしKであった場合は数列nは次項に進みます。

挿入先は毎ページ12列x行となっています。(7<x<20)
1~6列はLに対する、7~12列はMに対応しています。
1,7列目は数列nの各項が入っていきます。2~5,8~12列目にはほとんどの場合1が入りますが、(「)において数字が含まれる場合、その数字+1が入ります。

下に自分でできるだけやってみたものを示しますがもうぼろぼろです。この関数を使うと簡単等あれば教えていただければと思います。
相変わらずの説明下手、知識不足で申し訳ありませんがよろしくお願いします。


Sub maxtest()
  Dim n As Long, nrow As Long, ncol As Long, i As Long
  Dim target As Range, D3row As Long, D3col As Long
  Dim sh1 As Worksheet, D3 As Range, nexttarget As Range
  Dim cntA As Long, cntB As Long, cnt As Long
  Dim span As Range, srow As Long, scol As Long, nexts As Range
  Dim drrow As Long, drcol As Long
  Dim dzrow As Long, dzcol As Long
  Dim a As Long, b As Long

  
  Set sh1 = Worksheets("データベース")
  n = 1
  cnt = 1
  nrow = 5
  ncol = 3
  D3row = 8
  D3col = 29
  srow = 5
  scol = 2
  drrow = 10
  drcol = 7
  dzrow = 10
  dzcol = 1
  
  Set span = sh1.Cells(srow, scol)
  Set nexts = span.Offset(1)
  Set D3 = Cells(D3row, D3col)
  Set target = sh1.Cells(nrow, ncol)
  Set nexttarget = target.Offset(1)
  
  
  Do While Not IsEmpty(target)
    If target < nexttarget Then
      n = n + 1
      nrow = nrow + 1
    Else
      cntA = WorksheetFunction.CountIf(sh1.Range(sh1.Cells(nrow - n + 1, ncol), sh1.Cells _
      (nrow, ncol + 3)), "L")
      cntB = WorksheetFunction.CountIf(sh1.Range(sh1.Cells(nrow - n + 1, ncol), sh1.Cells _
      (nrow, ncol + 3)), "M")
      
      If cntA < 8 & cntB < 8 Then
        For i = 1 To n
          If target.Offset(cnt - n, 3) = "L" Then
            D3.Offset(drrow, drcol) = target.Offset(cnt - n)
              Select Case target.Offset(cnt - n, 12)
                Case "I"
                  D3.Offset(drrow, drcol + 1) = "1"
                Case "IIb"
                  If target.Offset(cnt - n, 11) Like "*" & "(他" & "*" & "箇所)" Then
                    a = InStr(target.Offset(cnt - n, 11), "(他")
                    b = InStr(target.Offset(cnt - n, 11), "箇所)")
                    D3.Offset(drrow, drcol + 2) = Mid(target.Offset(cnt - n, 11), a, b - a)
                  Else
                    D3.Offset(drrow, drcol + 2) = "1"
                  End If
                Case "IIa"
                  If target.Offset(cnt - n, 11) Like "*" & "(他" & "*" & "箇所)" Then
                    a = InStr(target.Offset(cnt - n, 11), "(他")
                    b = InStr(target.Offset(cnt - n, 11), "箇所)")
                    D3.Offset(drrow, drcol + 3) = Mid(target.Offset(cnt - n, 11), a, b - a)
                  Else
                    D3.Offset(drrow, drcol + 3) = "1"
                  End If
                Case "III"
                  If target.Offset(cnt - n, 11) Like "*" & "(他" & "*" & "箇所)" Then
                    a = InStr(target.Offset(cnt - n, 11), "(他")
                    b = InStr(target.Offset(cnt - n, 11), "箇所)")
                    D3.Offset(drrow, drcol + 4) = Mid(target.Offset(cnt - n, 11), a, b - a)
                  Else
                    D3.Offset(drrow, drcol + 4) = "1"
                  End If
                Case "IV"
                  If target.Offset(cnt - n, 11) Like "*" & "(他" & "*" & "箇所)" Then
                    a = InStr(target.Offset(cnt - n, 11), "(他")
                    b = InStr(target.Offset(cnt - n, 11), "箇所)")
                    D3.Offset(drrow, drcol + 5) = Mid(target.Offset(cnt - n, 11), a, b - a)
                  Else
                    D3.Offset(drrow, drcol + 5) = "1"
                  End If
                  
                cnt = cnt + 1
                drrow = drrow + 1
              End Select
          End If
          
          If target.Offset(cnt - n, 3) = "M" Then
            D3.Offset(dzrow, dzcol) = target.Offset(cnt - n)
              Select Case target.Offset(cnt - n, 12)
                Case "I"
                  D3.Offset(dzrow, dzcol + 1) = "1"
                Case "IIb"
                  If target.Offset(cnt - n, 11) Like "*" & "(他" & "*" & "箇所)" Then
                    a = InStr(target.Offset(cnt - n, 11), "(他")
                    b = InStr(target.Offset(cnt - n, 11), "箇所)")
                    D3.Offset(dzrow, dzcol + 2) = Mid(target.Offset(cnt - n, 11), a, b - a)
                  Else
                    D3.Offset(dzrow, dzcol + 2) = "1"
                  End If
                Case "IIa"
                  If target.Offset(cnt - n, 11) Like "*" & "(他" & "*" & "箇所)" Then
                    a = InStr(target.Offset(cnt - n, 11), "(他")
                    b = InStr(target.Offset(cnt - n, 11), "箇所)")
                    D3.Offset(dzrow, dzcol + 3) = Mid(target.Offset(cnt - n, 11), a, b - a)
                  Else
                    D3.Offset(drrow, dzcol + 3) = "1"
                  End If
                Case "III"
                  If target.Offset(cnt - n, 11) Like "*" & "(他" & "*" & "箇所)" Then
                    a = InStr(target.Offset(cnt - n, 11), "(他")
                    b = InStr(target.Offset(cnt - n, 11), "箇所)")
                    D3.Offset(dzrow, dzcol + 4) = Mid(target.Offset(cnt - n, 11), a, b - a)
                  Else
                    D3.Offset(dzrow, dzcol + 4) = "1"
                  End If
                Case "IV"
                  If target.Offset(cnt - n, 11) Like "*" & "(他" & "*" & "箇所)" Then
                    a = InStr(target.Offset(cnt - n, 11), "(他")
                    b = InStr(target.Offset(cnt - n, 11), "箇所)")
                    D3.Offset(dzrow, dzcol + 5) = Mid(target.Offset(cnt - n, 11), a, b - a)
                  Else
                    D3.Offset(dzrow, dzcol + 5) = "1"
                  End If
                  
                cnt = cnt + 1
                dzrow = dzrow + 1
              End Select
          End If
          srow = srow + 1
          Set span = sh1.Cells(srow, scol)
          Set nexts = span.Offset(1)
        Next
      End If
      
      D3row = D3row + 37
      cnt = 1
      n = 1
    End If
    nrow = nrow + 1
    Set target = sh1.Cells(nrow, ncol)
    Set nexttarget = target.Offset(1)
    Set span = sh1.Cells(srow, scol)
    Set nexts = span.Offset(1)
    Set D3 = Cells(D3row, D3col)
    
  Loop
End Sub

【78949】Re:vlookup,index関数他について
発言  γ  - 17/3/14(火) 20:08 -

引用なし
パスワード
   こんにちは。
(1)内容について共通理解に立つため
(2)テスト検証に役立てるため
具体的なサンプルデータと、得たい結果を示してもらえますか?
そうすれば、コメントもつきやすいでしょう。

【78953】Re:vlookup,index関数他について
発言  γ  - 17/3/15(水) 7:21 -

引用なし
パスワード
   仕様が曖昧です。
>Kに関してはほとんど不要
とか曖昧ですし、ほとんどとは何?
>2~5,8~12列目にはほとんどの場合1が入りますが
その列には、何がどんなルールで入るのか説明されていない。
"ほとんど"ではコードは組めない。

また、"ぼろぼろ"というコードが謙遜なのか本当にぼろぼろなのか
よく見ていないが、要件が不明のなかで、
これを回答者に分析しろというのは酷に過ぎる。

・正確な仕様を説明すること
・具体的な例を示すこと
がなければ、いくら待っていても適切な回答はつきにくいと思います。
一発逆転の関数では対応できない複雑なルールなのでは?

むだな繰り返しが多いとかの指摘はできても、
それが問題を解決するものでもなさそう。

【78954】Re:vlookup,index関数他について
発言  vba勉強中  - 17/3/15(水) 10:33 -

引用なし
パスワード
   ▼γ さん:
こんにちは、データベースと、その貼り付け先の例についてここに貼り付けますね。
start    1    tes    tes    K    tes    I
start    2    tes    tes    L    tes(他6箇所)    III
start    3    tes    tes    L    tes    III
1    1    tes    tes    M    tes    III
1    2    tes    tes    L    tes    III
2    1    tes    tes    M    tes    I
2    2    tes    tes    K    tes    I
2    3    tes    tes    L    tes    I
3    1    tes    tes    M    tes    III
4    1    tes    tes    L    tes    III
4    2    tes    tes    K    tes    III
end    1    tes    tes    M    tes    III

上記がデータベースの様式になります。範囲の関係上必要部分のみをコピーしました。[tes]に関しては参照不要です。
start~endは直接参照はしませんでしたが、実際はその部分が変わるたびにページが切り替わります。2列目の部分が数列n、最終列は5パターンあります。このデータでは2パターンでした。唯一[tes(他6箇所)]とある部分ですがこれがある場合は挿入先の数字が7に変わります。(これがない場合は1が挿入されます)

次に貼り付け先を示します。
K    L                        M                    
    N    tes                    N    tes                
        I   IIb   IIa   III   IV        I   IIb   IIa   III   IV
max


    max                        max                    
I    I                        I

【78955】Re:vlookup,index関数他について
発言  vba勉強中  - 17/3/15(水) 10:37 -

引用なし
パスワード
   途中送信してしまいました。
挿入先については格子で囲まれていますがうまくできませんでした。
maxという部分についてですが、I<IIb<IIa<III<IV
となっており、一番大きなものが入ります。先ほどの例ですとmaxのすぐ下にIもしくはIIIが入ることになります。
Kがほとんど不要と申し上げましたのはmaxのみが必要になるからです。

【78956】Re:vlookup,index関数他について
発言  vba勉強中  - 17/3/15(水) 10:48 -

引用なし
パスワード
   ▼γ さん:
>>2~5,8~12列目にはほとんどの場合1が入りますが
>その列には、何がどんなルールで入るのか説明されていない。
>"ほとんど"ではコードは組めない。
これについてですが先ほどの例ですと6列目(実際は14列目)において[他x箇所]と含まれている場合に限りx+1が入りますが、それ以外では1が入ります。

>また、"ぼろぼろ"というコードが謙遜なのか本当にぼろぼろなのか
>よく見ていないが、要件が不明のなかで、
>これを回答者に分析しろというのは酷に過ぎる。
申し訳ありません、切羽詰まりすぎて回答者の方に対する配慮が全く到っておりませんでした。


>・正確な仕様を説明すること
これについては分かるように練って再度投稿したいと思います。
>・具体的な例を示すこと
このことに関しましては先ほどのものでなんとかお願いしたいですが、どうでしょうか?
>がなければ、いくら待っていても適切な回答はつきにくいと思います。
>一発逆転の関数では対応できない複雑なルールなのでは?
複数の条件で特定のセルに挿入したいところであります。
nは任意ですが小さい順に,K,L,M及びI〜IVで挿入先の位置を特定し、挿入します。
それをnが1になるたびに挿入先のページが1ページ進む、ということをしたいです。

仕様について少しでもわかりやすいように練って参ります。

【78957】仕様の追記について
発言  vba勉強中  - 17/3/15(水) 10:55 -

引用なし
パスワード
   ▼vba勉強中 さん:
>▼γ さん:
>こんにちは、データベースと、その貼り付け先の例についてここに貼り付けますね。
>start    1    tes    tes    K    tes    I
>start    2    tes    tes    L    tes(他6箇所)    III
>start    3    tes    tes    L    tes    III
>1    1    tes    tes    M    tes    III
>1    2    tes    tes    L    tes    III
>2    1    tes    tes    M    tes    I
>2    2    tes    tes    K    tes    I
>2    3    tes    tes    L    tes    I
>3    1    tes    tes    M    tes    III
>4    1    tes    tes    L    tes    III
>4    2    tes    tes    K    tes    III
>end    1    tes    tes    M    tes    III
>
>上記がデータベースの様式になります。範囲の関係上必要部分のみをコピーしました。[tes]に関しては参照不要です。
>start~endは直接参照はしませんでしたが、実際はその部分が変わるたびにページが切り替わります。2列目の部分が数列n、最終列は5パターンあります。このデータでは2パターンでした。唯一[tes(他6箇所)]とある部分ですがこれがある場合は挿入先の数字が7に変わります。(これがない場合は1が挿入されます)
>
>次に貼り付け先を示します。
>K    L                        M                    
>    N    tes                    N    tes                
>        I   IIb   IIa   III   IV        I   IIb   IIa   III   IV
>max
>
>
>    max                        max                    
>I    I                        I

これが挿入先の各ページに存在します。初期設定ではmaxはIを入れています。
Nには数列nの各項が入ります。またI〜IV(これが最終列5パターンのどれかであるときにその列に挿入されます)の下に[1]もしくは([他x箇所]があった場合に限り)x+1が挿入されます。

【78958】可能な限り正確な仕様を書きます
発言  vba勉強中  - 17/3/15(水) 11:15 -

引用なし
パスワード
   ▼γ さん:
データベースについての記述です。
start    1    tes    tes    K    tesp    I
start    2    tes    tes    L    tesp    III
start    3    tes    tes    L    tesp    III
1    1    tes    tes    M    tesp    III
1    2    tes    tes    L    tesp    III
2    1    tes    tes    M    tesp    I
2    2    tes    tes    K    tesp    I
2    3    tes    tes    L    tesp    I
3    1    tes    tes    M    tesp    III
4    1    tes    tes    L    tesp    III
4    2    tes    tes    K    tesp    III
end    1    tes    tes    M    tesp    III

1列目は最初と最後は文字列、その間には数字が入ります。ここが変わると挿入先のページがきりかわります。(今回自分は参照していません)
2列目は数列nです。ここに[1]が現れる毎に挿入先のページが変わります。数列nの最大値の数だけ挿入先ページに挿入されることになります。(この例では各ページに3,2,3,1,2,1個挿入されていき、挿入先は6ページとなります。)
3,4列目は今回不要なデータです。
5,7列目が挿入先のどのセルに[1]もしくは[x+1]を入れるか特定することに使われます。挿入先は7列6行が2つ並んでいます。縦に数列n(n<7を想定しています)横にはI〜IVが並んでいます。
6列目では[(他x箇所)]と書かれている場合は挿入先の数字が1ではなくx+1が挿入されます。

なので自分はK,L,Mについてifを用いて3パターン、その中でI〜IVについての5パターンをcaseで分けました。caseの中で6列目に[他x箇所]がある場合についてifを用いています。(他x箇所)について出現する位置は不定であるのでまず場所をinstrで特定し、特定した場所からmidで抜き出すようにしました。

その前に数列nについての最大値を求めています。最大値分挿入を繰り返したら次のページに進むようにしたつもりです。
また挿入先のテンプレートが7行までしかないのでひとまず7行以下であることをcountifで確認しています。実際は7行以上になることはよくあるのですがその前に詰まってしまったため、ぼろぼろと表現させていただきました。

挿入先について1ページ目と2ページ目との表の間隔は一定ですのでページが切り替わるたびに定数(37行)あけています。

【78959】得たい結果について
発言  vba勉強中  - 17/3/15(水) 11:41 -

引用なし
パスワード
   ▼γ さん:
得たい結果について示します。
以下データベースです。
start    1    tes    tes    K    tes    I
start    2    tes    tes    L    tes    III
start    3    tes    tes    L    tes    III
1    1    tes    tes    M    他5箇所    III
1    2    tes    tes    L    tes    III
2    1    tes    tes    M    tes    I
2    2    tes    tes    K    tes    I
2    3    tes    tes    L    tes    I
3    1    tes    tes    M    tes    III
4    1    tes    tes    L    tes    III
4    2    tes    tes    K    tes    III
end    1    tes    tes    M    tes    III

以下挿入先です。

K        L                M
         n I IIb IIa III IV       nI IIb IIa III IV 
         2       1
         3       1


max       max               max
I       III                I

        (2ページ目)
K        L                M
         n I IIb IIa III IV       nI IIb IIa III IV 
         2       1         1      6


max       max               max
I       III                III


        (3ページ目)
K        L                M
         n I IIb IIa III IV       nI IIb IIa III IV 
         3 1              1 1


max       max               max
I       I                I

        (4ページ目)
K        L                M
         n I IIb IIa III IV       nI IIb IIa III IV 
                         1      1


max       max               max
I       I                III


        (5ページ目)
K        L                M
         n I IIb IIa III IV       nI IIb IIa III IV 
         1       1


max       max               max
III       III                I

        (6ページ目)
K        L                M
         n I IIb IIa III IV       nI IIb IIa III IV 
                         1      1


max       max               max
I       I                III

【78960】Re:vlookup,index関数他について
お礼  vba勉強中  - 17/3/15(水) 15:17 -

引用なし
パスワード
   申し訳ないです!
複雑であったためのケアレスミスが目立っていたようです。
サブルーチン化し一つずつこなすことでなんとかなりそうです。

お騒がせ致しました。
γさん、ありがとうございました。
人に伝わるようなことを考えるとミスに気付けるものだと痛感致しました。

ここで質問した工程についてはなんとかなりそうですが、おそらくまた詰まってしまいますのでその時は改めてよろしくお願いします。

完成したらこちらに貼り付けようかと思いますのでそのときはもしよければ無駄な繰り返しについて等ご指摘いただければと思います。

【78962】Re:vlookup,index関数他について
発言  γ  - 17/3/15(水) 20:53 -

引用なし
パスワード
   私は日中は職務専念義務がありますので、こちらへの投稿などできません。

大分詳細に書いていただいたので明確になった気がします。
きちんと書こうとすると、ご本人にとっても構造がよくわかってくる
という副作用も期待できることが理解いただけたと思います。
(アウトプットのフォーマットが少しわかりにくいです。
 maxがどうこうというのは説明ですか、それとも実際に求められているのか。
 行番号列番号の表示があるともっとよくなるとは思いますが・・・もういいでしょう。)

もう目処が立ったのであれば必要ないでしょうね。
サブプロシージャを使って同じ事は何度も書かないようにされたとのことですから、私の指摘したいことは既に自力で達成されたようです。


なお、私でしたら、ごく素直に以下のような感じでコードを書くと思います。
疑似コードですが、参考になりますか。

Sub test()

For j = 1 to 最終行
  ・j行の各列の値をそれぞれ変数に取り込む
  ・s6(6列目の値をこう表記)が"他"を含んでいたら数値をとりだし、
   そうでなければ 1 とする。
  
  ・もし、s2が 1 なら、ページを更新。(書込行を進めて更新する)
  
  ・s5 が "K" "L" "M"のどれに一致するか判定
  
  If "L" または "M"なら
    ・そのブロックの中での書込行番号を +1 加算。
    ・s7 をもとに(I,II,IIIなどで判定して)それぞれのブロックの列位置を決定
    ・行番号と列番号に従って、s2を書き込む。
    ・また、s6からの算定した値も書き込む   
  End If 
Next

End Sub

6 / 12962 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free