[[20100312203656]]　『マクロを体裁よく書く方法は？』（？）　ページの最後に飛ぶ

『マクロを体裁よく書く方法は？』（？）

 Excel2003 WindowsXP

 私はマクロコードをこんな長いものを書いています。短く書く方法を教えてください。
book1.xlsSheet1に次の１．の表を作成、Sheet２に２．の表を作成、Sheet３に３．の
表を作成しています。

1.

 図番	 納入月日	 製品名	数	  単価	    金額	  分類	得意先	発注先
Ｈ１	 2009/4/6	 ＡＢＣ	1        50,000	  50,000	 ｻﾝﾌﾟﾙ	  ㈱Ａ	  Ａ社
Ｈ２	 2009/4/7	   　DE	1     1,650,000  1,650,000	  本品	  ㈱Ｂ	  Ｂ社
Ｓ１	2009/4/10    F-G	1       220,000	 220,000	  本品	Ｃ商店	  Ｃ社
Ｓ２	2009/4/10    Hij	1       230,000	 230,000	  本品	Ｃ商店	  Ｃ社
Ｓ３	2009/4/10	   klms	1        50,000	  50,000	 ｻﾝﾌﾟﾙ	Ｃ商店	  Ｃ社

2.

 '09年	12月度					
		Ａ社				
本品						
図番	  納入月日       製品名	数	   単価	   金額	
Ｈ５	2009/12/25	  abc	 1	 90,000	 90,000	
Ｈ７	2009/12/25	 B-3G	 1	120,000	120,000	
Ｈ３	2009/12/25	 DD8A	 1	100,000	100,000	       
Ｈ１１	2009/12/25	HA320	 1	 75,000	 75,000	
Ｈ１８	2009/12/25	10&BB	 1	100,000	100,000	
			       本品合計	485,000	
サンプル						
図番	  納入月日       製品名	数	   単価	   金額	
Ｓ２２	2009/12/18	 ip13	1	 20,000	 20,000		
			    サンプル合計	 20,000

				合計	505,000

3.

 メーカー月別金額			
月	     A社	　　　     B社	　　   C社
 6	\586,000	　　　\978,000	　\986,000
 7	\774,500	　　\2,198,000	\2,151,000
 8     \1,227,000     \1,801,800	\4,155,000
 9	\122,000	    \1,752,900	\2,209,000
10	\634,000	    \1,245,000	\3,990,000
11	\160,000	       \98,000	\1,395,000
12	\505,000	    \1,541,000	\1,140,000
 1	      \0	            \0	        \0
 2			
 3			
 4			
 5			
計     \4,008,500	    \9,614,700	\16,026,000

 表３にまとめるため次のような長いマクロコードを作成しています。マクロは正常に
