Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


4198 / 13644 ツリー ←次へ | 前へ→

【57882】テキストボックスに条件分岐で検索値を表示 takeshi 08/9/18(木) 22:33 質問[未読]
【57883】Re:テキストボックスに条件分岐で検索値を... neptune 08/9/18(木) 22:46 発言[未読]
【57884】Re:テキストボックスに条件分岐で検索値を... takeshi 08/9/18(木) 23:17 発言[未読]
【57904】Re:テキストボックスに条件分岐で検索値を... neptune 08/9/19(金) 17:23 回答[未読]
【57906】Re:テキストボックスに条件分岐で検索値を... kanabun 08/9/19(金) 19:43 発言[未読]
【57908】Re:テキストボックスに条件分岐で検索値を... neptune 08/9/19(金) 21:52 発言[未読]
【57915】Re:テキストボックスに条件分岐で検索値を... takeshi 08/9/20(土) 12:58 お礼[未読]
【57889】Re:テキストボックスに条件分岐で検索値を... kanabun 08/9/19(金) 10:18 発言[未読]
【57912】Re:テキストボックスに条件分岐で検索値を... kanabun 08/9/20(土) 0:50 発言[未読]
【57918】Re:テキストボックスに条件分岐で検索値を... takeshi 08/9/21(日) 1:34 お礼[未読]
【57923】Re:テキストボックスに条件分岐で検索値を... kanabun 08/9/21(日) 9:34 発言[未読]
【57927】Re:テキストボックスに条件分岐で検索値を... takeshi 08/9/21(日) 19:31 お礼[未読]
【57914】Re:テキストボックスに条件分岐で検索値を... takeshi 08/9/20(土) 12:49 回答[未読]

【57882】テキストボックスに条件分岐で検索値を表...
質問  takeshi  - 08/9/18(木) 22:33 -

引用なし
パスワード
   VBA初心者のtakeshiです。

受注入力というユーザーフォームを作りました。
そのフォームには
1.得意先名(コンボボックスで得意先登録というシートよりリスト表示)

2.製品名(コンボボックスで製品登録というシートよりリスト表示、1.の得意先名を選択すると、その得意先の製品のみ表示されるようになっている)

3.受注数(コンボボックスで製品登録というシートよりリスト表示、1.,2.のリストから選択することにより、その製品のメーカー指定のロット数がリスト表示される)

4.在庫数(手入力)
5.社内発注数(受注数-在庫数)

6.単価(テキストボックス。3.の受注数に対してそれぞれの単価が製品登録のシートに登録されている)

という項目があります。

教えていただきたいのは、単価の箇所に3.の受注数を選択すると自動的に単価表示ができるようにしたいのです。

Private Sub ComboBox1_Change()
  Dim k As Integer
  Dim cnt As Integer
  cnt = Worksheets("製品登録").Range("A5").CurrentRegion.Rows.Count
  受注入力.ComboBox2.Clear
     For k = 5 To cnt
       If Worksheets("製品登録").Cells(k, 1).Value = 受注入力.ComboBox1.Value Then
         受注入力.ComboBox2.AddItem Worksheets("製品登録").Cells(k, 3).Value
       End If
     Next
End Sub

Private Sub ComboBox2_Change()
  Dim i As Integer
  Dim cnt As Integer
  cnt = Worksheets("製品登録").Range("A5").CurrentRegion.Rows.Count
  受注入力.ComboBox3.Clear
     For i = 5 To cnt
      If Application.WorksheetFunction.IsNumber(ComboBox2.Value) = True Or Application.WorksheetFunction.IsText(ComboBox2.Value) = True Then
        If Worksheets("製品登録").Cells(i, 3).Value = 受注入力.ComboBox2.Value Then
          受注入力.ComboBox3.AddItem Worksheets("製品登録").Cells(i, 87).Value
          受注入力.ComboBox3.AddItem Worksheets("製品登録").Cells(i, 89).Value
          受注入力.ComboBox3.AddItem Worksheets("製品登録").Cells(i, 91).Value
        End If
      End If
     Next
End Sub

上記のコードは2.製品名3.受注数のコードで何とか正常に動いてくれます。
問題は、単価です。いろいろと試してみたんですが、いろいろなエラーが出て、もう訳が分からなくなっています。

一応、Worksheet関数のVlookupがいいのかなぁなんて思いながら、やってみても
どうもおかしいみたいで、いったいどういう表記の仕方をすればいいのか、条件分岐の仕方がわかりません。

一応、やりかけでパニックになったところのプログラムを下にはっ付けておきますんで、どなたかどうしたらいいか教えてください、お願いします。

