Excel VBA質問箱 IV

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

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


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

【28222】match関数について KIKAKU 05/8/31(水) 15:44 質問[未読]
【28226】Re:match関数について ichinose 05/8/31(水) 16:42 発言[未読]
【28227】Re:match関数について KIKAKU 05/8/31(水) 17:33 お礼[未読]
【28233】Re:match関数について Hirofumi 05/8/31(水) 21:12 回答[未読]
【28238】Re:match関数について KIKAKU 05/9/1(木) 0:03 質問[未読]
【28242】Re:match関数について ichinose 05/9/1(木) 7:20 発言[未読]
【28273】Re:match関数について Hirofumi 05/9/1(木) 20:46 回答[未読]
【28275】Re:match関数について Hirofumi 05/9/1(木) 21:46 発言[未読]
【28305】Re:match関数について KIKAKU 05/9/2(金) 12:53 質問[未読]
【28311】Re:match関数について ichinose 05/9/2(金) 13:21 発言[未読]
【28340】Re:match関数について KIKAKU 05/9/2(金) 18:50 質問[未読]
【28354】Re:match関数について ichinose 05/9/3(土) 8:53 発言[未読]
【28365】Re:match関数について KIKAKU 05/9/3(土) 22:06 お礼[未読]
【28349】Re:match関数について Hirofumi 05/9/2(金) 22:21 回答[未読]

【28222】match関数について
質問  KIKAKU  - 05/8/31(水) 15:44 -

引用なし
パスワード
   関数Matchについて質問させていただきます。
 A   B   C   D   E   F
100  200  300  400  500  600 ・・・・・
上のような配列が在り、例えば101〜199の検索値の場合はBと200がヒットし、
201〜299の場合はCと300が、つまり、近時値ではなく各数値間の上をヒット
してくるような関数はMatch関数を使用できるのでしょうか?

【28226】Re:match関数について
発言  ichinose  - 05/8/31(水) 16:42 -

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


>関数Matchについて質問させていただきます。
> A   B   C   D   E   F
>100  200  300  400  500  600 ・・・・・
この値がセルA1〜F1にあったとします。
検索値は A2
数式を入れるセルをB2とすると、
セルB2には
「=OFFSET(A1,0,IF(ISERROR(MATCH(A2,A1:F1,1)),0,
IF(ISERROR(MATCH(A2,A1:F1,0)),MATCH(A2,A1:F1,1),MATCH(A2,A1:F1,1)-1)),1,1)」

という数式を入れてみて下さい。

他には・・・、

「=OFFSET(A1,0,COUNT(A1:F1)-MATCH(A2,LARGE(A1:F1,COLUMN(A1:F1)),-1),1,1)」

但し、配列を使用していますから、数式の確定はEnterキーではなく
Ctrl+Shift+Enterキーで確定して下さい。

尚、A2が600を超える値だった場合のエラー処理はしていないので
これは考えてみてください。

A2>Max(A1:F1)を使う かな?

【28227】Re:match関数について
お礼  KIKAKU  - 05/8/31(水) 17:33 -

引用なし
パスワード
   ▼ichinose さん:
有難うございました。うまく動作しました。

ちなみに、
>この値がセルA1〜F1にあったとします。
この部分を検索範囲とし

>検索値は A2
この部分をTextbox1

>数式を入れるセルをB2とすると、
この部分をTextbox2
とした場合にマクロとして記述することはできるのでしょうか?

【28233】Re:match関数について
回答  Hirofumi  - 05/8/31(水) 21:12 -

引用なし
パスワード
   >ちなみに、
>>この値がセルA1〜F1にあったとします。
>この部分を検索範囲とし
>
>>検索値は A2
>この部分をTextbox1
>
>>数式を入れるセルをB2とすると、
>この部分をTextbox2
>とした場合にマクロとして記述することはできるのでしょうか?

此れがUserFormのTextBoxでと言う事なら以下の様に成ります

以下をUserFormのコードモジュールに記述して下さい

Option Explicit

