Excel VBA

質問

お世話になります。
概要
Excelで入力した値をExcelへ転送させたいのですが

管理簿.xls
社員名｜社員番号｜記事｜販売数値｜
太郎　｜00120142｜　　｜140 ｜
花子 ｜12333457｜　　｜　　　　｜
山田　｜0123443 ｜ ｜ ｜
エクセル側では販売数値はランダムに入力されています

入力フォーム.xls
社員番号｜販売数値｜
12333457｜200 ｜
0123443 ｜10E　　｜
入力フォーム側では社員番号及び販売数を入力します

入力フォーム内で社員番号を入力するときにエクセル側と社員番号が一致しない
又は、重複していればエラーでそのつど返したい。

入力が終わると実行ボタンで
管理簿に販売数値を入力させたい
管理簿.xls（結果）
社員名｜社員番号｜記事｜販売数値｜
太郎　｜00120142｜　　｜140 ｜
花子 ｜12333457｜　　｜200　　 ｜
山田　｜0123443 ｜ ｜10E ｜
管理簿側はブックの共有をしており数名が開いている場合があります

複雑ですがわか方お願い致します。

回答No.6

hallo-2007 · ベストアンサー

No2,3です。
>販売件数にもともと文字等がある場合のエラーも出したいです。
ですが、ボタンを押して判断するより
C2セルに=IF(ISERROR(MATCH(A2,Sheet1!B:B,0)),"",MATCH(A2,Sheet1!B:B,0))
D2セルに=IF(C2="","",IF(INDEX(Sheet1!B:B,C2,1)="","",INDEX(Sheet1!B:B,C2,1)))
で下フィルしておくと、D列に管理簿の販売数値が見て取れます。
これも十分かと思いますが、A列の入力規則のユーザー設定で
=D2=""
で設定すると、D列が空白以外は入力できなくなります。

回答No.5

hallo-2007

NO2,3です。以下、方法だけご紹介します。
上記のコマンドボタンを製作実行結果は１行に対して１コマンドボタン
となっている為１コマンドボタンですべての行を反映させてあげたいです。
入力シートの２行目から順に上に移動して、セルが空白になるまで
繰り返す方法ですが。
i = 2
Do Until Cells(i, 2).Value = ""
Sheets("Sheet1").Cells(Cells(i,3).Value, 3).Value = Cells(i,2).Value
i = i + 1
Loop
iという変数が２から順に１つづつ増えていきます。セルのi行の2列目が空白になったら終了です。
中の１行ですが、RangeコマンドからCellsに変えました。
どちらも同じ意味ですが、CellS(行番号,列番号)で使用します。
セルの場所を指定する為に使用するコマンドです。

販売件数にもともと文字等がある場合のエラーも出したいです。
If Sheets("Sheet1").Cells(Cells(i,3).Value, 3).Value="" Then
Sheets("Sheet1").Cells(Cells(i,3).Value, 3).Value = Cells(i,2).Value
Else
Msgbox ("既に値が入っています。")
Cells(i, 3).Interior.ColorIndex = 8
End If
と、条件で値を入れるか、メッセージを出すかの判断を組み込めばよいと思います。一応、既に値が入っていた場合には、セルの色変えるようにしてあります。
もし、エラーやご希望と違えば、作成されたVBAを貼り付けてもらうと、
間違いなど探せると思います。

回答No.4

TTak

こんにちは。

すみません。私の環境？XP+EXCEL2000ですが、管理簿.xls[共有]に対し、入力フォーム.xlsのVBAから書き込み＞保存をかけると、ブックの共有が解除されるようです（[共有]の表示は残りますが）。

よって、#1の方法を提示した次第です。もしBSR123さんの環境で可能であれば、共有機能でもよいかと思われます。

社員番号にない入力を規制するマクロ例は下記のとおりです。
このマクロ例は、入力フォーム.xlsのモジュールに記述した場合です。
入力フォーム.xlsのSheet1のセルA1に入力された社員番号に対し、管理簿.xlsのSheet1のセルB2:B100に入力された社員番号に一致するものがなければ、処理を中断するものです。

Sub Macro1()
Windows("管理簿.xls").Activate
With ThisWorkbook.Sheets(1)
　.Range("A2").Formula = _
　"=COUNTIF([管理簿.xls]Sheet1!$B$2:$B$100,A1)"
　If .Range("A2").Value = 0 Then
　　MsgBox "入力された社員番号はありません"
　Else
　　'-ここに転記マクロ記入
　　ActiveWorkbook.SaveAs
　End If
　.Range("A2").Value = ""
End With
End Sub

お礼

お返事ありがとうございます

補足

お返事ありがとうございます
この方法だと、どの位置（セル）でまちがっているのか？
入力間違い？
入力したけど販売件数にもともと値が入っているか？
などわからないと思われます
しかしながらこれは有力な情報ありがとうございます

回答No.3

hallo-2007

取り合えず、同じブックの別シートで、入力シートから反映させる方法ですが、非常に簡単ですので、ご紹介だけしておきます。
新しいブックで試してみてください。
Sheet1に
社員名｜社員番号｜記事｜販売数値｜
太郎　｜00120142｜　　｜140 ｜
花子 ｜12333457｜　　｜　　　　｜
山田　｜0123443 ｜ ｜ ｜
があったとして
別シートに
社員番号｜販売数値｜作業列
12333457｜200 ｜
で、C2セルには、=MATCH(A2,Sheet1!B:B,0)
と入れておくと、A1で入れた社員番号がSheet1の何行目にあるか行数を返します。
その隣にでもマクロボタンを作成して
Sub ボタン1_Click()
Sheets("Sheet1").Cells(Range("C2"), 3).Value = Range("B2").Value
End Sub

