エクセルVBAのChangeイベントで質問です。

質問

会社名をプルダウンより選択すると、その下の複数の行に
関数が設定してあり、〒番号、住所等が表示されるように
しています。しかし、そのデータ情報に手直しが必要な
場合もあるため、これをChangeイベントというので、でき
ないかと思い質問に参りました。

会社名、住所等の情報は「データ」シートにまとめてあります。
A1 No.
B1 会社名
C1 郵便番号
D1 住所1
E1 住所2
F1 住所3
G1 住所4
H1 電話番号
I1 FAX番号
と書いて、その下の行に各社のデータを入力してあります。

「入力」シートの
J24セル(結合セルで実際にはJ24:T25です)にプルダウンを設定して
会社名が選択できるようにしています。
ここで会社名を選択すると、下の行にズラッと〒、住所、電話番号、
FAX番号を入力できるようにしたいのですが、教えていただけない
でしょうか。
入力したいセルも結合セルです。
郵便番号=J26:T26
住所1=J27:T27
住所2=J28:T28
住所3=J29:T29
住所4=J30:T30
電話番号=J31:T31
FAX番号=J32:T32
宜しくお願いします。

回答No.5

mitarashi

入力用シートに設けたComboBoxのListFillRangeに、データシートのB列の会社名リストをセットし、
１．会社名を新規に打ち込んだときは、新しいレコードの入力を行い
２．会社名をリストから選択した時は、ComboBox_Changeイベントで、該当する情報をデータシートから読み出して入力用シートに表示して、レコードの修正が出来る様にして、
３．別途設けた書き込みボタンでもクリックすると、データシートへの新しいレコードの追記または、修正したレコードとの差し替えが出来るようにしたいという事でしょうか。
２についてだけ回答させていただくと、
データシートのB列に、
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
といった動的な名前をつけておき、これをComboBoxのListFillRangeに設定してやります。ComboBox_Changeイベントで、ListIndexにリストの何番目が選択されたかが得られますので、それに2を足してやると、データシートの該当する会社の存在するRow番号が得られます。これを用いて、#4さんの回答の様に、入力用シートに転記すれば良いと思います。
Range("J27").Value = Worksheets("データ").Cells(ComboBox1.ListIndex + 2, 3).Value
という様に。
すべては説明しきれませんので、ご自分で手を動かしてみて、ご不明な点をご質問下さい。

回答No.4

hallo-2007

検査値で、データを表示させたり、シートで編集した内容をデータシートに保存したいということでしょうか。
簡単なVBAにするために　１つ　作業用のセルを使った例です。
空いているセル（仮にI25）に　=MATCH(J24,データ!B:B,FALSE)
と入れておくと、検索値の行番号が表示されます。
これをVBAで使用すると、コードがシンプルになります。

以下は、検索したデータを表示します。
Sub Macro1()
Range("J27").Value = Worksheets("データ").Cells(Range("I25").Value, 3).Value
Range("J28").Value = Worksheets("データ").Cells(Range("I25").Value, 4).Value
Range("J29").Value = Worksheets("データ").Cells(Range("I25").Value, 5).Value
Range("J30").Value = Worksheets("データ").Cells(Range("I25").Value, 6).Value
Range("J31").Value = Worksheets("データ").Cells(Range("I25").Value, 7).Value
Range("J32").Value = Worksheets("データ").Cells(Range("I25").Value, 8).Value
End Sub
以下は、編集した内容をデータのシートに入れます。
Sub Macro2()
Worksheets("データ").Cells(Range("I25").Value, 3).Value = Range("J27").Value
Worksheets("データ").Cells(Range("I25").Value, 4).Value = Range("J28").Value
Worksheets("データ").Cells(Range("I25").Value, 5).Value = Range("J29").Value
Worksheets("データ").Cells(Range("I25").Value, 6).Value = Range("J30").Value
Worksheets("データ").Cells(Range("I25").Value, 7).Value = Range("J31").Value
Worksheets("データ").Cells(Range("I25").Value, 8).Value = Range("J32").Value
End Sub
１と２は　=の前後が変わるだけですので理解してもらえると思います。
１は、Changeイベントにしても良いですし、２は、ボタンを作成して標準モジュールに記述してください。
Loopで処理するともう少し短くなりますが、勉強用と思ってください。

回答No.3

kuma56