Private rngScope As Range

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

  Dim lngFound As Long
  Dim lngOver As Long
  
  TextBox2.Text = ""
  
  With TextBox1
    If .Text <> "" Then
      '探索範囲から、値を探索
      lngFound = ColumnSearh(CLng(.Text), rngScope, lngOver)
      '探索が失敗した場合(探索値其の物が無い場合)
      If lngFound = 0 Then
        '探索値を超える最小値のある列が範囲内の場合
        If lngOver <= rngScope.Columns.Count Then
          TextBox2.Text = rngScope(, lngOver).Value
        '範囲から外れる場合
        Else
          Beep
          MsgBox "参照値の範囲を超えています"
          Cancel = True
        End If
      '探索が成功した場合
      Else
        TextBox2.Text = rngScope(, lngFound).Value
      End If
    End If
  End With
  
End Sub

Private Sub UserForm_Initialize()

  Dim lngColumns As Long
  
  '探索範囲の先頭セル位置を指定
  With ActiveSheet.Cells(1, "A")
    '列数を取得
    lngColumns = .Offset(, 256 - .Column).End(xlToLeft).Column - .Column + 1
    If lngColumns <= 1 And .Value = "" Then
      MsgBox "参照データが有りません"
      Exit Sub
    End If
    '探索範囲を変数に代入
    Set rngScope = .Resize(, lngColumns)
  End With
  
End Sub

Private Sub UserForm_Terminate()

  Set rngScope = Nothing
  
End Sub

Private Function ColumnSearh(vntKey As Variant, _
            rngScope As Range, _
            Optional lngOver As Long) As Long

  Dim vntFound As Variant
  
  'Matchによる二分探索
  vntFound = Application.Match(vntKey, rngScope, 1)
  'もし、エラーで無いなら
  If Not IsError(vntFound) Then
    'もし、Key値と探索位置の値が等しいなら
    If vntKey = rngScope(, vntFound).Value Then
      '戻り値として、列位置を代入
      ColumnSearh = vntFound
    End If
    'Key値を超える最小値のある列
    lngOver = vntFound + 1
  Else
    lngOver = 1
  End If
  
End Function

【28238】Re:match関数について
質問  KIKAKU  - 05/9/1(木) 0:03 -

引用なし
パスワード
   ▼Hirofumi さん:
ご丁寧に有難うございます。正常に動作いたしました。
もし、下記のように探索範囲が複数行の表となった場合で
   A  B  C  D  E・・・
1  100  120  140  160  180
2  130  160  190  210  240
3  150  190  230  270  310




その行がtextbox等で1行目とか3行目とか指定された場合に、その指定された配列から同じように探索するには、”探索範囲の先頭セル”下記の部分を変更すればいいのでしょうか?
Cellsのセルが変化するということですよね?
また、表から探索するとなると範囲も定義づけが必要となるのでしょうか?

>  '探索範囲の先頭セル位置を指定
>  With ActiveSheet.Cells(1, "A")
>    '列数を取得
>    lngColumns = .Offset(, 256 - .Column).End(xlToLeft).Column - .Column + 1

【28242】Re:match関数について
発言  ichinose  - 05/9/1(木) 7:20 -

引用なし
パスワード
   おはようございます。
Hirofumiさんとはだいぶ方法が違いますが、
私は前回の数式をVBAコード内でも生かしました。


>ご丁寧に有難うございます。正常に動作いたしました。
>もし、下記のように探索範囲が複数行の表となった場合で
>   A  B  C  D  E・・・
>1  100  120  140  160  180
>2  130  160  190  210  240
>3  150  190  230  270  310
>・
>・
>・
このシートをアクティブな状態にして下さい。


>
>その行がtextbox等で1行目とか3行目とか指定された場合に、その指定された配列から同じように探索するには、”探索範囲の先頭セル”下記の部分を変更すればいいのでしょうか?

ユーザーフォーム(Userform1)には

  Textbox1----検索行の指定用--1と指定すると1行目を
         検索対象とします

  Textbox2----検索値指定用

  Textbox3----検索結果表示用

とすると、

標準モジュールに
'=================================================
Sub main()
  UserForm1.Show
End Sub


userform1のモジュールに
'================================================
Dim 検索セル範囲 As String
Dim 検索開始セル As String
'=======================================================================
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  Dim crow As Long
   If IsNumeric(TextBox1.Text) Then
     crow = Val(TextBox1.Text)
     If crow > 0 Then
      With ActiveSheet
        検索セル範囲 = .Range(.Cells(crow, 1), .Cells(crow, .Columns.Count).End(xlToLeft)).Address
        検索開始セル = .Cells(crow, 1).Address
        End With
     Else
      MsgBox "検索行指定エラー"
      Cancel = True
      
      End If
   Else
     MsgBox "検索行指定エラー"
     Cancel = True
     End If
