マクロでデーター増減に対応するには？

質問

いつもお世話になり有難うございます。少し前からマクロをやり始めているものですが、記録方式ばかりで記述はまだできません。

　その記録マクロで殆どは対応できたのですが、何度やっても思う結果が得られないので質問しました。
下記の配列で表が並び
順番　　型式 　品番　　数量　　　行先
　1　　　　A-2 　 ZX 　 300 　　 　　 1
　1 　　　 A-1 　 WX 　 150 　　　 　 2
　1 　　 　B-3 　 FK 　 200　 　　　　 2
　 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ↓　　　　↓　　　　↓　　　↓　　　　↓
　これを
順番　　 　1　　　 2 3
　 品番　　A-1 A-2 B-3 　　　 総計
　　 ZX 　　 200
　　　WX 　　　 150
FK 300
形式の表の型に変えたいと思い、縦に並んでいるCSVファイルをピボットテーブルを用いて一覧表にしょうとしたいのですが、行データーは日により、増減し、最後の総計（合計の列が表示されません。絶対参照と相対参照を切り替えたりしていますが、上手く表示できる場合とできない　時があります。

　コードの意味は何となく、分かりますが、原因と対策が分かりません。エラー、デバックばかりで行き詰まっています。

回答No.3

Wendy02 · ベストアンサー

こんばんは。Wendy02です。

＞順番というのはなんですか？元の表では、1,1,1 となっていて、移し変えると、1,2,3 となっているようですが、その論理が分りません。
これについては、きちんとした説明がされていませんので、こちら側で、つけさせていただきました。

'---------------------------------------------
'<標準モジュール>
Sub LinesCopyToDestinations()
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim c As Range
Dim strItem As String
Dim strType As String
Dim ItemRow As Variant
Dim TypeColumn As Variant
Dim i As Long
Dim j As Long
'シートの設定
Set Sh1 = Worksheets("Sheet1")
Set Sh2 = Worksheets("Sheet2")

With Sh2
　.Range("A1").CurrentRegion.ClearContents
　.Range("A1").Value = Sh1.Range("A1").Value '順番
　.Range("A2").Value = Sh1.Range("B1").Value '型式
End With
Application.ScreenUpdating = False
For Each c In Sh1.Range("E2", Sh1.Range("E65536").End(xlUp))
　　strItem = c.Offset(, -2).Value
　　strType = c.Offset(, -3).Value
　On Error Resume Next
　　TypeColumn = WorksheetFunction.Match(strType, Sh2.Rows(2), 0)
　On Error GoTo 0
　　If TypeColumn = Empty Then
　　　TypeColumn = Sh2.Range("IV2").End(xlToLeft).Offset(, 1).Column
　　　i = CLng(TypeColumn)
　　　Sh2.Cells(2, i).Value = strType
　　 Else
　　　i = CLng(TypeColumn)
　　End If
　　TypeColumn = Empty
　On Error Resume Next
　　ItemRow = WorksheetFunction.Match(strItem, Sh2.Columns(1), 0)
　On Error GoTo 0

　If ItemRow = Empty Then
　　j = Sh2.Range("A65536").End(xlUp).Offset(1).Row
　　Sh2.Cells(j, 1).Value = strItem
　　Sh2.Cells(j, i).Value = Sh2.Cells(j, i).Value + c.Offset(, -1).Value
　　j = j + 1
　Else
　　Sh2.Cells(CLng(ItemRow), 1).Value = strItem
　　Sh2.Cells(CLng(ItemRow), i).Value = Sh2.Cells(CLng(ItemRow), i).Value + c.Offset(, -1).Value
　　ItemRow = Empty
　End If
Next
　　With Sh2.Range("A1").CurrentRegion
　　.Offset(, 1).Resize(, Columns.Count - 1).Sort _
　　　 Key1:=.Cells(2, 2), _
　　　 Order1:=xlAscending, _
　　　 Header:=xlGuess, _
　　　 OrderCustom:=1, _
　　　 MatchCase:=False, _
　　　 Orientation:=xlLeftToRight
　　End With
　　With Sh2
　　i = .Range("IV2").End(xlToLeft).Offset(, 1).Column
　　j = .Range("A65536").End(xlUp).Row
　　.Cells(2, j).Value = "総計"
　　.Range("B1").Resize(, i - 2).FormulaLocal = "=Column()-1" '順番
　　.Range("B1").Resize(, i - 1).Value = .Range("B1").Resize(, i - 1).Value
　　.Range(.Cells(3, i), .Cells(j, i)).FormulaLocal = "=Sum(RC2:RC[-1])"
　　End With
　　Application.ScreenUpdating = True
　　Set Sh1 = Nothing: Set Sh2 = Nothing
End Sub

