Excel VBA質問箱 IV

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

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


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

【68577】他シートを参照しているデータの並べ替え すず 11/3/27(日) 1:54 質問[未読]
【68578】Re:他シートを参照しているデータの並べ替え マジ山レス次郎 11/3/27(日) 4:25 発言[未読]
【68590】Re:他シートを参照しているデータの並べ替え UO3 11/3/28(月) 10:18 発言[未読]
【68596】Re:他シートを参照しているデータの並べ替え UO3 11/3/28(月) 18:25 発言[未読]
【68603】Re:他シートを参照しているデータの並べ替え すず 11/3/28(月) 23:21 発言[未読]

【68577】他シートを参照しているデータの並べ替え
質問  すず  - 11/3/27(日) 1:54 -

引用なし
パスワード
   よろしくお願いいたします。

Sheet1にはこのようなデータが入っているとします。

      A列     B列
1行目  取引先名   拠点名
2行目  あそな銀行  東京、仙台、大阪
3行目  いつびし銀行 東京、千葉、神奈川
4行目  えずほ銀行  東京、埼玉、栃木
      :     :  :  :

こちらは、常にA列の取引先名順に並んでおり、B列の拠点名は拠点数を集計したりする関係で、「Sheet2」に入力用のシートがあり、そちらから入力した拠点名をカンマで区切った状態で表示させています。

Sheet2はこのような感じで入っています。

     A列     B列   C列   D列
1行目  取引先名   拠点1  拠点2  拠点3
2行目  あそな銀行  東京   仙台  大阪
3行目  いつびし銀行 東京   千葉  神奈川
4行目  えずほ銀行  東京   埼玉  栃木

つまり、Sheet1のB列は、「=Sheet2!B2&","&Sheet2!C2&","&Sheet2!D2」 
という式が入っています。
※Sheet1の表は「リスト」形式にしてあります。

そこで、やりたいことは、ここでSheet1に「うみとも銀行」が追加されたとします。
その場合は、まず5行目の最終行に取引先名を追加して、そのあとA列を基準に並べ替えをしようと考えていますが、リストにしてあるので、自動的に上の式がコピーされてきます。
そして並べ替えると、「うみとも銀行」は4行目に来るわけですが、そうするとB列の式はB5を参照してしまいます。

ここでなんとかSheet2のほうもSheet1と連動して並べ替えられないかと思っておりますが、どのようにしてよいかわかりません。

たとえば、Sheet2のA列の取引先名を「=Sheet1!A2」というようにしてリンクさせ、B〜D列まで入力されているものはそのまま整合性を保って、Sheet1で並べ替えたと同時にSheet2にも反映され、「空白行を作ってくれる」というようなことはできませんでしょうか?

もしよろしければご教示ください。
お願いいたします。

【68578】Re:他シートを参照しているデータの並べ...
発言  マジ山レス次郎  - 11/3/27(日) 4:25 -

引用なし
パスワード
   VBA? ワークシート関数?

【68590】Re:他シートを参照しているデータの並べ...
発言  UO3  - 11/3/28(月) 10:18 -

引用なし
パスワード
   ▼すず さん:

こんにちは

浅学にて、

>※Sheet1の表は「リスト」形式にしてあります。

ということが、具体的にどういうことなのかがわからないのですが、
たとえば、発想をかえて、
・データメンテナンスは入力用シートの「Sheet2」で行う。
・メンテナンスが終われば、Sheet2をソートするとともに、Sheet1を【再作成】する
 マクロを走らせる。
こんな構えではいかがでしょうか?

【68596】Re:他シートを参照しているデータの並べ...
発言  UO3  - 11/3/28(月) 18:25 -

引用なし
パスワード
   ▼すず さん:

上でご提案した方式の場合のコード案です。

Sub Sample()
  Dim c As Range, myA As Range
  Dim x As Long
  Dim v As Variant
  Dim s As String
  
  Application.ScreenUpdating = False
  
  With Sheets("Sheet1")
    With .UsedRange
      Set myA = Intersect(.Cells, .Offset(1))
      If Not myA Is Nothing Then myA.ClearContents
    End With
    Set myA = .Range("A2")
  End With
  
  With Sheets("Sheet2")
    .Cells.Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes
    For Each c In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
      x = .Cells(c.Row, .Columns.Count).End(xlToLeft).Column
      v = c.Offset(, 1).Resize(, x - 1).Value
      If IsArray(v) Then
        v = WorksheetFunction.Index(v, 1, 0)
        s = Join(v, ",")
      Else
        s = v
      End If
      myA.Value = c.Value
      myA.Offset(, 1).Value = s
      Set myA = myA.Offset(1)
    Next
  End With
  
  Set myA = Nothing
  Application.ScreenUpdating = True
      
End Sub

【68603】Re:他シートを参照しているデータの並べ...
発言  すず  - 11/3/28(月) 23:21 -

引用なし
パスワード
   UO3様

素晴らしいご回答ありがとうございます。
リストというのは、データメニューのリストから作成する、特別な表形式です。
特にこちらでなければいけないということではありませんが、PCに不慣れな人が途中でデータを追加するのに、何かと分かりやすいので、この形式にしてあります。

ところで、こちらのコードを試してまいりたいと思います。
ご報告が少し遅くなるかもしれませんが、また結果をお知らせいたします。

またよろしくお願いいたします。

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