End Sub
'====================================================================
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  Dim 検索値 As String
  With ActiveSheet
    If Val(TextBox2.Value) > Application.Max(.Range(検索セル範囲)) Then
     MsgBox "検索値指定エラー"
     Cancel = True
    Else
     検索値 = TextBox2.Text
     TextBox3.Text = .Evaluate("=OFFSET(" & 検索開始セル & ",0,IF(ISERROR(MATCH(" & _
                  検索値 & "," & 検索セル範囲 & ",1)),0," & _
                  "IF(ISERROR(MATCH(" & 検索値 & "," & 検索セル範囲 & ",0))," & _
                  "MATCH(" & 検索値 & "," & 検索セル範囲 & ",1)," & _
                  "MATCH(" & 検索値 & "," & 検索セル範囲 & ",1)-1)),1,1)")
     End If
    End With

End Sub


対象シートをアクティブにした状態で
mainを実行してみて下さい。

【28273】Re:match関数について
回答  Hirofumi  - 05/9/1(木) 20:46 -

引用なし
パスワード
   >その行がtextbox等で1行目とか3行目とか指定された場合に、
>その指定された配列から同じように探索するには、
>”探索範囲の先頭セル”下記の部分を変更すればいいのでしょうか?
>Cellsのセルが変化するということですよね?
>また、表から探索するとなると範囲も定義づけが必要となるのでしょうか?
>
>>  '探索範囲の先頭セル位置を指定
>>  With ActiveSheet.Cells(1, "A")
>>    '列数を取得
>>    lngColumns = .Offset(, 256 - .Column).End(xlToLeft).Column - .Column + 1

基本的には、其の通りだと思います
ただ、「表から探索するとなると範囲も定義づけが必要となるのでしょうか?」の意味が解りません
表の最終列は、コードで取得しているので特に何かする必要は無いと思います

今回、数表の行を選択すると言う事なので、前回のコードを少し変更して見ました
変更点は、数表の行を選択する為、ComboBox1を追加しています
ComboBox1には、数表の先頭初期値を表示します

また、変更、追加しているプロシージャは、「Sub UserForm_Initialize()」、
「Sub UserForm_Terminate」、「Sub ComboBox1_Change()」
変数は、「Private rngListTop As Range」が追加されています
「Sub TextBox1_BeforeUpdate」と「Function ColumnSearh」の変更は、有りません

尚、前回は、ActiveSheetに数表が有る様に書きましたが、
今回は、Sheet1に数表が有る様に直していますので気を就けて下さい
また、数表の各行の列数は、違っていても構いません(最終列はコードで取得)

Option Explicit

Private rngListTop As Range
Private rngScope As Range

Private Sub ComboBox1_Change()

  Dim lngRow As Long
  Dim lngColumns As Long
  
  With ComboBox1
    'ComboBoxで有効な行が選択された場合
    If .ListIndex > -1 Then
      'ListIndexの値を保存
      'ListIndexの値と数表の行Offset値は等しい為
      lngRow = .ListIndex
    Else
      Exit Sub
    End If
  End With
    
  With rngListTop
    '列数を取得
    lngColumns = .Offset(lngRow, 256 - .Column).End(xlToLeft).Column - .Column + 1
    'この部分(以下4行)は、数表が有れば必要ないかも?
    If lngColumns <= 1 And .Value = "" Then
      MsgBox "参照データが有りません"
      Exit Sub
    End If
    '探索範囲を変数に代入
    Set rngScope = .Offset(lngRow).Resize(, lngColumns)
  End With
  
  TextBox1.Text = ""
  TextBox2.Text = ""
  