回答No.2

Wendy02

こんばんは。
#1 さんと同じ内容で恐縮です。

サンプルの列が乱れていてよく分りませんので、こちらが書き直してみました。
半角のスペースは、1個以上はつぶれてしまいますので、よく分らなくなります。

＞順番　　型式 　品番　　数量　　　行先
＞　1　　　A-2　　ZX　　 300　　　　1
＞　1　　　A-1　　WX　　 150　　　　2
＞　1　　　B-3　　FK　　 200　　　　2

-------------------------------------------
＞順番　　1　　　　2　　　　　3
＞品番　　A-1　　　A-2　　　B-3　　 総計
＞　ZX　　　　　　 200
＞　WX　　　　　　　　　　　150
＞　FK　 300

同じものが出てきたときは、加算をされるということでしょうか？
行き先とはいうのは、並べ替えられた表には関係があるのですか？
順番というのはなんですか？元の表では、1,1,1 となっていて、移し変えると、1,2,3 となっているようですが、その論理が分りません。

お礼

ご返答有難うございます。質問内容の下書き欄が上手く書けず、説明不足になっていました。

　列と行が下書きでズレており、
　表示したいのは
１．順番を最上段に表示する
２．品番ＺＸ以下を最左の列に並べる（ＷＥＮＤＹ０　　２さんの返答通り）
３．型式と順番は上段の元配列通りで項目が５００位ある。増減有
４．行先は捨てる
５．品番ごとの合計を最右列に表示させたい。

　なのですが、データー増減幅が大きく、空白セルが（空白）と表示される
６．品番と順番のデーターラベルを入れ替えしているが、品番/順番ごとの合計表示セルが出てしまい、このセルは不要なので表示行を選択し、ジャンプ→空白セル→削除で消したいが、そうすると合計欄が表示されず、消えてしまう。
７．データーの多少に関わらず、不要な（空白セル）を出したくない。

の以上なんです。質問下書きレイアウト編集中にページを切り替えていた時、誤って質問するをクリックしてしまいました。どうもすみません。

回答No.1

imogasi

回答は上げることはできないかもしれませんが、捨石となって、補足要求します。
実例を挙げてくださったのはありがたいが、それだけでは不十分な場合が多く、意図するところを、文章でも説明してください。
下記で良いでしょうか。
（１）順番が同じ製品を、縦横並べ替える。
すると、順番　　 　1　　　 2 3の２、３は何ですか
（２）品番は同じ順番のものは、スペース区切りで列挙する。それも並べ替えて。
（３）総計とは何の合計ですか
（４）品番と数量に関しては、組にして
縦に１行づつの形式で、下方向に列挙していく。
（５）行き先は捨てるのですか。

お礼

ご返答有難うございます。エクセルエキスパートの
ｉｍｏｇａｓｉさん、いつも明解なお答えをいただき、感謝申し上げます。

　ウェンディ０２さんのところでも言いましたが、質問内容不完全のまま、クリックしてしまい、申し訳ありませんでした。

　私はＣＳＶから作成したピボットテーブルをワークシートを挿入、データー範囲を形式を値にてコピー、ペーストしていますが、合計の項目が順番を上の行、Ａ－１，Ａ－２の番号をその下の行に配置したい、（２３の２，３は何かと言うのはＡ－１は○番目に生産、Ａ－２は○番目に生産と言うことなんです。

順　　　１　　　　　２　　　　　　３　　　　合計
品番　　Ａ１　　　　Ａ２　　　　　Ｂ２　　　○○
ＺＸ　　○
ＷＸ　　　　　　　　　　　　　　　○　　　　○○
ＦＫ　　　　　　　　　○　　　　　　　　　　○○
としたいのですが、データーラベルを入れ替えてもこのレイアウトにならず、コピー、ペーストしてますが、中々上手く行きません。まだ抽象的だと思われますが、こんな感じです。

　ｉｍｏｇａｓｉさんは何故そこまでエクセルを理解されているのでしょうか？ウェンディ０２さんもそうですが、知識の深さに感心しています。有難うございました。