初心者ですんで、変なプログラムで恥ずかしい限りです・・・。

Private Sub CommandButton4_Click()
  Dim i As Integer, j As Integer
  i = Worksheets("製品登録").Range("A5").CurrentRegion.Rows.Count
  j = Worksheets("製品登録").Range("A5").CurrentRegion.Columns.Count
  If 受注入力.ComboBox3.Value = Application.WorksheetFunction.VLookup(受注入力.ComboBox2.Value, Worksheets("製品登録").Range("C5:CN23"), 85, False) Then
    受注入力.TextBox5.Value = Application.WorksheetFunction.VLookup(受注入力.ComboBox2.Value, Worksheets("製品登録").Range("C5:CN23"), 86, False)
  Else: TextBox5.Value = "*"
  End If
End Sub

【57883】Re:テキストボックスに条件分岐で検索値...
発言  neptune  - 08/9/18(木) 22:46 -

引用なし
パスワード
   ▼takeshi さん:
こんにちは

すみません、長いのでよく読んでません。m(_ _)m

>教えていただきたいのは、単価の箇所に3.の受注数を選択すると自動的に単価表示ができるようにしたいのです。
よ〜わからんのですが、
>3.の受注数に対してそれぞれの単価が製品登録のシートに登録されている)
は製品名、得意先、受注数によって変化するもんですよね?
と、言う事は、製品名、得意先、受注数によって、一意の値があるんですよね。

とすると、シートの製品名、得意先、受注数を検索すればよいだけと思うんですが?

if 製品名=hoge then
 if 得意先=hogehoge then
  if 受注数=hogehoge3 then
    単価=?
  end if
 end if
end if
ってな感じで、セルの値を1つ1つ舐めていく力技ってのも有りかと思いますよ。
まぁfindとかmatchとか使う方が速いですが。そちらは力技が出来てからでも
遅くは無いです。

【57884】Re:テキストボックスに条件分岐で検索値...
発言  takeshi  - 08/9/18(木) 23:17 -

引用なし
パスワード
   ▼neptune さん:
早速のご返答ありがとうございます。

得意先が約50社、製品は何千とあるし、常に得意先も製品も追加されていきます。
そうなもんで、力技はちょっと・・・。

【57889】Re:テキストボックスに条件分岐で検索値...
発言  kanabun  - 08/9/19(金) 10:18 -

引用なし
パスワード
   おじゃまします。

> 問題は、単価です。

> 6.単価(テキストボックス。3.の受注数に対して
> それぞれの単価が製品登録のシートに登録されている)

ロット数は 87,89,91列目にデータがあるわけですよね?

A列   C列     CI列    CM列    CK列  
得意先  製品    ロット1  ロット2  ロット3

問題の「単価」は何列目に記入されているのですか?

方針として、
ComboBox2 に製品名を AddItemするとき、同じ行の87,89,91列目
もリストに (Column2,3,4に)転記しておけば、
さらに、単価も 5,6,7列目に 転記しておけば、
話が早いと思います。
(全部読んだわけじゃありませんが、どういうエラーが出て
苦悩されておられるのですか?)

【57904】Re:テキストボックスに条件分岐で検索値...
回答  neptune  - 08/9/19(金) 17:23 -

引用なし
パスワード
   ▼takeshi さん:
こんにちは

>得意先が約50社、製品は何千とあるし、常に得意先も製品も追加されていきます。
>そうなもんで、力技はちょっと・・・。
それでは、サンプル、提案を何個か。
いずれも未検証です。

取り合えず、力技のサンプル。そんなには遅くはないかも?
これが検索の基本かな?
Sub t()
Dim rngdb As Range
Const shName As String = "製品登録"
Dim sName As String   '製品名
Dim Accept As String  '受注
Dim Customer As String '得意先
Dim i As Long

'  sName、accept、customerのそれぞれにデータを代入しておく
  'それぞれの関係が判らないので自分で書いてください。
'  sName = ?
'  Accept = ?
'  Customer = ?

  Set rngdb = Worksheets(shName).Range("A5").CurrentRegion
  For i = 1 To rngdb.Rows.Count
    If rngdb(i, 1).Value = Name Then        '製品名の比較検証
      If rngdb(i + 1, 1).Value = Customer Then  '顧客
        If rngdb(i, 3).Value = Name Then    '製品名
          '全て一致したのでしたい処理
        End If
      End If
    End If
  Set rngdb = Nothing
End Sub

サンプル2.findを使ってみた。findは使った事ないのでHelpで煮詰めてね。
これはそこそこ速いと思います。
Sub t2()
Dim rngdb As Range, rngFind As Range
Const shName As String = "製品登録"
Dim sName As String   '製品名
Dim Accept As String  '受注
Dim Customer As String '得意先