End Sub

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

  Dim lngFound As Long
  Dim lngOver As Long
  
  TextBox2.Text = ""
  
  With TextBox1
    If .Text <> "" Then
      '探索範囲から、値を探索
      lngFound = ColumnSearh(CLng(.Text), rngScope, lngOver)
      '探索が失敗した場合(探索値其の物が無い場合)
      If lngFound = 0 Then
        '探索値を超える最小値のある列が範囲内の場合
        If lngOver <= rngScope.Columns.Count Then
          TextBox2.Text = rngScope(, lngOver).Value
        '範囲から外れる場合
        Else
          Beep
          MsgBox "参照値の範囲を超えています"
          Cancel = True
        End If
      '探索が成功した場合
      Else
        TextBox2.Text = rngScope(, lngFound).Value
      End If
    End If
  End With
  
End Sub

Private Sub UserForm_Initialize()

  Dim lngRows As Long
  Dim vntData As Variant
  
  '探索範囲の先頭セル位置を指定
  Set rngListTop = Worksheets("Sheet1").Cells(1, "A")
  'ComboBoxのListを取得
  With rngListTop
    lngRows = .Offset(65536 - .Row).End(xlUp).Row - .Row + 1
    If lngRows <= 1 And .Value = "" Then
      MsgBox "参照データが有りません"
      Exit Sub
    End If
    vntData = .Resize(lngRows).Value
  End With
  
  'ComboBoxを設定
  With ComboBox1
    .List = vntData
    .Style = fmStyleDropDownList
    .ListIndex = 0
  End With
  
End Sub

Private Sub UserForm_Terminate()

  Set rngListTop = Nothing
  Set rngScope = Nothing
  
End Sub

Private Function ColumnSearh(vntKey As Variant, _
            rngScope As Range, _
            Optional lngOver As Long) As Long

  Dim vntFound As Variant
  
  'Matchによる二分探索
  vntFound = Application.Match(vntKey, rngScope, 1)
  'もし、エラーで無いなら
  If Not IsError(vntFound) Then
    'もし、Key値と探索位置の値が等しいなら
    If vntKey = rngScope(, vntFound).Value Then
      '戻り値として、列位置を代入
      ColumnSearh = vntFound
    End If
    'Key値を超える最小値のある列
    lngOver = vntFound + 1
  Else
    lngOver = 1
  End If
  
End Function

【28275】Re:match関数について
発言  Hirofumi  - 05/9/1(木) 21:46 -

引用なし
パスワード
   言葉が足らなかったので、付け足します

>その行がtextbox等で1行目とか3行目とか指定された場合に、
>その指定された配列から同じように探索するには、
>”探索範囲の先頭セル”下記の部分を変更すればいいのでしょうか?
>Cellsのセルが変化するということですよね?
>また、表から探索するとなると範囲も定義づけが必要となるのでしょうか?
>
>>  '探索範囲の先頭セル位置を指定
>>  With ActiveSheet.Cells(1, "A")
>>    '列数を取得
>>    lngColumns = .Offset(, 256 - .Column).End(xlToLeft).Column - .Column + 1

基本的には、其の通りだと思います

と書きましたが、私が直すとすれば、今回書いたコードの
「Private Sub ComboBox1_Change()」の中で書いている様にOffsetで指定します

  With rngListTop
    '列数を取得
    lngColumns = .Offset(lngRow, 256 - .Column).End(xlToLeft).Column - .Column + 1
    'この部分(以下4行)は、数表が有れば必要ないかも?
    If lngColumns <= 1 And .Value = "" Then
      MsgBox "参照データが有りません"
      Exit Sub
    End If
    '探索範囲を変数に代入
    Set rngScope = .Offset(lngRow).Resize(, lngColumns)
  End With

理由は、本来、以下の部分は基準点の意味を持たせて有ります
(前回は、1行なので同じなのですが?)

  '探索範囲の先頭セル位置を指定
  Set rngListTop = Worksheets("Sheet1").Cells(1, "A")

其の意味は、今回の「A1:E3」の範囲が、
もし何かの理由で「B2:F4」に成った場合、行列を直接指定していると
コードの他の部分まで影響する可能性が有りますが、
今回の様にOffsetで指定すれば、以下の様にすれば事足ります

  '探索範囲の先頭セル位置を指定
  Set rngListTop = Worksheets("Sheet1").Cells(2, "B")

其の点を注意して下さい

【28305】Re:match関数について
質問  KIKAKU  - 05/9/2(金) 12:53 -

引用なし
パスワード
   Hirofumi さん,Ichinoseさんご丁寧に解説くださいまして本当に有難うございました。
