Excel 追加データの自動入替え

質問

No. 商品名 製造元
1　A　○社
2　B ○社
3　C　○社
4　D △社
5　E △社
6　F　×社
の3項目からなる表があるとします。※上から順に○社、△社、×社等製造元ごとに分類し、入力していました。

これから新に商品名 G、製造元 ○社を入力しようと思っているのですが、通常ならNo.3、4の間に行を挿入し項目を入力するところですが、製造元毎に分類化されていることを知らず、No.6の後に入力した場合、自動的にNo.3の後にその追加データが移動する仕組みは可能でしょうか？

分かりにくい質問であると思いますが、VBA等も駆使して実現可能であるなら、回答下さい。よろしくお願い致します。

回答No.5

noname#22222 · ベストアンサー

１、[No.]を除き、[商品名と[製造元]を並び替えよ！
２、但し、製造元の順番は、最初に出現した並びとする。

こういう設問かと。

そこで、[製造元]が入力された直後にデータを所定位置に挿入するＶＢＡを組んでみました。
挿入位置は、該当する製造元の最後です。

Option Explicit
Dim isInput　As Boolean
Dim 現製造元 As String
Dim 新製造元 As String
Const 商品名列 = 2
Const 製造元列 = 3
Const 比較開始 = 1

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim isFound　　　 As Boolean
　　Dim R　　　　　　 As Integer
　　Dim intCurrentRow As Integer

　　If isInput Then
　　　　新製造元 = Target.Cells.Value & ""
　　　　intCurrentRow = Target.Cells.Row
　　　　If 新製造元 <> 現製造元 Then
　　　　　　R = 比較開始
　　　　　　Do
　　　　　　　　If R < intCurrentRow Then
　　　　　　　　　　If Cells(R, 製造元列) = 新製造元 Then
　　　　　　　　　　　　isFound = True
　　　　　　　　　　Else
　　　　　　　　　　　　If isFound Then
　　　　　　　　　　　　　　並び替え intCurrentRow, R, 新製造元
　　　　　　　　　　　　　　Exit Do
　　　　　　　　　　　　End If
　　　　　　　　　　End If
　　　　　　　　Else
　　　　　　　　　　Exit Do
　　　　　　　　End If
　　　　　　　　R = R + 1
　　　　　　Loop Until (0)
　　　　End If
　　End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　　isInput = CBool(Target.Row >= 比較開始 And Target.Column = 製造元列)
　　If isInput Then
　　　　現製造元 = Target.Cells.Value & ""
　　End If
End Sub

Public Sub 並び替え(ByVal intCurrentRow As Integer, ByVal intInsertRow As Integer, ByVal 製造元 As String)
　　Dim I　　　As Integer
　　Dim J　　　As Integer
　　Dim K　　　As Integer
　　Dim 商品名 As String

　　商品名 = Cells(intCurrentRow, 商品名列)
　　J = intInsertRow + 1
　　For I = intCurrentRow To J Step -1
　　　　K = I - 1
　　　　Cells(I, 商品名列) = Cells(K, 商品名列)
　　　　Cells(I, 製造元列) = Cells(K, 製造元列)
　　Next I
　　Cells(intInsertRow, 商品名列) = 商品名
　　Cells(intInsertRow, 製造元列) = 製造元
End Sub

補足

すごい…ありがとうございます。
単純な質問で申し訳ありませんが、これはVBAを起動し、This Work Book内に記述すればよろしかったでしょうか？

回答No.8

noname#22222

If Verify("並び替えるべき[製造元]が入力されました。実行しますか？") =vbYes Then

=vbYesも追加して下さい。

お礼

ありがとうございました。なんとかやってみたところ、希望に沿った形で実現出来ました。

回答No.7

noname#22222

s_husky です。

急遽追加した部分でバグがありました。

If isFound Then
　　　　If Verify("並び替えるべき[製造元]が入力されました。実行しますか？") Then
　　　　 　　並び替え intCurrentRow, R, 新製造元
　　　　End If
　 　　Exit Do　<--- ここへ！！
End If

回答No.6

noname#22222

s_husky です。

１、入力しているシートのタグを右クリックしてコードを表示。

２、既定のイベントプロシージャを表示。

・上に選ぶようになっています。

Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

３、[並び替え]は、メニュー[挿入]-[プロシージャ]で。

注意１、生成、あるいは追加されたプロシージャをコピーして移動しないで下さい。

※内部のコードは、WEBをメモ帳等に移してからコピーして構いません。

＜修正すべきかなと思った点＞

現状では、誤って製造元を入力しても、有無も言わさずに並び替えます。
そこで、Verify関数を使って確認すると、多少、ユーザーに優しくなります。

If isFound Then
　　並び替え intCurrentRow, R, 新製造元
　　Exit Do
End If

If isFound Then
　　　　If Verify("並び替えるべき[製造元]が入力されました。実行しますか？") Then
　　　　 　　並び替え intCurrentRow, R, 新製造元
　　　　 　　Exit Do
　　　　End If
End If

Private Function Verify(ByVal Msg As String, _
Optional ByVal DefaultButton As Integer = vbDefaultButton1) As Integer
　　　Verify = MsgBox(Msg, vbYesNo + vbQuestion + DefaultButton, " 確認")
End Function

回答No.4

telescope

「データ」－「並べ替え」で
［最優先されるキー］に「製造元」を選んでOKボタンを押せば製造元ごとに分類されます。（No.1の回答はそういうことです）

製造元に順番があるなら、
「ツール」－「オプション」の「ユーザー設定リスト」で
［リストの項目］に
○社
△社
×社
のように一社ごとに改行して入力するか
○社,△社,×社
のようにカンマで区切って追加ボタンを押します。OKボタンを押します。

「データ」－「並べ替え」で［オプション］ボタンを押して、
［並べ替え順序の指定］で先ほど追加した「○社,△社,×社」を選べば指定した製造元の順に並びます。
No.2の回答にあるようにNoの欄に
=row()-1
と式を入れてあれば、自動的に番号が振られます。

回答No.3

noname#123709

＃1です。
並び替えでなんでオートフィルタが出てくるのか不明ですが、並び替えの
VBAは下記のような感じです。＃2の方も言ってますがタイミングが色々あり
ます。シートモジュールでしたらセルの場所が変われば動くものがあったり
ダブルクリックしたら動くものもあったりと・・・。
範囲は適当ですので適所修正して下さい。
Range("A1:C4").Sort Key1:=Range("ｃ2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin

回答No.2

mshr1962

並べ替え自体は「ツール」「マクロ」「新しいマクロの記録」で記録すれば手動で並べ替えは簡単です。
自動でとなるとVBAでWorkSheetにマクロを作成して行うことになりますがタイミングが難しいと思いますよ。

ところでNo.は上から順のままがいいのでしょうか？
その場合は、数値で入力するより
=ROW()-1
と関数で表示させたほうが楽ですよ。並べ替えても自動的に変わります。
上記の式は行番号から1を引いただけです。開始行が2行目からでない場合は数値を調節してください。

補足

ROW関数、非常に参考になりました。
ところでタイミングが難しいということですが、実際にこれを解決するためのマクロを組めることは可能でしょうか？当方、マクロを自分で組んだことがないので、よく分かりません。。。すみません、何かヒント等頂ければ幸いです。

回答No.1

noname#123709

製造元をキーとして並び替えではダメなのでしょうか？

補足

オートフィルタで製造元をキーに抽出し、追加データを手動で並び替えるということですか？