'  sName、accept、customerのそれぞれにデータを代入しておく
  'それぞれの関係が判らないので自分で書いてください。
'  sName = ?
'  Accept = ?
'  Customer = ?
  Set rngdb = Worksheets(shName).Range("A5").CurrentRegion
  Set rngFind = rngdb.Find(sName)
  Do Until rngFind Is Nothing
    If rngFind.Offset(, 1).Value = Accept Then
      If rngFind.Offset(, 2).Value = Customer Then
        '見つかった
        Exit Do
      End If
    End If
    Set rngFind = rngdb.FindNext(rngFind)
  Loop

End Sub

その3
データが数万件あり、検索条件が複数なら、フィルタオプションをお勧めします。
これも結構速いです。条件によってはその5と変わらない検索速度を持ちます。

その4
findよりmatchワークシート関数が速いといわれています。自分では試した事
はありません。

その5
データをcsvファイルなど外部ファイルとしておき、ADOで取得する。
バラツキなしに速いです。但し、経験有りの場合に話。

研究してみて自分にあったのを考えて下さい。

【57906】Re:テキストボックスに条件分岐で検索値...
発言  kanabun  - 08/9/19(金) 19:43 -

引用なし
パスワード
   ▼neptune さん:こんにちは。

>これが検索の基本かな?

これは、複数のComboBoxによる絞り込み検索の問題かと思います。

1.ComboBox1には「得意先」リストがある。
2.ComboBox1リストから 一つの「得意先」が選択されたら、
 該当する製品をComboBox2 にリストアップする。
3.ComboBox2リストから 一つの「製品」が選択されたら、
 対応する行のロット数(1,2,3種類、不連続3列にある)を
 ComboBox3 にリストアップする。
4.ComboBox3 からどれかのロット数が選択されたら、
 対応する単価をTextBoxに表示する。

という順次抽出の流れかと思います。


> findよりmatch
Match は検索対象が一つのときですよね?

【57908】Re:テキストボックスに条件分岐で検索値...
発言  neptune  - 08/9/19(金) 21:52 -

引用なし
パスワード
   ▼kanabun さん:
こんにちは

突っ込みありがとうございます。^ ^;

>
>これは、複数のComboBoxによる絞り込み検索の問題かと思います。
省略
>という順次抽出の流れかと思います。
はい、確かに、最初にアップされているソースを見る限りそうですね。
正直ソースは余り見てませんでした。

で、私見ですが、1つのコンボボックスに数百〜数千
(実数は不明だが目で見られる範囲ではないと思う)のデータを入力するのは
ユーザーインターフェイスとして無意味だし、無駄な処理です。
全部見られませんから。まぁ、検索用にするなら別ですが。
出来る限り、Key入力なしで選択できる範囲のデータ数にするのがベターですね。

>これが検索の基本かな?
まぁちょっと言い過ぎの感はありますが、
これはこれであながち間違いでもないでしょう。

幸いにもご指摘くださったので、質問者さんもご自分に
最適な方法を検討なさるでしょう。

>Match は検索対象が一つのときですよね?
その通りです。matchって検索範囲を指定しなおす事で対応できませんか?

【57912】Re:テキストボックスに条件分岐で検索値...
発言  kanabun  - 08/9/20(土) 0:50 -

引用なし
パスワード
   >> 問題は、単価です。
>
>> 6.単価(テキストボックス。3.の受注数に対して
>> それぞれの単価が製品登録のシートに登録されている)

>> 教えていただきたいのは、単価の箇所に3.の受注数を選択すると
>> 自動的に単価表示ができるようにしたいのです。

>ComboBox2 に製品名を AddItemするとき、同じ行の87,89,91列目
>もリストに (Column2,3,4に)転記しておけば、
>さらに、単価も 5,6,7列目に 転記しておけば、
>話が早いと思います。

この案によるサンプルコードです。

>ロット数は 87,89,91列目にデータがあるわけですよね?
>
>A列   C列     CI列    CM列    CK列  
>得意先  製品    ロット1  ロット2  ロット3
>

>問題の「単価」は何列目に記入されているのですか?

この質問に対するご回答がいただけてないので、
88,90,92列に 左隣のロット数に対応する「単価」が書き込まれてるものと
仮定しました。

'【Dictionary】にA列の受注先名を格納 案
'【ComboBox2】に 製品名 ロット数1,2,3 単価 1,2,3 をリスト

'--------------------------------
Option Explicit
Private dic As Object

