Exclデータベース用マクロについて

質問

データベース用マクロを作っています。
sheet1のA列の中で文字検索を行い「入社」であればsheet2にある人事データベースに検索でかかった行ごとコピーしデータベースの一番下の行に貼り付ける。「異動」であればB1の「社員コード」から検索し、新しい上書き。「退社」であれば削除を行いたいと思っています。どのようにマクロを組めばよいのでしょうか。よろしくお願いします。

回答No.5

Wendy02 · ベストアンサー

こんばんは。

シートの状態が分かりませんので、では、こちらで、想像で割り振らしていただきます。
ただ、本来は、こういう具体例をご質問者から、出していただきたいのです。

Sheet1

　　A　　　B　　　C
1　処遇　コード　名前．．．．
2　退社　　3　　　あ　　
3　異動　　4　　　か　　
4　入社　　20　　　さ　

Sheet2

　　A　　　B　　　　C
1　コード　名前　　データ1．．．
2　　3　　　あ　　　
3　　4　　　か　　　
4　　

Sub testsample()
Dim Sh1 As Worksheet, Sh2 As Worksheet
Dim r As Range, c As Range
Set Sh1 = Worksheets("Sheet1")
Set Sh2 = Worksheets("Sheet2")
For Each c In Sh1.Range("A2", Sh1.Range("A65536").End(xlUp))
Select Case c.Value
Case "入社"
　c.Offset(, 1).Resize(, 255).Copy Sh2.Range("A2").End(xlDown).Offset(1)
　'B列より、右端255行を、シート2のA列の最後尾の次にコピーする。
Case "異動"
　Set r = Sh2.Columns(1).Find(c.Offset(, 1).Value, , xlValues, xlWhole)
　If Not r Is Nothing Then c.Offset(, 1).Resize(, 255).Copy r
　'コードを検索して、その見つかったものを上書き
Case "退社"
　Set r = Sh2.Columns(1).Find(c.Offset(, 1).Value, , xlValues, xlWhole)
　If Not r Is Nothing Then r.EntireRow.Delete
　'コードを検索して、その見つかったものを削除
End Select
Next
End Sub

こういうコードを参考にして作ってみてください。

なお、こういうのは、データベースとはかなり違ったものです。Excel自体で本格的なデータベースが使えますが、プロのプログラマの知識が必要な技術のひとつです。Excelで本当にデータベースを使える人は、Access も使えますが、Accessのほうがはるかに便利で易しいです。

回答No.6

imogasi

＞データベース
エクセルで作ったデータは専門家には小さい声で、上役には大きな声で「データベース」という言葉を使いましょう。
さて内容だが
（１）Ｓｈｅｅｔ１（異動データ）とＳｈｅｅｔ２（人事データベース）が話題になっている。
（２）Ｓｈｅｅｔ１のＡ列には「入社」、「異動」、「退社」と何もなかった人の空白行がある。異動ない人はＳｈｅｅｔ１には乗っていないかも。
（３）Ｓｈｅｅｔ１のでＡ列入社であればＳｈｅｅｔ２の既存社員の最後の次の行にＳｈｅｅｔ１のデータを上から詰めて足してゆく。
（４）異動であれば、Ｓｈｅｅｔ１のＢ列にある社員コードでＳｈｅｅｔ２のＢ列（例）を検索し、見つかった行に現勤務先などを書き換える
（４）退社であれば、（３）と同じことをして、退社サインを立てる。
世の中では、普通は削除を行いません。退社後もデータが必要だからです。
例データＳｈｅｅｔ３
異動タイプ コード 氏名 勤務店
退社 123 山田 新宿
入社 301 近藤 四谷
異動 102 大川 有楽町
退社 231 大木 品川
異動 98 畑中 秋葉原
異動 67 木村 お茶
入社 302 川島 馬場
退社 25 海野 目黒
例データＳｈｅｅｔ４
コード 氏名 勤務店 移動日 退社
25 海野 品川
27 上野 品川
67 木村 蒲田
68 今野 大崎
98 畑中 横浜
100 丘 新宿
102 大川 浜松町
105 谷田 目黒
123 山田 新宿
231 大木 川口
233 今 目黒
245 木田 目白
246 越山 池袋
コード
Sub test01()
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Set Sh1 = Worksheets("Sheet3")
Set Sh2 = Worksheets("Sheet4")
d1 = Sh1.Range("A65536").End(xlUp).Row
d2 = Sh2.Range("A65536").End(xlUp).Row
d3 = d2 + 1
For i = 2 To d1
Select Case Sh1.Cells(i, "A")
Case "入社"
Sh2.Cells(d3, "A") = Sh1.Cells(i, "B")
Sh2.Cells(d3, "B") = Sh1.Cells(i, "C")
Sh2.Cells(d3, "C") = Sh1.Cells(i, "D")
Sh2.Cells(d3, "D") = "2005/4"
d3 = d3 + 1