と作成してみてください。内容はわずか1行でし、難しくもないと思いますので、応用してみてください。
A1セルには、Sheet1の社員番号の列で入力規則を設定しておくと誤入力防止になります。

別ブックからでも応用は出来ると思いますが、現在、共有で出来ているのであれば、これで十分のような気がします。

ただ、ブックの共有はファイルが非常に大きくなりますので将来については、十分ご検討ください。
例えば、現在のファイルを別にコピィして、ブックの共有をはずして保存したファイルと、ファイルの大きさを比較してみると良くわかると思います。
前スレで紹介したように、別シートにデータを縦方向に追加して行き、
結果を集計のシートにSUMIFなどで表示したほうが良いような気がします。

お礼

お返事まことにありがとうございました

補足

お返事ありがとうございます
これです、こんなのがほしかったです。
本当に有力な情報ありがとうございます。
ただもう2つ問題があります
HITすれば管理簿の番号が入力される
HITしない場合は下記エラー
＃N/A
ここまではOKです
HITしているのだけど販売件数にもともと文字等がある場合のエラーも出したいです。

又、VBAのボタンですが
Sheets("Sheet1").Cells(Range("C2"), 4).Value = Range("B2").Value
上記のコマンドボタンを製作実行結果は１行に対して１コマンドボタン
となっている為１コマンドボタンですべての行を反映させてあげたいです。

まことに申し訳ございませんがもう少しのご教授宜しくお願い申し上げます。

回答No.2

hallo-2007

>管理簿側はブックの共有をしており数名が開いている場合があります
すでに、管理簿ブックで運用されていると思いますが、管理簿ブックは、
複数が編集したりされているのですか？
基本的には、日々、社員番号で販売数が入力されて、販売数値が加算されていくとは違いますか？
ご存知かと思いますが、エクセルでは、データを保存することで、ほかの人へ編集した結果を伝えます。同時に、同じセルを編集した場合の問題が常についてきます。
この様な環境でればアクセスに乗り換えたほうが便利かと思います。
役に立つかどうかはわかりませんが、過去スレ紹介いたします。
参考にしてください。

参考URL：

http://oshiete1.goo.ne.jp/qa3775393.html

お礼

お返事ありがとうございます

補足

お返事ありがとうございます
はい現状はブックの共有して、多数のPCが書き換えても、問題はありません
現在同じセル位置での編集はないはずなので
VBA内でのVLOOKかなと思い始めました
ブックの共有で閉じるときに他のユーザの更新も反映させてあげれば問題ないと思っております

関数でのVLOOKUPは管理簿自体に書き込まなければならないので
入力フォーム用からの書き込み、が理想なんですが・・・

回答No.1

TTak

入力フォーム.xlsに入力されたデータ「販売数値」を、「社員番号」をキーとして、管理簿.xls　に転記。また、入力フォーム.xlsの社員番号入力タイミングで、社員フィールドに無い番号はエラーを出す機能も付けたいということと察します。

これらはExcelVBAでもできないことはないですが、管理簿.xlsを複数の人が閲覧している状況で、入力フォーム.xls側から書き込みを掛けることはできません。

よって、構成としては閲覧用の管理簿ファイルの他に、管理簿データ本体のファイルが必要かと思われます。入力フォーム.xlsからの書き込みは、データ本体ファイルに行い、閲覧する人は、閲覧用の管理簿.xlsを開くと同時に最新のデータファイルの内容を「外部データの取込」で開くのが良いのではないでしょうか。「外部データの取込」ではクエリも使えるので、閲覧データの絞り込みなども有る程度できます。

社員フィールドに無い番号を入力させないようにするには、COUNTIF関数などを使って、データファイルの社員番号フィールドを範囲指定し、返値が0ならば、他のセルにメッセージを出すか、Changeイベントなどを使って、その返値により条件分岐させる方法が考えられます。

お礼

お返事ありがとうございます
今しばらくの間ご教授願います

補足

お返事ありがとうございます
＞入力フォーム.xlsに入力されたデータ「販売数値」を、「社員番号」をキーとして、管理簿.xls　に転記。また、入力フォーム.xlsの社員番号入力タイミングで、社員フィールドに無い番号はエラーを出す機能も付けたいということと察します。

はいご理解ありがとうございます

＞これらはExcelVBAでもできないことはないですが、管理簿.xlsを複数の人が閲覧している状況で、入力フォーム.xls側から書き込みを掛けることはできません。

そうですか？
私はできそうな気がするのですが、複数の人が管理簿.xlsを開いているのにはブックの共有を有効にしているので複数の人が同じセルに書き込まない限り可能ではないでしょうか？

＞社員フィールドに無い番号を入力させないようにするには、COUNTIF関数などを使って、データファイルの社員番号フィールドを範囲指定し、返値が0ならば、他のセルにメッセージを出すか、Changeイベントなどを使って、その返値により条件分岐させる方法が考えられます。

VBAに関して初心者です
よろしければコードがあれば助かります。