[[20070926112026]] 『リストボックスから検索』(でこ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『リストボックスから検索』(でこ)

データの整理をするのにエクセルを使用しています。

どんどんデータが追加されているので簡単にできないものかと

エクセルの学校でどなたか似たようなことをしていないかなと

探してみたのですが、検索の仕方が悪いのでしょうが私ができたらなと

思うのと同じような事例がみつかりません。

どなたかお知恵を貸してください。

シート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)

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)

HANAさん

ありがとうございます。献立名をB5に参照することは可能です。

標準モジュールに入れてみましたがコンボボックスにリストが表示されません。

はじめは一部のセルにLISTという名前をつけてLISTFILLRANGEにLIST

としてコンボボックスにとりあえず表示させていたのでそれは消してモジュールにいれてみたのですが・・・・

seiyaさん

ありがとうございます。

同じくやってみたのですがリストが表示されません。

標準モジュールに貼り付けたら大丈夫なんですよね?

2)も登録シートに入れてやってみたのですが

型が一致しませんとなってしまいます。


 1, 2 ともにシートモジュールです。
 修正が遅かったので、もう一度コピーしてみてください。
 (seiya)

 標準モジュールに入れて、【実行】しましたか?
 コンボボックスは、コントロールツールボックスのコンボボックスですよね?
 コンボボックスを選択したときに数式バーに
 「=EMBED("Forms.ComboBox.1","")」の様に表示されると思いますが
 この名前は同じですか?
 何かエラーが出ますか?

 そうそう、文頭に半角スペースを入れると
 改行がそのまま表示されますよ。
 ←この部分に半角スペース。
 また、コメント記入の際は、その都度ご署名をお願いしますね。

 (HANA)

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

HANAさん

 何度も何度もありがとうございます。
 標準モジュールにコードをペーストしリストを設定する方法を
 していて、何かいじってしまったのか実行すると
 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)

HANAさん
 ありがとうございます。またまた初歩的なことでした。
 アクティブにしているつもりが、そのシートを見ているだけの状態でした。
 すみません。

seiyaさん

 おかげさまでseiyaさんのでもリストがでました!

 おふたりのおかげで入力が簡単にできるようになりました。
 ありがとうございます。

                (でこ)


HANAさん
 もう一度すみません。
 .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.