Excel VBA 変数 ワークシートへ反映

質問

支店毎に売上集計を行うVBAを現在作成中です。
sheet3に支店名をリストにしており、ユーザーフォームのテキストボックスとコマンドボックスを配置し、テキストボックスに支店名を入力してコマンドボックスを押す事でsheet1の決まったセルに商品毎の金額の合算が出る様にしたいと考えています。
金額の集計表は別にあり、列の一番目に支店名、2番目に商品名、3番目に取引内容が反映されております。
今回ご相談させて頂きたいのは、ユーザーフォームのコマンドでどの様に入力したら良いかという事です。下記にユーザーフォームで現在までに入力した内容を記載します。

Sub 売上集計()
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If

Dim Result As Long

For i = 1 To 100
GroupName = Sheets(3).Cells(i, 1).Value
Range("A1").AutoFilter field:=1, Criteria1:=Array("" & GroupName & ""), Operator:=xlFilterValues
Range("A1").AutoFilter field:=2, Criteria1:=Array("商品名A"), Operator:=xlFilterValues　
　　Range("A1").AutoFilter field:=3, Criteria1:=Array("売上", "返品"),
Operator:=xlFilterValues
result1 = WorksheetFunction.Subtotal(9, Range("BF:BF"))
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False

End If
End Sub

上記希望内容についてユーザーフォームのコマンドに入力すべき内容をご存知の方、何卒ご教示願います。

回答No.2

mitarashi · ベストアンサー

>ユーザーフォームのテキストボックスとコマンドボックス
とありますが、文脈からフォームコントロールの事の様に思えます。
リストを用意してあるなら、入力規則で十分と思いますがいかがでしょうか。
Sheet1に入力規則から選択するセルと、結果を表示するセル
Sheet2にオートフィルタをかけるデータ
Sheet3にリストがあるものとします。

☆標準モジュール
Sheet3のリストに動的な名前をつけ、Sheet1のB2に入力規則を設定します。
Sub makeNameAndValidation()
ActiveWorkbook.Names.Add Name:="mylist", RefersToR1C1:="=OFFSET(Sheet3!R1C1,0,0,COUNTA(Sheet3!C1),1)"
With Sheets(1).Range("B2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=mylist"
End With
End Sub

☆Sheet1モジュール
オートフィルタを掛けるところは、試験のため簡略化しています。
お気に召したらご自分でアレンジして下さい。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim result1 As Long
Dim sh As Worksheet
Dim GroupName As String

GroupName = Target.Value
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set sh = Sheets(2)
With sh
If .AutoFilterMode Then
.AutoFilterMode = False
End If
'ここは適当な範囲に合わせて試験したもの
'-----------------------------------------------------------
.Range("A1").AutoFilter Field:=1, Criteria1:=GroupName
result1 = WorksheetFunction.Subtotal(9, .Range("B:B"))
'-----------------------------------------------------------
Me.Range("A2").Value = result1
If .AutoFilterMode Then
.AutoFilterMode = False
End If
Application.ScreenUpdating = True
End With

End Sub

※本当にユーザーフォームを使いたいという場合は、補足して下さい。

回答No.1

nak777r

>支店毎に売上集計を行うVBAを現在作成中です。
>sheet3に支店名をリストにしており、
>ユーザーフォームのテキストボックスとコマンドボックスを配置し、

意味不明、
ユーザーフォームそのものが存在するのか
ユーザーフォーム等で使用するテキストボックス等を Sheet3 に貼り付けているのか

そもそも、コマンドボックスとは何か？

コンボボックスなのか、コマンドボタンなのか
それとも、コマンドボックスというコントロールが存在するのか？

>sheet1の決まったセルに商品毎の金額の合算が出る様にしたいと考えています。

意味不明、
最初、支店毎に～と言っていて、
テキストボックス等に支店を入力するとか書いているのに、結果は商品毎

>今回ご相談させて頂きたいのは、
>ユーザーフォームのコマンドでどの様に入力したら良いかという事です。

意味不明、
コマンドって何？

例えば、"A" って打ったら、○○支店　みたいな事がコマンドだと思うけど
そもそも、支店の数や名称は決まっているのでしょうから
テキストボックスでなく、コンボボックスで選ばせれば良いだけではないかと
思うのだが、なぜ、テキストボックスを使用して、コマンド入力させるのか
結局なにがしたいのか、何を質問したいのか