[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『リストボックスから検索』(でこ)
データの整理をするのにエクセルを使用しています。
どんどんデータが追加されているので簡単にできないものかと
エクセルの学校でどなたか似たようなことをしていないかなと
探してみたのですが、検索の仕方が悪いのでしょうが私ができたらなと
思うのと同じような事例がみつかりません。
どなたかお知恵を貸してください。
シート1に入力済のものとして献立一覧があります。(献立名・材料・分量)
この一番上の行には献立分類名(汁物、焼物等)があります。
これでシート2に登録画面を作り、コンボボックスとコマンドボタンとをそれぞれひとつ作りました。
出来たらなと思うことは
@献立分類名でコマンドボックスのリストを作りたい(A1:Y1にデータが入ってます)
自分でやってみたのですが間に空白があってうまくリストが作れません。
Aコマンドボタンを押すとコンボボックスで選んだ献立分類名の列に入力されている一番
下の行に登録画面で入力した材料と分量をコピーしたい。
D2に献立名を入力していてそれだけならコピーすることが出来たのですが
↓これで この時のコンボボックスはリストを別に作ってためしました
With ComboBox1
If .ListIndex = -1 Then Exit Sub Application.GoTo Sheet1.Cells(1, .ListIndex + 1) setupSheet.Cells(lastLineInteger + 2, 1) = sheet2.Range("D2").Value
材料と分量で2列ですが献立によって何行になるかわからなくて入力されているのをコ ピーして貼り付けたいのです。
@について With Sheets("sheet2").ComboBox1 For n = 1 To Columns("ay").Column If Cells(1, n) <> "" Then .AddItem Cells(1, n) End If Next n End With Aについて With Sheets("sheet2") 目的の列 = WorksheetFunction.Match(.ComboBox1.Value, .Rows("1:1"), 0) 最下行 = .Cells(Rows.Count, 目的の列).End(xlUp).Row End With でそれぞれ目的の列No及びその最下行がもとめられます。 (弥太郎) 追伸、今からチョットでかけます。見当違いの回答なら無視してくらはい。
実行すると@のfor n でコンパイルエラーがでます。
コンボボックスのリストを作りたい行がシート1の一番上を指定するにはどうしたらよいでしょうか?
Aをどうやって使っていいのかわからないのですが
教えていただけないでしょうか?
すみません
(でこ)
それはただ単にSheet名がちゃうだけの話とちゃいまんのん? それとかCombobox1のオブジェクト名が変わっているとか? (弥太郎)
各シート名と、どのシートに何が入力されているのか 各ボタン等があるシート名 実際にどの様なデータを入力し、どの様に蓄積したいのかのサンプル
このあたりを交えながら、もう一度ご説明してみるのはどうでしょう。 弥太郎さんは、口は悪いですが良い人ですから、怖がらなくて大丈夫ですよ。
(HANA)
ちょっとこわくて遠ざかっていましたが
やっぱりもうち一度チャレンジしようと検索していたら
自分の質問にぶつかりました。
すみませんがもう一度よろしくおねがいします。
@シート1の名前は材料一覧でシート2の名前は登録です。
登録シートのコンボボックスのリストは材料一覧シートのA1〜Y1(空白あり)
で作りたいです。
Aも随分と説明不足でした。
登録シートのC5とD5から下に最高20行くらい材料と分量を入力します(何行になるかわかりません)
材料一覧シートの配列
A D G
煮物 汁物 麺類
肉じゃが 肉 30 味噌汁 みそ 5 ラーメン 麺 30
人参 20 ねぎ 5 しなちく10
登録シートのコンボボックスで選択した献立分類名(煮物とか汁物など)の
データが登録されている一番下の行にC5D5から下に入力されたデータをコピーして
貼り付けたいのです。
ここのデータをもとにして他のシートで献立の検索を出来るようにしたいのです
検索だけはVLOOKUPで出来るようになりました。
今は材料一覧シートの一番下を探しては入力するの繰り返しをしているのですが
献立分類によっておわりの場所が遠く探すのが大変なのでこんな感じのものが出来たらな
とこちらでいろいろと調べたりしていたのですがうまくいかなくて。
1) もっと詳細なデータの配置 (配置を変更したほうが早い?) 2) リストボックスにどのように表示したいのか
を提示していただかないと難しいでしょう。 (seiya)
>データが登録されている一番下の行にC5D5から下に入力されたデータをコピーして >貼り付けたいのです。 貼り付けるのは 材料(C5以降)と分量(D5以降)だけでよいのですか? 献立名(D2)も要るのですよね?
たとえば、B5に献立名をセル参照出来ますか? (貼り付けるのと同じ形を先に作っておけば あっちへ行ったり、こっちへ行ったりしなくても コピペだけですみますが・・・・。)
リストの方は、シート名をそれぞれ入れて 標準モジュールにこんなのではどうでしょう。 Sub コンボボックス更新() With Sheets("登録").ComboBox1 .Clear For n = 1 To 25 If Sheets("材料一覧").Cells(1, n) <> "" Then .AddItem Sheets("材料一覧").Cells(1, n) End If Next n End With End Sub
(HANA)
1) Private Sub Worksheet_Activate() Me.ComboBox1.List = _ Split(WorksheetFunction.Trim(Join(["transpose(transpose(sheet2!a1:y1))"]))) End Sub
2) Private Sub CommandButton1_Click() With Me.ComboBox1 '<- 誰だか知らないけど修正ありがとう If .ListIndex = -1 Then Exit Sub x = WorksheetFunction.Match(.Value, Sheets("sheet2").Range("a1:y1"),0) + 1 'この行の修正は違ってた End With Sheets("sheet2").Cells(Rows.Count, x).End(xlUp)(2).Value = me.Range("d2").Value End Sub
かな? (seiya)
ありがとうございます。献立名をB5に参照することは可能です。
標準モジュールに入れてみましたがコンボボックスにリストが表示されません。
はじめは一部のセルにLISTという名前をつけてLISTFILLRANGEにLIST
としてコンボボックスにとりあえず表示させていたのでそれは消してモジュールにいれてみたのですが・・・・
seiyaさん
ありがとうございます。
同じくやってみたのですがリストが表示されません。
標準モジュールに貼り付けたら大丈夫なんですよね?
2)も登録シートに入れてやってみたのですが
型が一致しませんとなってしまいます。
1, 2 ともにシートモジュールです。 修正が遅かったので、もう一度コピーしてみてください。 (seiya)
標準モジュールに入れて、【実行】しましたか? コンボボックスは、コントロールツールボックスのコンボボックスですよね? コンボボックスを選択したときに数式バーに 「=EMBED("Forms.ComboBox.1","")」の様に表示されると思いますが この名前は同じですか? 何かエラーが出ますか?
そうそう、文頭に半角スペースを入れると 改行がそのまま表示されますよ。 ←この部分に半角スペース。 また、コメント記入の際は、その都度ご署名をお願いしますね。
(HANA)
ありがとうございます。実行するのを忘れていました。 半角の件もありがとうございます。ちゃんと読むと書いていますね・・・
seiyaさん
勝手に修正してしまってすみません。しかも間違っているのに・・・おはずかしいです。 1)をシートモジュールにペーストしてみたのですが、やはりリストが出てきませんでした。 2)d4→d2に変えてやってみたところ、コンボボックスで選択した献立分類の一番下に はd2の値が反映されました。
半角の件もですがもう一つ ●コメント記入の際は、その都度ご署名をお願いしますね。
それから、他の方の書き込みを勝手につつくと、後で分からなくなりますので 一度ご自身のコメントに引用して、その中で書き換えるのが良いと思いますよ。 変更箇所が後から分かるような形にしておくと、変更が妥当かどうかの アドバイスももらえると思います。
リストの更新は度々無いだろうと思ったので、標準モジュール用にしましたが 更新を忘れるといけませんので、シートが選択されたときに 実行された方が良さそうですね。 標準モジュールの方は削除して、↓を両方とも 「登録」のシートモジュールに貼り付けてみてください。
'----リストの更新---- Private Sub Worksheet_Activate() Dim n As Long With Sheets("登録").ComboBox1 .Clear For n = 1 To 25 If Sheets("材料一覧").Cells(1, n) <> "" Then .AddItem Sheets("材料一覧").Cells(1, n) End If Next End With End Sub
'----献立転記---- Private Sub CommandButton1_Click() Dim 目的の列 As Long, 最下行 As Long, 入力した行 As Long Dim 範囲 As Range, 名前 As Name
入力した行 = Cells(Rows.Count, 3).End(xlUp).Row
If ComboBox1.Value = "" Then MsgBox "献立分類を選んでください。" Exit Sub End If If Range("D2") = "" Then MsgBox "献立名を入力してください。" Exit Sub End If If 入力した行 = 4 Then MsgBox "献立を入力してください。" Exit Sub End If
With Sheets("材料一覧") 目的の列 = WorksheetFunction.Match(ComboBox1.Value, .Rows("1:1"), 0) 最下行 = .Cells(Rows.Count, 目的の列 + 1).End(xlUp).Row
If 最下行 + 1 + 入力した行 - 4 > Rows.Count Then MsgBox "最終行を超えるので転記出来ません。" Exit Sub End If
Set 範囲 = .Cells(最下行 + 1, 目的の列 + 1).Resize(入力した行 - 4, 2) On Error Resume Next Set 名前 = ActiveWorkbook.Names(Range("D2").Value) If Err = 0 Then If (MsgBox("この名前は使われています。" & Chr(10) & "再定義しますか?", vbYesNo) = vbNo) Then Range("D2").Select Exit Sub Else 名前.RefersTo = "=材料一覧!" & 範囲.Address 名前.Visible = True End If Else ActiveWorkbook.Names.Add Name:=Range("D2").Value, RefersTo:="=材料一覧!" & 範囲.Address, _ Visible:=True Err.Clear End If On Error GoTo 0
.Cells(最下行 + 1, 目的の列).Value = Range("D2").Value .Cells(最下行 + 1, 目的の列 + 1).Resize(入力した行 - 4, 2) = Range("C5:D" & 入力した行).Value End With
Range("D2,C5:D" & 入力した行).Clear Range("D2").Select End Sub
上手く行きましたら >献立名をB5に参照 は、不要の方向で。(笑)
(HANA) 済みません、変数の宣言を忘れていたので追記しました。10/30 22:43 名前の定義を追加しました。11/01 0:35 On Error GoTo 0 追加がてら、少し変更しました。11/01 15:33
何度も何度もありがとうございます。 標準モジュールにコードをペーストしリストを設定する方法を していて、何かいじってしまったのか実行すると for nのところでコンパイルエラー プロジェクトまたはライブラリーがみつかりませんと 表示されるようになりました。
このせいもあるのか今、新たに登録シートのモジュールに上記の コードをペーストしてもリストが表示されません。
(でこ)
ちなみに、登録シートがアクティブになったときに コードが実行されます。 一度違うシートを選択してから、もう一度 登録シートを選択してみても駄目ですかね?
また、新しいブックで試してみたらどうなりますか? もちろんシート名を合わせて、 材料一覧シートの1行目をコピペして 登録シートにコンボボックスを設置 する必要がありますが。
(HANA)
この様なことを意図して居られるのかどうかは分かりませんが seiyaさんの1)の方は With WorksheetFunction Me.ComboBox1.List = _ Split(.Trim(Join(.Transpose(.Transpose(Sheets("sheet2").Range("a1:y1")))))) End With とすれば、リストが更新されるようです。
ちなみに、私のは たとえば「煮物」を選んでいた状態で材料一覧シートへ移動し 「煮物」→「煮物1」に変更後、登録シートへ戻った場合 コンボボックスは空欄になり、▼で「煮物1・汁物・麺類」が選べます。
Me.ComboBox1.List = で更新した場合 コンボボックスは「煮物」が表示されたままのようです。 もちろん▼で「煮物1・汁物・麺類」が選べます。
(HANA)
あっ、 "が余計でした。 Private Sub Worksheet_Activate() Me.ComboBox1.List = _ Split(WorksheetFunction.Trim(Join([transpose(transpose(sheet2!a1:y1))]))) End Sub (seiya)
あっっ、seiyaさん これですね! [[20070212134849]]『Evaluate Method の研究』(seiya) 今なら少しは分かるかも〜♪
でこさん、勝手にリンクごめんなさいね。 ちょっとはらせといてください。
(HANA)
HANAさん、そうなんです。 どうも [ ](Evaluate method のショートカット) と Evaluate の記述が混乱してしまって... (seiya)
ありがとうございます。またまた初歩的なことでした。 アクティブにしているつもりが、そのシートを見ているだけの状態でした。 すみません。
seiyaさん
おかげさまでseiyaさんのでもリストがでました!
おふたりのおかげで入力が簡単にできるようになりました。 ありがとうございます。
(でこ)
もう一度すみません。 .Cells(最下行 + 1, 目的の列 + 1).Resize(入力した行 - 4, 2) = Range("C5:D" & 入力した行).Value ここでデータが入力された2列×何行かを範囲指定して名前をつけるには Range(Cells(最下行 + 2, 目的の列 + 1).Resize(入力した行 - 4, 2)).Select ActiveWorkbook.Names.Add Name:=Range("D2").Value これじゃあダメなんですね。 どのようにしたらいいでしょうか?
(でこ)
えっと・・・遅くなってごめんなさい。 漸く出来たっぽいので、上のコードを変更しておきます。 上手く行かなかったら、またお知らせ下さい。
名前が既に登録されている場合は、メッセージが出ます。 (この名前は使われています。再定義しますか?) [ No ]を選ぶと処理が終了しますので、D2セルの値を変更して下さい。 [ Yes ]を選ぶと再定義されますが、以前定義されていた範囲のデータは そのまま残ります。
(HANA) 11/01 0:35 コードを少し変更しました。11/01 15:33
> 材料と分量で2列ですが... 見逃してました。
Private Sub CommandButton1_Click() With Me.ComboBox1 '<- 誰だか知らないけど修正ありがとう If .ListIndex = -1 Then Exit Sub x = WorksheetFunction.Match(.Value, Sheets("sheet2").Range("a1:y1"),0) + 1 'この行の修正は違ってた End With Sheets("sheet2").Cells(Rows.Count, x).End(xlUp)(2).Resize(,2).Value = me.Range("c2:d2").Value End Sub (seiya) Me.Range("c2:d2").Value の範囲はよくわからなかったので適当です。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.