んん(-_-?)
やりたいことが今一はっきりしないようですが・・・・・
現在"データ"と"入力"の二つのシートがある。
"データ"シートには、A列からI列まで、それぞれを項目として、複数行のデータが入っている。
"入力"シートには、J24セル(J24:T25の結合)にプルダウンが設定してあり会社名が選択できる。
ここまではわかるのですが、質問文の１～３行目には
>その下の複数の行に関数が設定してあり、〒番号、住所等が表示されるようにしています。
っとなっていますが、20行目からは
>ここで会社名を選択すると、下の行にズラッと〒、住所、電話番号、FAX番号を入力できるようにしたいのですが・・・・・
っとなっています。そして
>入力したいセルも結合セルです。
郵便番号=J26:T26
住所1=J27:T27
住所2=J28:T28
住所3=J29:T29
住所4=J30:T30
電話番号=J31:T31
FAX番号=J32:T32
となっています。
この入力したいセルと言うのは、質問文の１～３行目にある"その下の複数の行に関数が設定"されているセルの事でしょうか？

やりたいことと言うのは、Ｊ24セルのプルダウンからＡ社を選択すると、その下に登録されている住所等が表示される。
しかし、Ａ社は最近転居したので、表示されたデータ（"データシート"のデータ）の住所が正しくなくなっている。
そこで、現在関数式で表示されているJ26:T26～J32:T32セルのデータのうち、修正が必要なものは、関数式の上から直接データを書き込んでやって、それをChangeイベントでマクロを走らせて"データシート"のデータを修正したい・・・・・・
っと言う事でしょうか？？。
それならば、J26:T26～J32:T32セルにそれぞれ Worksheet_Change イベント を設定すれば可能でしょう。
マクロの内容としては、

・該当セルのコピーを取る。
・"データシート"のＢ１セルを選択する。
・もし　Ｊ24セル <> activecell ならば、activecell offset(1.0）・・・これを、 Do.....Loop で Ｊ24セル = activecell になるまで繰り返す。
・郵便番号なら activecell offset(0.1） 住所１なら activecell offset(0.2） FAXなら activecell offset(0.7）
・貼り付け
・"入力シート"を選択
・郵便番号なら J26:T26セルに、 住所１なら J27:T27セルに、 FAXなら J32:T32セルに、それぞれ正しい関数を入力

こんなのをＶＢＡの構文にしてやればできそうな気がしますが、いかがでしょうか？

もっとも、そんなことしないで"データシート"のデータを直接修正する方が良いような気もします。
データを探すのが大変ならば、上記の
・"データシート"のＢ１セルを選択する。
・もし　Ｊ24セル <> activecell ならば、activecell offset(1.0）・・・これを、 Do.....Loop で Ｊ24セル = activecell になるまで繰り返す。
この部分だけ応用して、ボタンなどでマクロが走るようにしておけばいいだろうし、
マクロにしなくても、条件付き書式で、＝Ｊ２４でセルに色が付くなど工夫すれば、データを見つけるのもそれほど難しいことではないと思います。

また関数式でなく、マクロでデータをコピーしてくるなら、やはり上記のマクロの部分を使って、データシート上のデータを特定してコピーを取って、入力シートに持ってくれば出来ると思います。

参考URL：

http://www.happy500z.com/YNxv9c7.html

回答No.2

ASIMOV

VLOOKUP で、いけると思いますが．．．どうでしょう

J26
=IF(J$24="","",VLOOKUP(J$24,データ!B$2:I$100,2,0))　<- "100"はデーターに合わせる
J27
=IF(J$24="","",VLOOKUP(J$24,データ!B$2:I$100,3,0))　<- 以下、右端の"3"を4,5,6～と増やす
..
.
J32
=IF(J$24="","",VLOOKUP(J$24,データ!B$2:I$100,8,0))

＞そのデータ情報に手直しが必要な場合もあるため

データーシートを変更すれば、入力シートも変ります

お礼

こんばんは。VLOOKUPでやっていましたが、
＞そのデータ情報に手直しが必要な場合もあるため
というのは結構頻繁に変わるとこや、諸般の事情により
そこに関数設定がないほうがいい状態にしたいからです。

回答No.1

hige_082

意味が良くわかりませんが
＞これをChangeイベントというので
何に対してのChangeイベントなの？
もう少し勉強なされてから質問されては