Private Sub UserForm_Initialize()
  Dim i As Long, ss As String
  
  '(1)ComboBox1に得意先をリスト
  ' 省略
  ComboBox1.List =           '得意先リスト
  
  '(2)ComboBox2用 得意先別 製品行を Dictionaryに登録
  Set dic = CreateObject("Scripting.Dictionary")
  With Worksheets("製品登録")
    For i = 5 To .Range("A65536").End(xlUp).Row
      ss = .Cells(i, 1).Value
      dic(ss) = dic(ss) & "," & i '得意先別 行番号
    Next
  End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If Not dic Is Nothing Then Set dic = Nothing
End Sub

'▼リストから得意先が選択されたら 対応する製品名をComboBox2にセット
Private Sub ComboBox1_Change()
  Dim i As Long, j As Long, k As Long
  Dim str得意先 As String
  Dim v
  
  str得意先 = ComboBox1.Value
  v = Split(dic(str得意先), ",") '選択得意先 の製品別行番号の _
                 配列をDictionaryのItemから取得
  ComboBox2.Clear
  With Worksheets("製品登録")
    For k = 1 To UBound(v)
      i = v(k)               '行番号
      ComboBox2.AddItem .Cells(i, 3).Value 'C列 製品名
      For j = 1 To 6  '受注数(87,89,91)または単価(88,90,92)
        ComboBox2.List(k - 1, j) = .Cells(i, 86 + j).Value
      Next
    Next
  End With
End Sub

'▼ ComboBox2リストから製品名が選択されたら ロット(受注)数と単価を _
  ComboBox3 にセット
Private Sub ComboBox2_Change()
 Dim i As Long, j As Long, Index As Long, lot As Long
 
 Index = ComboBox2.ListIndex
 With ComboBox3
   .Clear
   i = -1
   For j = 1 To 6 Step 2
     lot = Val(ComboBox2.List(Index, j))
     If lot > 0 Then
       i = i + 1
       .AddItem lot 'ロット(受注)数
       .List(i, 1) = ComboBox2.List(Index, j + 1)
     End If
   Next
   .ListIndex = i
 End With
End Sub

'▼ロットが一つ選択されたら、対応する単価をTextBoxに表示
Private Sub ComboBox3_Change()
 With ComboBox3
   If .ListIndex > -1 Then
     txt単価.Text = .List(.ListIndex, 1)
   End If
 End With
End Sub

【57914】Re:テキストボックスに条件分岐で検索値...
回答  takeshi  - 08/9/20(土) 12:49 -

引用なし
パスワード
   ▼kanabun さん:
>
>ロット数は 87,89,91列目にデータがあるわけですよね?
>
>A列   C列     CI列    CM列    CK列  
>得意先  製品    ロット1  ロット2  ロット3
>
>問題の「単価」は何列目に記入されているのですか?

takeshiです。返事が遅くなってすみません。昨日急な用事で、パソコン見れませんでした。

申し訳ないです。
単価は各ロットごとの次の列に記入しています。
CI列ならCJ列、CM列ならCN列という具合に。

>(全部読んだわけじゃありませんが、どういうエラーが出て
>苦悩されておられるのですか?)

私の場合は、コンパイルエラーが殆どです。自己流でやってますので、文法がなってないようです。
色々やってるうちに、いろんなエラーがでて、一体どうしたらええねん!となったわけです。

すいませんが、よろしくです。

【57915】Re:テキストボックスに条件分岐で検索値...
お礼  takeshi  - 08/9/20(土) 12:58 -

引用なし
パスワード
   ▼neptune さん:
▼kanabun さん:

質問者のtakeshiです。

たくさんのアドバイス有難うございます。意見を参考にしながら、やってみようと思います。

結果報告はちゃんとしますので、また宜しくお願いします。

どうも、有難うございました。

【57918】Re:テキストボックスに条件分岐で検索値...
お礼  takeshi  - 08/9/21(日) 1:34 -

引用なし
パスワード
   ▼kanabun さん:

takeshiです。kanabunさんの記述の通りに入力してみました。

すると、自分の思ったとおりに動きました!か、感動です!!

10日くらい考えて、どうにもならずにあきらめかけてたので、感慨ひとしおです。

ほんとに、ありがとうございます!

でも、一か所だけうまくいかない箇所があります。

順々にリストから選択していって最後に単価が自動的に出るまでいったんですが、もう一回、得意先名を変更しようとすると、エラーが出ます。(実際、仕事で使用する際には得意先の箇所は一度入れると変更することはほぼないんですが・・・。)

「実行時エラー'381' Listプロパティの値を取得できません。プロパティの配列のインデックスが無効です。」と出ます。