大変参考になり、勉強になりました。
お忙しいとは思いますが、最後にもう1つだけ質問させて下さい。

今まで取得した値プラス、A、C、・・・というような”列番号”を取得することは可能ですか?

例えば、行2で180ならば値190はもとめられましたが、更に”列C”という値も同時に。

  A  B  C  D  E・・・
>1  100  120  140  160  180
>2  130  160  190  210  240
>3  150  190  230  270  310
>・

【28311】Re:match関数について
発言  ichinose  - 05/9/2(金) 13:21 -

引用なし
パスワード
   ▼KIKAKU さん:
こんにちは。
出かけてしまうので問題があっても投稿は夜になってしまいますが、

>ユーザーフォーム(Userform1)には

>  Textbox1----検索行の指定用--1と指定すると1行目を
>         検索対象とします

>  Textbox2----検索値指定用

>  Textbox3----検索結果表示用

  textbox4----検索の結果見つかったセルアドレス
  これを↑を追加して、
>とすると、

Userform1のモジュールの
以下のプロシジャーを差し替えて下さい。
(他は変更なしです)

'=============================================================
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  Dim 検索値 As String
  Dim col As Long
  With ActiveSheet
    If Val(TextBox2.Value) > Application.Max(.Range(検索セル範囲)) Then
     MsgBox "検索値指定エラー"
     Cancel = True
    Else
     検索値 = TextBox2.Text
     col = .Evaluate("IF(ISERROR(MATCH(" & _
               検索値 & "," & 検索セル範囲 & ",1)),0," & _
              "IF(ISERROR(MATCH(" & 検索値 & "," & 検索セル範囲 & ",0))," & _
                "MATCH(" & 検索値 & "," & 検索セル範囲 & ",1)," & _
                "MATCH(" & 検索値 & "," & 検索セル範囲 & ",1)-1))")
               
     TextBox4.Text = .Evaluate("=OFFSET(" & 検索開始セル & ",0," & col & ",1,1)").Address
     TextBox3.Text = .Evaluate("=OFFSET(" & 検索開始セル & ",0," & col & ",1,1)")
     End If
    End With

End Sub

アドレス表示にしましたが、本当に列だけ(BとかCとかのみ)の結果がほしいなら
上記の結果をちょっと加工すれば得られます。
確認してみて下さい。

【28340】Re:match関数について
質問  KIKAKU  - 05/9/2(金) 18:50 -

引用なし
パスワード
   ▼ichinose さん:
ichinose さんお疲れ様です。
おかげさまで、動作良好でございますが、もう少し突っ込んで質問させていただいてよろしいでしょうか。

検索範囲の指定がアクティブシート上ではなくて、Sheet1上でフォームを
立ち上げ、検索範囲をSheet3上にする場合はどのような変更が伴うのでしょうか?

>With ActiveSheet

の部分だけなのでしょうか?

また、A1を基点にするのではなく、Sheet3上の1つの表を検索範囲にする場合
の指定は?、つまり、別シートの下記表を検索範囲にする場合で、

sheet3上に表を作成
    あ    い    う    え    お
イ    100    120    140    160    180
ロ    110    140    160    180    200
ハ    120    160    180    200    220
二    130    180    200    220    240
ホ    140    200    220    240    260

Textbox1に”ロ”、Textbox2に”150”を入力した場合、
結果値が”160”、結果列が”う”という場合です。

宜しくお願いします。

【28349】Re:match関数について
回答  Hirofumi  - 05/9/2(金) 22:21 -

引用なし
パスワード
   色々と誤解が有るとまずいので整理します

例えば、以下の様な数表とします

sheet3上に表を作成
  A  B   C  D   E  F

2    あ   い  う   え  お
3 イ  100  120  140  160  180
4 ロ  110  140  160  180  200
5 ハ  120  160  180  200  220
6 二  130  180  200  220  240
7 ホ  140  200  220  240  260


A列3行目から下に、行見出し(イロハ・・)が有るとします
2行目、B2:F2に列見出し(あいう・・)が有るとします
数表のデータは、B3を先頭とします

次にUserFormの構成は、以下の様に成ります
ComboBox1 : 行見出しイロハ・・を表示し数表の行を指定
TextBox1 : 探索値を指定
TextBox2 : 探索結果を表示(例、ロ行、141の時、160を表示)
TextBox3 : 探索結果の有る列見だしを表示(例、ロ行、141の時、「う」)
TextBox4 : 探索結果の有る列位置を表示(例、ロ行、141の時、「D」)

