Excel VBA質問箱 IV

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

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


6334 / 13644 ツリー ←次へ | 前へ→

【45698】VBA上で集計するフィールドの選択をしたい 自動記録編集者 07/1/11(木) 15:34 質問[未読]
【45699】Re:VBA上で集計するフィールドの選択を... りん 07/1/11(木) 16:31 発言[未読]
【45707】Re:VBA上で集計するフィールドの選択を... 自動記録編集者 07/1/12(金) 9:12 発言[未読]
【45724】Re:VBA上で集計するフィールドの選択を... 馬太馬 07/1/12(金) 17:31 回答[未読]
【45736】Re:VBA上で集計するフィールドの選択を... Kein 07/1/13(土) 0:05 回答[未読]
【45849】Re:VBA上で集計するフィールドの選択を... 自動記録編集者 07/1/16(火) 9:28 お礼[未読]

【45698】VBA上で集計するフィールドの選択をし...
質問  自動記録編集者  - 07/1/11(木) 15:34 -

引用なし
パスワード
   小計を行う際に出てくるチェックボックスのチェックをVBA上で入れて、集計をしたいのですが入れることができません。

見出し | A B C 総計1 A B C 総計2
    | 
    | 2 2 3  7  3 5 2  10
    | 3 1 4  8  4 2 5  11
    |
    |→毎回ここから右を全て集計したい

けど、A〜Cまでだったり、A〜Fまでだったりするため列数が変わります。

自動記録でマクロ記述すると以下のようになってました。

Range("A14:DI1358").Select
Range("DI14").Activate
Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(94, 95,_
96 , 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110,_
111, 112, 113), _
Replace:= True, PageBreaks:=False, SummaryBelowData:=True

上の「Array」部分を変えなくてはいけないと思うのですが、ほかにも方法があれば教えていただけますでしょうか。

よろしくお願いします。

【45699】Re:VBA上で集計するフィールドの選択...
発言  りん E-MAIL  - 07/1/11(木) 16:31 -

引用なし
パスワード
   自動記録編集者 さん、こんにちわ。

>小計を行う際に出てくるチェックボックスのチェックをVBA上で入れて、集計をしたいのですが入れることができません。
>
>見出し | A B C 総計1 A B C 総計2
>    | 
>    | 2 2 3  7  3 5 2  10
>    | 3 1 4  8  4 2 5  11
>    |
>    |→毎回ここから右を全て集計したい
>
>けど、A〜Cまでだったり、A〜Fまでだったりするため列数が変わります。

>自動記録でマクロ記述すると以下のようになってました。
>Range("A14:DI1358").Select
「A〜Cまでだったり、A〜Fまでだったり」といいつつ、A14:DI1358なのは何故ですか?

【45707】Re:VBA上で集計するフィールドの選択...
発言  自動記録編集者  - 07/1/12(金) 9:12 -

引用なし
パスワード
   ▼りん さん:
>自動記録編集者 さん、こんにちわ。
>
>>小計を行う際に出てくるチェックボックスのチェックをVBA上で入れて、集計をしたいのですが入れることができません。
>>
>>見出し | A B C 総計1 A B C 総計2
>>    | 
>>    | 2 2 3  7  3 5 2  10
>>    | 3 1 4  8  4 2 5  11
>>    |
>>    |→毎回ここから右を全て集計したい
>>
>>けど、A〜Cまでだったり、A〜Fまでだったりするため列数が変わります。
>
>>自動記録でマクロ記述すると以下のようになってました。
>>Range("A14:DI1358").Select
>「A〜Cまでだったり、A〜Fまでだったり」といいつつ、A14:DI1358なのは何故ですか?

わかりづらくてすいません。
つまり、『「列数が変化する表を集計する」際に、「集計するフィールドの選択」をしたい』ということで、A〜CorA〜Fという風に2種類ということではありません。

表の右端の列数を取得することは出来るので、それを利用すればどうにかできるのではないかと思ったのですが・・・ちょっとわかりませんでした。
よろしくお願いします。

【45724】Re:VBA上で集計するフィールドの選択...
回答  馬太馬  - 07/1/12(金) 17:31 -

引用なし
パスワード
   ▼自動記録編集者 さん:
ToralList:=Array(〜
の所を変数を利用して設定したいという事で良いですか?

コードの例としては、以下のようになります。

Sub subtotalsumple()
  Dim myArray As Variant
  Dim mySTR As String
  Dim myCnt As Long
  Dim myRow As Long
  Dim myCol As Long
  Dim myColSt As Long
  
  
  myCol = 20     '集計開始の列番号
  myRow = 4      '見出し行の行番号
  
  
  myColSt = Cells(myRow, myCol).End(xlToLeft).Column - 1
  Do Until Cells(myRow, myCol).Value = Empty
    mySTR = mySTR & ", " & myCol - myColSt
    myCol = myCol + 1
  Loop
  mySTR = Mid$(mySTR, 3)
  
  myArray = Split(mySTR, ",")
  
  Cells(myRow, myColSt + 1).Select
  
  Selection.Subtotal GroupBy:=5, _
            Function:=xlSum, _
            TotalList:=myArray, _
            Replace:=True, _
            PageBreaks:=False, _
            SummaryBelowData:=True
  
End Sub


エラートラップ等、加味してないので適当に改良して使ってください。

【45736】Re:VBA上で集計するフィールドの選択...
回答  Kein  - 07/1/13(土) 0:05 -

引用なし
パスワード
   >チェックボックスのチェックをVBA上で入れて、集計をしたい
ということは、単に項目が多すぎてチェックが面倒だからなのか、ある一定の条件
に合う項目は全部集計対象にしたいと思うが、それが多すぎて困るから
なのか分かりませんが、条件が無いとすれば(ただし当然のことながら、集計の
基準にする列は除外するが)どうしても任意の項目を選択する必要がありますよね ?
なので集計機能のダイアログよりは "選択しやすくしたつもり" のリストボックス
をシート上に配置して、任意の複数の項目を選択できるようにしてみました。
以下の2つのマクロを標準モジュールに入れ、Select_Item の対象シートを
アクティブにするコードを正しく変更し、そのマクロを実行して下さい。
MySubTotal は呼び出し専用です。

Sub Select_Item()
  Dim Rc As Integer
  Dim Lp As Single, Tp As Single, Wp As Single
  Dim Hp As Single, Wp2 As Single, Hp2 As Single
  Dim LB As ListBox, BT As Button
  Dim LVal As Variant
 
  Sheets("Sheet1").Activate '←集計対象の表があるシートに変更 
  If Not IsError(Application.Match("総計", Range("E:E"), 0)) Then
   Cells.RemoveSubtotal
   If MsgBox("現在の集計を削除して終了しますか", 36) = 6 Then Exit Sub
  End If
  With ActiveWindow.VisibleRange
   Rc = .Rows.Count - 1
   With .Range("A1").Resize(Rc, 2)
     Tp = .Top: Wp = .Width: Hp = .Height
   End With
   With .Range("C1:D2")
     Lp = .Left + 0.1: Wp2 = .Width: Hp2 = .Height
   End With
  End With
  With ActiveSheet
   LVal = .Range("A14", .Range("A14").End(xlToRight)).Value
   Set LB = .ListBoxes.Add(0.1, Tp, Wp, Hp)
   Set BT = .Buttons.Add(Lp, Tp, Wp2, Hp2)
  End With
  LB.List = LVal
  LB.MultiSelect = xlExtended
  BT.Caption = "集計スタート"
  BT.OnAction = "MySubtotal"
  Set LB = Nothing: Set BT = Nothing
End Sub

Sub MySubTotal()
  Dim i As Integer, j As Integer
  Dim Cnt As Integer, CkAry() As Integer
  Dim Ck As Variant
 
  If VarType(Application.Caller) <> 8 Then Exit Sub
  With ActiveSheet
   .Cells.RemoveSubtotal
   With .ListBoxes(1)
     Ck = Application.Match(True, .Selected, 0)
     If IsError(Ck) Then
      MsgBox "集計対象の項目が選択されていません", 48
      Exit Sub
     ElseIf Ck = 5 Then
      MsgBox "集計の基準は選択できません", 48
      .ListIndex = 0: Exit Sub
     End If
     Cnt = .ListCount
     For i = 1 To Cnt
      If i <> 5 And .Selected(i) Then
        ReDim Preserve CkAry(j): CkAry(j) = i
        j = j + 1
      End If
     Next i
     .Delete
   End With
   .Buttons(1).Delete
   .Range("A14", .Range("A65536").End(xlUp)).Resize(, Cnt) _
   .Subtotal 5, xlSum, CkAry, False, True
   .Cells.ClearOutline
  End With
  Erase CkAry
End Sub

* なお、Select_Item のコードで
>LB.MultiSelect = xlExtended
についてですが、複数選択のできる形式には2種類あります。
いちおう"拡張複数選択"にしてありますが、テストした後、以下のヘルプの抜粋を
読んで適宜設定を変更して下さい。
------------------------------------------------------------------------
複数選択 (xlSimple) を指定すると、フォーカスの設定された状態で項目をクリック
するか Space キーを押したときに、その項目の選択状態が切り替わるようになります。
このモードは、頻繁に複数の項目が選択される場合に適しています。

拡張複数選択 (xlExtended) を指定すると、普通は単独選択と同じ動作で、
いずれかの項目をクリックするとそれまで選択されていた項目が選択解除されます。
しかし、Shift キーを押しながらクリックしたり方向キーを押したりすると、
それに応じて、選択される項目の範囲が拡張されます。
また、Ctrl キーを押しながらマウスをクリックすると、クリックされた項目だけが
選択項目に追加されます。

【45849】Re:VBA上で集計するフィールドの選択...
お礼  自動記録編集者  - 07/1/16(火) 9:28 -

引用なし
パスワード
   馬太馬さん、Keinさん、ありがとうございました。
記述いただいたコードを元に自分で頑張ってみます。
非常に助かりました。それでは失礼します。

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