作動しますが、あまりにも長すぎます。短くまとめる方法があれば、教えてください。

 Private Sub CommandButton3_Click()
 Sheets(UserForm2.ComboBox1.Text).Select
    Range("g60").Select
    Selection.Copy
    If Range("b3").Text = "6" Then
    Sheets("まとめ").Select
      If UserForm2.ComboBox1.Text = "A社" Then
    Range("d4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(UserForm2.ComboBox1.Text).Select
    Application.CutCopyMode = False
    Else
     If UserForm2.ComboBox1.Text = "B社" Then
    Range("b4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(UserForm2.ComboBox1.Text).Select
    Application.CutCopyMode = False
    Else
     If UserForm2.ComboBox1.Text = "C社" Then
    Range("c4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(UserForm2.ComboBox1.Text).Select
    Application.CutCopyMode = False
    Else
     End If
     End If
     End If
     End If

    If Range("b3").Text = "7" Then
    Sheets("まとめ").Select
    If UserForm2.ComboBox1.Text = "A社" Then
    Range("d5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(UserForm2.ComboBox1.Text).Select
    Application.CutCopyMode = False
    Else
     If UserForm2.ComboBox1.Text = "B社" Then
    Range("b5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(UserForm2.ComboBox1.Text).Select
    Application.CutCopyMode = False
    Else
     If UserForm2.ComboBox1.Text = "C社" Then
    Range("c5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(UserForm2.ComboBox1.Text).Select
    Application.CutCopyMode = False
    Else
         End If
     End If
     End If
     End If
     End If
        Else
    If Range("b3").Text = "8" Then
    Sheets("まとめ").Select
    If UserForm2.ComboBox1.Text = "A社" Then
    Range("d6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(UserForm2.ComboBox1.Text).Select
    Application.CutCopyMode = False
    Else
     If UserForm2.ComboBox1.Text = "B社" Then
    Range("b6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(UserForm2.ComboBox1.Text).Select
    Application.CutCopyMode = False
    Else
     If UserForm2.ComboBox1.Text = "C社" Then
    Range("c6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    　　　　・
　　　　　　・
　　　　　　・

 と６から初めて５まで１年分続けています。

 下から20行目位の
    If Range("b3").Text = "8" Then
 の上の
     End If
        Else
 はどのIfと対応していますか。
 インデントをしっかりつけてください。
 Selectionが多くて対象がどれかわかりにくいです。
    Range("g60").Select
    Selection.Copy
 は Range("g60").Copy と書けます。
 それから大まかな処理の流れを日本語で書いていただけませんか。
 また、コードを短く書くために表1、2のレイアウトの変更は可能ですか。
 (semm)

 こんな事かな。。。？

 '------
Private Sub CommandButton3_Click()
Dim 行 As Long, 列 As String
    Sheets(UserForm2.ComboBox1.Text).Select
    If Range("B3").Value <= 5 Then
        行 = Range("B3").Value + 10
    Else
        行 = Range("B3").Value - 2
    End If
    Select Case UserForm2.ComboBox1.Text
        Case "A社"
            列 = "D"
        Case "B社"
            列 = "B"
        Case "C社"
            列 = "C"
    End Select
    Sheets("まとめ").Range(列 & 行).Value = Range("G60").Value
End Sub
 '------

 まず、コピー（転記元の値のセル）は
 　UserForm2.ComboBox1.Text のシートの G60 セルの値

 貼り付け先は
 　シート名・・・・・・まとめ
   セル番地（行）・・・UserForm2.ComboBox1.Text のシートのB3セルの値が
 　　　　　　　　　　　　　5以下の場合　 B3セルの値 +10 の行
                       　　5より大の場合 B3セルの値 - 2 の行
 　セル番地（列）・・・UserForm2.ComboBox1.Text で選んである会社が
 　　　　　　　　　　　　　A社の場合　D列
 　　　　　　　　　　　　　B社の場合　B列
 　　　　　　　　　　　　　C社の場合　C列

 例えば、
 　UserForm2.ComboBox1.Text で、A社が選ばれており
 　　A社のシートのB3セルの値が　12 だった場合
 　A社のシートのG60セルの値を
 　まとめシートの　D10セルに転記

 ちなみに Select Case ・・・～ Case ・・・～ Csase ・・・　の部分は
 If ・・・ Then ～　ElseIf ・・・ Then ～　の様にもかけますし
 現在やって居られるように、Ifの中にIfを入れても良いと思います。

 (HANA)

 確かにシートの表示、マクロコードが多く流れの説明がありませんでした。

 １．シート１は　A列に図番、B列に納入月日　・・・　とし行には対応項目が発生するごとに
記入してゆきます。

 ２．シート２は発注先別に月ごとのデータを、本品、サンプルに分けてまとめています。
現物はシート３にB社、シート４にC社のまとめを設定しマクロと関数で記入するようにして
います。

 ３．シート３と表示しているシート名の現物は「まとめ」としています。ここには
発注先別月別各社の本品、サンプル金額の合計を記入しています。

 流れは、シート１にユーザーフォームを作成しそこにテキストボックス２個を置き、
検索開始日と検索終了日を書き込むようにし、またコンボボックスで発注先を選ぶように
しています。さらにコマンドボタンを２個おき「コマンドボタン１」でシート２、３、４に
転記し、「コマンドボタン２」でシート「まとめ」に転記するようにしています。

 この「コマンドボタン２」に登録しているマクロコードが上記コードです。毎月、月初に
前月のデータをまとめるのにこの２個のボタンをクリックするだけで短時間で処理できるので
非常に便利に利用しております。ただifを使用して１年分まとめようとしているので
非常に長いマクロコードになってしまいました。正常に作動しているのでこれでもいいのですが、
今後同様のマクロ作成のときの参考のため、ほかの簡潔なマクロコード作成方法があれば
教えてください。

 HANAさんの答えの最初に記入されているコードがこれに対応するコードですか？非常に簡単な
コードですね。（内容は非常に濃いですが。）
（？）

 HANAさん有難うございます。

 返事を書いた後、教えていただいたコードを使用中のコードに貼り付け、一部修正し（社名等）
実行したところ完璧です。私の作成したコードは何だったのだろうと、私のマクロに対する
勉強不足を痛感させられました。

 これは、Sheet1のデータを集計するものでしょうか?
 データ数がそれほど多くなく、SUMPRODUCT関数で処理できる程度の物なら
 関数で計算させればよくはないでしょうか、簡単に考えすぎでしょうか?　　(Hatch)
Sub Macro1()
    With Sheets("まとめ")
        .Range("B2:D13").Formula = _
            "=SUMPRODUCT((MONTH(Sheet1!$B$2:$B$10000)=$A2)*(Sheet1!$I$2:$I$10000=C$1)*(Sheet1!$F$2:$F$10000))"
        .Range("B2:D13").Value = .Range("B2:D13").Value
    End With
End Sub

 Hatchさんご指導のマクロを実際のデータにあわせる様、一部修正して、実行してみました。
 するとシート「まとめ」の指定すべてのセルに「#NUM!」が表示されました。

 確認です。

 $B$2:$B$10000 は「納入月日」の列をさし、$I$2:$I$10000は「発注先」の列をさし、また
$F$2:$F$10000は金額の列をさしているのですね。
 それと$A2とC$1の意味を教えてください。（？）

 数式を間違っていました。以下のように修正。
 C$1→B$1です。申し訳ありません。
Sub Macro1()
    With Sheets("まとめ")
        .Range("B2:D13").Formula = _
            "=SUMPRODUCT((MONTH(Sheet1!$B$2:$B$10000)=$A2)*(Sheet1!$I$2:$I$10000=B$1)*(Sheet1!$F$2:$F$10000))"
        .Range("B2:D13").Value = .Range("B2:D13").Value
    End With
End Sub
 まとめシートの構成は以下のようになっているとの仮定です。
 B2:D13に=SUMPRODUCT((MONTH(Sheet1!$B$2:$B$10000)=$A2)*(Sheet1!$I$2:$I$10000=B$1)*(Sheet1!$F$2:$F$10000))
 と数式を入れて計算したのと同じです。
	A	B	C	D
1	月	Ａ社	Ｂ社	Ｃ社
2	6	0	0	0
3	7	0	0	0
4	8	0	0	0
5	9	0	0	0
6	10	0	0	0
7	11	0	0	0
8	12	0	0	0
9	1	0	0	0
10	2	0	0	0
11	3	0	0	0
12	4	50,000	1,650,000	500,000
13	5	0	0	0

 元データのシート（Sheet1) は、以下の構成を仮定しています。
 	A	B	C	D	E	F	G	H	I
1	図番	納入月日	製品名	数	単価	金額	分類	得意先	発注先
2	Ｈ１	2009/4/6	ＡＢＣ	1	50,000	50,000	ｻﾝﾌﾟﾙ	㈱Ａ	Ａ社
3	Ｈ２	2009/4/7	DE	1	1,650,000	1,650,000	本品	㈱Ｂ	Ｂ社
4	Ｓ１	2009/4/10	F-G	1	220,000	220,000	本品	Ｃ商店	Ｃ社
5	Ｓ２	2009/4/10	Hij	1	230,000	230,000	本品	Ｃ商店	Ｃ社
6	Ｓ３	2009/4/10	klms	1	50,000	50,000	ｻﾝﾌﾟﾙ	Ｃ商店	Ｃ社

 以上のようなシートでのコードです。(Hatch)

 Hatchさん再度ご指導のように変更してテストしても同様に「#NUM!」がまとめの各セルに表示され
ました。そこでサンプルと同じSheet1、まとめを作成し元データの12,1月のデータを貼り付け
このマクロで実行したところ表示月はあっていますが、集計データは違っていました。

 そこでまとめシートの各セルにこの関数を貼り付けたところ正解のデータが表示されました。

 これで各セルの関数を確認したところ=$A2の数値が行により変化し=B$1の数値が列により変化
しています。マクロで実行するには、変化する数値も定義しないといけないのではないでしょうか。

 私にはこのマクロ表現の仕方はわかりませんが。（？）

 数式をセルに書き込んで計算しているだけです。
 > そこでまとめシートの各セルにこの関数を貼り付けたところ正解のデータが表示されました。
 一応、計算式は間違っていないようですね。

 ややこしく考えるより、数式でも計算できるのですから、それを利用した方が簡易かな。。。
 と、思ったまでです。
 列見出し、行見出しは一応固定のものと思ったので、A列と1行目の値は
 あらかじめ入力されているものと仮定しています。
 また、数式のままですと、再計算で重くなるといけないので、値に書き換えています。

 あと、
 > これで各セルの関数を確認したところ=$A2の数値が行により変化し=B$1の数値が列により変化
 > しています。マクロで実行するには、変化する数値も定義しないといけないのではないでしょうか。
 どのようなことなのか私には理解できません。
 セル範囲を選択して、数式を入力後Ctrl+Enterですべての選択セルに数式を入力すると
 いったことをマクロで書いているだけですから、
 当然、列や行の位置によって$のついていない部分は変化しているはずです。

 お書きのコードはシート2の計算結果の値を見て、それを転記するように読みましたが、
 各社ごと、もしくは各月ごとにコードを動かすのは面倒そうだと思いました。
 また、元データを修正したときはどうするのだろう、再計算させるなりしないといけないので
 該当する会社や月についてコードを実行するというのもちょっと面倒そう。と思ったりしました。
 元データから関数で割と容易に計算できそうでしたので、上記のようなものは使えないのかな
 と思い書いたまでです。 　　(Hatch)

コメント返信：

[ 一覧(最新更新順) ]

[[20100312203656]] 『マクロを体裁よく書く方法は？』（？） ページの最後に飛ぶ

[[20100312203656]]　『マクロを体裁よく書く方法は？』（？）　ページの最後に飛ぶ