UserFormのコードは以下の様に成ります

Option Explicit

Private rngListTop As Range
Private rngScope As Range

Private Sub ComboBox1_Change()

  Dim lngRow As Long
  Dim lngColumns As Long
  
  With ComboBox1
    'ComboBoxで有効な行が選択された場合
    If .ListIndex > -1 Then
      'ListIndexの値を保存
      'ListIndexの値と数表の行Offset値は等しい為
      lngRow = .ListIndex
    Else
      Exit Sub
    End If
  End With
  
  '選択された行の探索範囲を設定
  With rngListTop
    '列数を取得
    lngColumns = .Offset(lngRow, 256 - .Column).End(xlToLeft).Column - .Column + 1
    'この部分(以下4行)は、数表が有れば必要ないかも?
    If lngColumns <= 1 And .Value = "" Then
      MsgBox "参照データが有りません"
      Exit Sub
    End If
    '探索範囲を変数に代入
    Set rngScope = .Offset(lngRow).Resize(, lngColumns)
  End With
  
  TextBox1.Text = ""
  TextBox2.Text = ""
  TextBox3.Text = ""
  TextBox4.Text = ""
  
End Sub

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

  Dim lngFound As Long
  Dim lngOver As Long
  
  TextBox2.Text = ""
  
  With TextBox1
    If .Text <> "" Then
      '探索範囲から、値を探索
      lngFound = ColumnSearh(CLng(.Text), rngScope, lngOver)
      '探索が失敗した場合(探索値其の物が無い場合)
      If lngFound = 0 Then
        '探索値を超える最小値のある列が範囲内の場合
        If lngOver <= rngScope.Columns.Count Then
          '探索結果を表示(例、ロ行、141の時、160を表示)
          TextBox2.Text = rngScope(, lngOver).Value
          '探索結果の有る列見だしを表示(例、ロ行、141の時、「う」)
          TextBox3.Text = rngListTop.Offset(-1, lngOver - 1).Value
          '探索結果の有る列位置を表示(例、ロ行、141の時、「D列」)
          TextBox4.Text = GetColumnChr(rngListTop.Column + lngOver - 1) & "列"
        '範囲から外れる場合
        Else
          Beep
          MsgBox "参照値の範囲を超えています"
          Cancel = True
        End If
      '探索が成功した場合
      Else
        '探索結果を表示 (例、ロ行、141の時、160を表示)
        TextBox2.Text = rngScope(, lngFound).Value
        '探索結果の有る列見だしを表示(例、ロ行、141の時、「う」)
        TextBox3.Text = rngListTop.Offset(-1, lngFound - 1).Value
        '探索結果の有る列位置を表示(例、ロ行、141の時、「D列」)
        TextBox4.Text = GetColumnChr(rngListTop.Column + lngFound - 1) & "列"
      End If
    End If
  End With
  
End Sub

Private Sub UserForm_Initialize()

  Dim lngRows As Long
  Dim vntData As Variant
  
  '探索範囲の先頭セル位置を指定(Sheet3のB3を指定)
  Set rngListTop = Worksheets("Sheet3").Cells(3, "B")
  'ComboBoxのListを取得
  With rngListTop
    lngRows = .Offset(65536 - .Row).End(xlUp).Row - .Row + 1
    If lngRows <= 1 And .Value = "" Then
      MsgBox "参照データが有りません"
      Exit Sub
    End If
    '行見出し(イロハ・・)を取得
    vntData = .Offset(, -1).Resize(lngRows).Value
  End With
  
  'ComboBoxを設定
  With ComboBox1
    .List = vntData
'    .Style = fmStyleDropDownList
    .ListIndex = 0
  End With
  
End Sub

Private Sub UserForm_Terminate()

  Set rngListTop = Nothing
  Set rngScope = Nothing
  
End Sub