Case "異動"
no = Sh1.Cells(i, "B")

s = "A1:A" & d2
n = WorksheetFunction.Match(no, Sh2.Range(s), 0)
Sh2.Cells(n, "C") = Sh1.Cells(i, "D")
Sh2.Cells(n, "D") = "2005/4"

Case "退社"
no = Sh1.Cells(i, "B")
MsgBox no
s = "A1:A" & d2
n = WorksheetFunction.Match(no, Sh2.Range(s), 0)
MsgBox n
Sh2.Cells(n, "E") = "退社"
Sh2.Cells(d2, "D") = "2005/4"

End Select
Next
End Sub
夜更けて、急いでやったので、不都合箇所があるかもしれませんが、質問者が修正できることを望んでいます。

お礼

皆さんありがとうございました。二方の作っていただいたものを元に勉強して自分らしく作りたいと思います。下の方もありがとうございました。

回答No.4

Wendy02

こんにちは。

#2 で書き込みした、Wendy02 です。
私の書き込みには反応がないようですが、一応、もう一度だけ書かせていただきます。

Sheet1 で
＞A列の中で文字検索を行い「入社」
とあれば、
＞検索でかかった行ごとコピーしデータベースの一番下の行に貼り付ける。

ということですが、「入社」という文字も書き込まれてしまいます。

＞「異動」であればB1の「社員コード」から検索し、新しい上書き。

こちらも、そのまま、上書きしたら、「異動」という文字まで含めることになります。

もしかしたら、B列から、最終列までなのかな？
データの状態が今ひとつ見えませんね。

補足

返信遅くなりました。
出来ればA列でデータの追加・上書き・削除を判断し、sheet2には入社などの文字は入れたくないです。
まだこの仕事は手を付け始めたばかりで大まかな考えしかなくて・・・。しかもVBEは初心者。勉強中です。
よろしくお願いします。

回答No.3

KenKen_SP

Sheet1 が人事の履歴データで Sheet2 が現在の人事データのシート。
履歴データから現在の人事データを作成したい、ということですか？

つまり、Sheet1 は履歴だから同一人物について入社・異動・退社の
複数のデータが在り得るが、Sheet2 は現在の人事データであるから
1人1データとなる、、、ということ？

A列は入社・異動・退社のデータ種別を表す？

補足

書き込みありがとうございます。
sheet1では人事異動、入社がある際に人事担当が必要情報（社員コードや氏名、部署など）を書き込み左端（A列）に入社か異動、退社）を区別するように書き込みます。人事命令がある際にsheet1は作るため履歴としては残しません。ちなみにsheet2は現在の人事データになります。
よろしくお願いします。

回答No.2

Wendy02

こんばんは。

お話の様子では、Excelを本格的なデータベースでは使っていないようですね。

本格的なものになれば、Access もExcelもあまり変わらなくなってしまいますから、使いやすいほうで使えばよいのですが、ADO やDAO の知識が必要です。一応、VBAでは上級クラスになりますが、そういうレベルまでは必要ないようにお見受けしました。

ただ、ひとつずつお話を吟味しなければなりませんね。

　現状で、どのぐらいのところまで出来ているのですか？

　A列の中の文字検索は、どのようにしたのですか？それとも、どのようにするのですか？

　人事データベースの構造は、どのようなものですか？単に、データ・テーブルになっているだけですか？検索システムは、何をお使いになるのですか？Query ですか？それとも、単に、関数なのですか？

ひとつ、アドレスを含めた具体例を提示していただいて、そこからマクロのコードを組むという形なら、ここのカテゴリでVBAを専門とする人たちなら、お話の程度なら、わけなく作ります。

ただし、DAO　とか　ODBCで行いたいとかおっしゃられると、少なくとも、プライベートで書いている私には、少し手を余します。悔しいかな、まだ至りませんが、こういうのを企業で専門にする人たちもいらっしゃいますし、VBA専門掲示板ではすぐにレスがつきます。

回答No.1

komet163

こんにちは。
社員数、部署数、組織構造etc.にもよると思いますが、
Excel よりも Access、もしくはもっと上位の
何かで管理されたらどうでしょう。

Access より上位のことは門外漢なんでわかりませんが、
例えば、異動もただ上書では、異動の履歴が残りませんし、
かなり凝ったデータ構造になると思います。
（Access でデータベース構築した場合のはなし。）

全然アドバスになってませんね。ごめんなさい (^^;

補足

ありがとうございます。
ただ上からの命令でExclときめられてまして・・・