デバッグしてみると、

Private Sub ComboBox2_Change()
 Dim i As Long, j As Long, Index As Long, lot As Long

 Index = ComboBox2.ListIndex
 With ComboBox3
   .Clear
   i = -1
   For j = 1 To 6 Step 2
→→→   lot = Val(ComboBox2.list(Index, j))
     If lot > 0 Then
       i = i + 1
       .AddItem lot 'ロット(受注)数
       .list(i, 1) = ComboBox2.list(Index, j + 1)
     End If
   Next
   .ListIndex = i
 End With
End Sub

→の段が黄色反転しています。

どうしたらいいのか、また迷っています。すみませんが、教えてもらえないでしょうか?

【57923】Re:テキストボックスに条件分岐で検索値...
発言  kanabun  - 08/9/21(日) 9:34 -

引用なし
パスワード
   ▼takeshi さん:こんにちは。

>すると、自分の思ったとおりに動きました!か、感動です!!
>
>10日くらい考えて、どうにもならずにあきらめかけてたので、感慨ひとしおです。

検証をどうも。


> もう一回、得意先名を変更しようとすると、エラーが出ます。

>「実行時エラー'381' Listプロパティの値を取得できません。プロパティの配列のインデックスが無効です。」と出ます。

>デバッグしてみると、
>
>Private Sub ComboBox2_Change()
> Dim i As Long, j As Long, Index As Long, lot As Long
>
> Index = ComboBox2.ListIndex
> With ComboBox3
>   .Clear
>   i = -1
>   For j = 1 To 6 Step 2
> →→→   lot = Val(ComboBox2.list(Index, j))
>     If lot > 0 Then
>       i = i + 1
>       .AddItem lot 'ロット(受注)数
>       .list(i, 1) = ComboBox2.list(Index, j + 1)
>     End If
>   Next
>   .ListIndex = i
> End With
>End Sub
>
>→の段が黄色反転しています。
>
>どうしたらいいのか、また迷っています。

再現しました。
え〜これはですね〜、(って、しばし時間稼ぎする ^^)//...^^)
> 得意先名を変更しようと
ですから、ComboBox1をクリックしたときの動作が関連しているわけですね。

> Private Sub ComboBox1_Change()
では、
  一度、リストをClear する、という処理をしています。↓
>  ComboBox2.Clear

これが起きると、ComboBox2_Changeイベントが発生するのです。
そして そこの先頭に
>  Index = ComboBox2.ListIndex
って書いてありますが、
ComboBox2.Clearしたときの ListIndex は -1 (何も選択されていない状態)
なので、変数Indexには -1 が代入され、
何のチェックもないので、そのまま
ComboBox2_Changeイベント 内の処理が実行され、

   For j = 1 To 6 Step 2
→→→   lot = Val(ComboBox2.list(Index, j))
                   ▲ここで、Index の値が -1 なので、
> 「実行時エラー'381' Listプロパティの値を取得できません。
> プロパティの配列のインデックスが無効です。」

となるわけです。

理由が分かったら、対処しましょう。
このばあい、ComboBox1_Change で ComboBox2.Clear していることが遠因ですが、
ComboBox2_Change()イベントは ユーザーがリストを選択したとき以外にも
起こるので、ListIndexの値が -1 だったら、後続処理をしないように
条件分岐すれば、問題解決です。


'' ComboBox2リストから製品名が選択されたら ロット(受注)数1,2,3
Private Sub ComboBox2_Change()
 Dim i As Long, j As Long, Index As Long, lot As Long
 
 Index = ComboBox2.ListIndex
 With ComboBox3
   .Clear
   If Index < 0 Then Exit Sub '★ この一行を挿入してください
   i = -1
   For j = 1 To 6 Step 2
     lot = Val(ComboBox2.List(Index, j))
     If lot > 0 Then
       i = i + 1
       .AddItem lot 'ロット(受注)数
       .List(i, 1) = ComboBox2.List(Index, j + 1)
     End If
   Next
   .ListIndex = i
 End With
End Sub

【57927】Re:テキストボックスに条件分岐で検索値...
お礼  takeshi  - 08/9/21(日) 19:31 -

引用なし
パスワード
   ▼kanabun さん:こんばんは
 
takeshiです。
完璧です。本当に重ね重ねありがとうございます。

丁寧な説明まで、どう感謝していいやら・・。

まだ、わからんまま、kanabunさんのプログラムの記述をうつしている状態なんで、これからこのプログラムの記述の仕方をゆっくりと理解していこうと思います。

どうもありがとうございました!

4198 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free