Private Function ColumnSearh(vntKey As Variant, _
            rngScope As Range, _
            Optional lngOver As Long) As Long

  Dim vntFound As Variant
  
  'Matchによる二分探索
  vntFound = Application.Match(vntKey, rngScope, 1)
  'もし、エラーで無いなら
  If Not IsError(vntFound) Then
    'もし、Key値と探索位置の値が等しいなら
    If vntKey = rngScope(, vntFound).Value Then
      '戻り値として、列位置を代入
      ColumnSearh = vntFound
    End If
    'Key値を超える最小値のある列
    lngOver = vntFound + 1
  Else
    lngOver = 1
  End If
  
End Function

Private Function GetColumnChr(lngMark As Long) As String
    
  Const clngAlphabet As Long = 26
  
  Dim lngNumb As Long
  Dim strColumn As String
  
  lngNumb = (lngMark - 1) \ clngAlphabet
  If lngNumb > 0 Then
    strColumn = Chr(64 + lngNumb)
  End If
  
  lngNumb = (lngMark - 1) Mod clngAlphabet
  
  GetColumnChr = strColumn & Chr(65 + lngNumb)
  
End Function

【28354】Re:match関数について
発言  ichinose  - 05/9/3(土) 8:53 -

引用なし
パスワード
   おはようございます。

シートSheet3に
以下の表があったとします。


   C   D    E    F    G    H
5      あ    い    う    え    お
6  イ   100   120    140   160   180
7  ロ   110   140    160   180   200
8  ハ   120   160    180   200   220
9  ニ   130   180    200   220   240


左端の数字が行番号、上端のアルファベットは列を表わします。

これで表の要素の起点をセル$D$6
列方向の要素数は 5
行方向の要素数は 4

と言うことにします。

関係をよく把握してください。


Userform1に配置するテキストボックスは、

>  Textbox1----検索行の指定用
         上記の表で「ロ」の指定で7行目を検索対象にします
でもこれは、Hirofumiさんの仕様のコンボボックスに賛成です。
変更は、考えてみて下さい

>  Textbox2----検索値指定用

>  Textbox3----検索結果表示用

  textbox4----検索の結果みつかった列見出し
        (上記の「あ」とか「い」)を表示

では、Userform1のモジュールです。
'================================
Dim 検索セル範囲 As String
Dim 検索開始セル As String
'****** ↓表の位置により以下の3つの値を変更します *******
Const strng As String = "$d$6" '表の起点セル
Const c_num As Long = 5 '表の列方向要素数
Const r_num As Long = 4 '表の行方向の要素数
'=======================================================================
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  Dim 行見出し As Range
  Dim crow As Variant
  With Worksheets("sheet3") '対象シートにする
    Set 行見出し = .Range(strng).Offset(0, -1).Resize(r_num, 1)
    crow = Application.Match(TextBox1.Value, 行見出し, 0)
    If Not IsError(crow) Then
     検索セル範囲 = .Range(strng).Offset(crow - 1, 0).Resize(, c_num).Address
     検索開始セル = .Range(strng).Offset(crow - 1, 0).Address
    Else
     MsgBox "行指定エラー"
     Cancel = True
     End If
    End With
End Sub
'=======================================================================
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  Dim 検索値 As String
  Dim col As Long
  With Worksheets("sheet3") '対象シートにする
    If Val(TextBox2.Value) > Application.Max(.Range(検索セル範囲)) Then
     MsgBox "検索値指定エラー"
     Cancel = True
    Else
     検索値 = TextBox2.Text
     col = .Evaluate("IF(ISERROR(MATCH(" & _
               検索値 & "," & 検索セル範囲 & ",1)),0," & _
              "IF(ISERROR(MATCH(" & 検索値 & "," & 検索セル範囲 & ",0))," & _
                "MATCH(" & 検索値 & "," & 検索セル範囲 & ",1)," & _
                "MATCH(" & 検索値 & "," & 検索セル範囲 & ",1)-1))")
               
     TextBox3.Text = .Evaluate("=OFFSET(" & 検索開始セル & ",0," & col & ",1,1)")
     TextBox4.Text = .Evaluate("=OFFSET(" & strng & ",-1," & col & ",1,1)")
     End If
    End With
End Sub


確認してください。

【28365】Re:match関数について
お礼  KIKAKU  - 05/9/3(土) 22:06 -

引用なし
パスワード
   Ichinoseさん、Hirofumiさん
本当に有難うございました。おかげさまで、大変勉強になりました。
またわからない事がありましたら質問させていただきます。
その節は宜しくお願いいたします。

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