Excel VBA質問箱 IV

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

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


3565 / 13646 ツリー ←次へ | 前へ→

【61413】コピーして貼り付け nao3 09/5/7(木) 8:26 質問[未読]
【61415】Re:コピーして貼り付け ゆみこん 09/5/7(木) 9:48 発言[未読]
【61417】Re:コピーして貼り付け nao3 09/5/7(木) 12:02 質問[未読]
【61418】Re:コピーして貼り付け ゆみこん 09/5/7(木) 13:10 発言[未読]
【61419】Re:コピーして貼り付け nao3 09/5/7(木) 13:55 お礼[未読]

【61413】コピーして貼り付け
質問  nao3  - 09/5/7(木) 8:26 -

引用なし
パスワード
   エクセル初心者です。初めて質問させていただきますので、質問の仕方もよく分からないのですが、どうぞ宜しくお願いします。

Sheet1のA列に顧客コードが入っています。日によって人数は違います。
Sheet1のB列からG列まで商品名と金額が入っているとします。(以下のように)
(Sheet1)
  A     B    C   D     E   F   G
1 顧客CD 商品1 金額 商品2 金額 商品3 金額
2 1001  りんご 100  みかん  50  バナナ  150
3 1002  りんご 200  みかん  60  バナナ  200
4 1003  りんご 300  みかん  70  バナナ  250
5 1004  りんご 400  みかん  80  バナナ  300
6 1005  りんご 500  みかん  90  バナナ  350
このデータを顧客ごとの単票にしていきたいのです。

Sheet2にVlookup関数で顧客CD別に商品名・金額が反映する単票のフォームを作成しています。
(Sheet2)
   A   B     C
1 顧客CD 1001    
2 りんご みかん バナナ
3 100   50   150
このフォームを次の顧客CD 1002の単票を4行目に作成し、次の顧客を7行目に・・・と連続して顧客CDが入力され、フォームをコピーして貼り付けていきたいのです。
一人の顧客に対して、紙を一枚使うのがもったいないので、このようなことがマクロを利用してできないかと考えました。
分かりにくい質問で大変申し訳ないのですが、どうぞ宜しくお願いします。

【61415】Re:コピーして貼り付け
発言  ゆみこん  - 09/5/7(木) 9:48 -

引用なし
パスワード
   Sub try()
Dim i As Long, k As Long, m As Long
Dim v, x

v = Worksheets("Sheet1").Range("A1").CurrentRegion

ReDim x(1 To (UBound(v, 1) - 1) * 3, 1 To (UBound(v, 2) - 1) / 2)
k = 1
For i = 2 To UBound(v, 1)
   x(k, 1) = "顧客CD"
   For m = 1 To UBound(v, 2)
     If LenB(v(i, m)) > 0 Then
      Select Case True
          Case m = 1
            x(k, 2) = v(i, m)
          Case (m Mod 2) = 0
            x(k + 1, m / 2) = v(i, m)
          Case Else
            x(k + 2, (m - 1) / 2) = v(i, m)
      End Select
     End If
   Next
   k = k + 3
Next
Worksheets("Sheet2").Range("A1").Resize(UBound(x, 1), UBound(x, 2)).Value = x
Erase v, x
End Sub

ご参考になれば幸いです。

【61417】Re:コピーして貼り付け
質問  nao3  - 09/5/7(木) 12:02 -

引用なし
パスワード
   ゆみこんさん ありがとうございます。
大変参考になりました。自分で加工して使えるように試してみるのですが
なかなかうまくいきません。もう少しお力添えをいただけないでしょうか。
(Sheet1)
  A        B    C    D     E    F    G    H   
1 顧客CD   氏名  商品1  金額  商品2  金額  商品3  金額
2 1001    一郎    1    100    1    50    1    150
3 1002    二郎    2    200    2    60    2    200
4 1003    三郎    3    300    3    70    3    250
5 1004    四郎    4    400    4    80    4    300
6 1005    五郎    5    500    5    90    5    350

Sheet1のデータは顧客CD 氏名と始まり、商品は14項目あり商品名・金額・・と続きます。最後は合計金額(AE1)があり、それぞれの顧客の合計金額が入ります。


(Sheet2)                        

   A      B       C       D       E      N     O                
1 1001    一郎            
2 商品1    商品2    商品3    商品4    商品5・・・商品14  合計
3  1       1       1       1       1      1
4  100     50      150    3000     2000    4000  15000

Sheet2に上記のような帳票を顧客の数だけ連続して作成していきたいのです。
1行目に顧客CD(A1)と氏名(B1)
2行目に商品名と合計(O2)、3行目に個数、4行目に金額と合計金額(O4)
が表示される帳票にしたいのです。
はじめからこのようにお願いすればよかったです。
自分で加工して使えるようにしてみたかったのですが、まだまだ知識不足で教えていただいたものを加工することができませんでした。

お手数でなければ、上記を実行できるマクロをご教授下さい。
宜しくお願いいたします。

【61418】Re:コピーして貼り付け
発言  ゆみこん  - 09/5/7(木) 13:10 -

引用なし
パスワード
   商品は14まで必ずあるとして

Sub try2()
  Dim r As Range, rr As Range

  Set rr = Worksheets("Sheet2").Range("A1")
 
  Application.ScreenUpdating = False
  With Worksheets("Sheet1")
    ' Sheet1のA2〜A列最終行までをループ
    For Each r In .Range(.Range("A2"), .Cells(Rows.Count, 1).End(xlUp))

      ' IDと名前を転記
      r.Resize(, 2).Copy rr

      ' 商品名1〜商品名14の項目を転記
      .Range("C1,E1,G1,I1,K1,M1,O1,Q1,S1,U1,W1,Y1,AA1,AC1").Copy rr.Offset(1)

      ' ID毎の各商品個数(?)を転記
      r.Range("C1,E1,G1,I1,K1,M1,O1,Q1,S1,U1,W1,Y1,AA1,AC1").Copy rr.Offset(2)

      ' ID毎の各商品金額を転記
      r.Range("D1,F1,H1,J1,L1,N1,P1,R1,T1,V1,X1,Z1,AB1,AD1").Copy rr.Offset(3)

      ' 項目名”合計”を代入
      rr.Offset(1, 14).Value = "合計"

      ' ID毎の金額の合計を”合計”の列に代入
      rr.Offset(3, 14).Value = Application.Sum(rr.Offset(3).Resize(, 14))

      ' Sheet2のA列の行数を4行ずらす
      Set rr = rr.Offset(4)
    Next
  End With
 
  Application.ScreenUpdating = True
  Set rr = Nothing
End Sub

こちらの方がわかりやすいでしょうか?

【61419】Re:コピーして貼り付け
お礼  nao3  - 09/5/7(木) 13:55 -

引用なし
パスワード
   ゆみこん さん
本当にありがとうございます!!
解説文を入れていただいていたおかげで
ちゃんと動いてくれるように加工できました。
今後ともどうぞ宜しくお願いします。

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