[[20240229171328]]　『配列数式をvbaで』（処理が）　ページの最後に飛ぶ

『配列数式をvbaで』（処理が）

={IF( COUNTIF(K:T, A1),
INDEX(C:C, MAX(IF(K:T=A1, ROW(K:T)))))
ていう配列数式があります。

これをVBAで書くとどうなりますか？

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

入力作業をマクロの記録してみるとわかります。
式が入力されているセルで、F2キー押して編集状態にしてEnterで確定するだけで記録されます
（マクロの記録） 2024/02/29(木) 17:43:47

 趣旨を誤解しているかも知れませんが...

    Function test(検索値, ByVal 検索範囲 As Range, 戻り範囲 As Range)
        test = False
        With 検索範囲.Worksheet
            Set 検索範囲 = Intersect(検索範囲, Range(.Cells(1, 1), .UsedRange))
        End With
        If 検索範囲 Is Nothing Then Exit Function
        Dim v() As Variant, c As Long, r As Long, x As Long
        v = 検索範囲.Value
        For r = 1 To 検索範囲.Rows.Count
            For c = 1 To 検索範囲.Columns.Count
                If v(r, c) = 検索値 Then If r > x Then x = r
            Next
        Next
        If x Then Set test = 戻り範囲.Rows(x)
    End Function

（白茶） 2024/02/29(木) 18:06:21

 あ、「Option Compare Text」付けとかなきゃダメですかね

（白茶） 2024/02/29(木) 18:17:20

 ユーザー定義関数の別案です。

 Function test(検索値 As Range, 検索範囲 As Range, 戻り値列 As Range) As Variant
     Dim found As Range
     Set found = 検索範囲.Find(検索値.Value, , xlValues, xlWhole, xlByRows, xlPrevious)
     If Not found Is Nothing Then
         test = 戻り値列(found.row, 1)
     Else
         test = False    '該当なし
     End If
 End Function

 使い方は、下記です。
   =test(A1,K:T,C:C)
（xyz） 2024/03/01(金) 06:07:04

xyzさん

コードを試した所
A1セルでCtrl+Sgift+Enterを押すと値が出ます。

押さないと
VALUEがでます。
なぜでしょうか？

ちなみに
検索値、検索範囲、戻り値列は実際の参照する箇所を入力した方がよいでしょうか？

（処理が） 2024/03/01(金) 15:33:49

 それは配列数式ではありません。
 ユーザー定義関数は、ワークシート関数と同じように使って下さい。
 それぞれの引数の意味は、使い方も示しましたし、コードも提示していますので、想像がつくと思います。

 >A1セルでCtrl+Sgift+Enterを押すと値が出ます。 
 >検索値、検索範囲、戻り値列は実際の参照する箇所を入力した方がよいでしょうか？

 どのセルにどんな式を入れたのでしょうか。
 どういう目的で、どのようなことをされたのか、想像できません。

（xyz） 2024/03/01(金) 16:08:27

やりたいことは
検索値を検索範囲の下からから探し、一番初めに見つかった場所から右に9列先を抽出したいです。
検索値は検査範囲の中に複数あり、
VlookUPだと一番上の値しか抽出できないためマクロで出来ないかと思いました。

現状は
検索値は検索する値というシートのB12セルです。
検索範囲は検索する範囲というシートのK列～S列です。
右に9列というのはS列となります。

現状
Module1に
Function test(検索値 As Range, 検索範囲 As Range, 戻り値列 As Range) As Variant

     Dim found As Range
     Set found = ThisWorkbook.Worksheets("検索する範囲").Range("K:T").Find(ThisWorkbook.Worksheets("検索する値").Range("B12").Value, , xlValues, xlWhole, xlByRows, xlPrevious)
     If Not found Is Nothing Then
         test = ThisWorkbook.Worksheets("検索する範囲")Range("S")(found.row, 1)
     Else
         test = False    '該当なし
     End If
 End Function
で
検索する値のシートのF10セルには
=test(B12,検索する範囲!K:S,検索する範囲!S:S)
という式が入っています。
これで行うと
F10セルに検索値を入れても反応せず
F10セルを編集状態にしてCtrl+Sgift+Enterを押すと値を抽出します。
何回か試したら合っているときとあっていない時があり困っております。

すみません。

（処理が） 2024/03/01(金) 20:26:35

 ユーザー定義関数は変更せず、そのまま標準モジュールにコピーしてください。
 Function test(検索値 As Range, 検索範囲 As Range, 戻り値列 As Range) As Variant
     Dim found As Range
     Set found = 検索範囲.Find(検索値.Value, , xlValues, xlWhole, xlByRows, xlPrevious)
     If Not found Is Nothing Then
         test = 戻り値列(found.row, 1)
     Else
         test = False    '該当なし
     End If
 End Function

 "検索する値"というシートのF10セルに
 =test(B12,検索する範囲!K:S,検索する範囲!S:S)
 と入れてみてください。
 結果がF10セルに表示されるはずです。

（xyz） 2024/03/01(金) 22:30:41

xyzさん

無事にできました。

ひとつ気になることが
検索する値は
159-A278-Rなどですが
試しに数字の7とか5とかを入れてみると、
違ったものが出てきます。
本来は検索する値が検索する範囲になければFalseが表示されると思いますが
なぜでしょうか？

すみません。
（処理が） 2024/03/02(土) 08:31:07

 > 検索する値は
 > 159-A278-Rなどですが
 > 試しに数字の7とか5とかを入れてみると、
 > 違ったものが出てきます。
 意味がわかりません。
 > 試しに数字の7とか5とかを入れてみると、
 とは？
 検索値を7や5に変更したら、ということですか？
 そりゃ別の結果になるのに不思議はないです。

 もう少し説明してください。

 なお、関係する事項ですが、
 検索はxlWhole、つまり完全一致で検索する仕様、と言うことに留意してください。
 部分一致ではありません。 
（xyz） 2024/03/02(土) 08:37:48

検索する値を検索する範囲にない値に変更する事です。
例えば7や5の一文字はないので
入れてみたら、FALSEではなく、
全く関係ないあたいが抽出されました。

（処理が） 2024/03/02(土) 08:56:41

 あ、そうですか。では下記のことを実行して結果を知らせてください。

 Set found = 検索範囲.Find(検索値.Value, , xlValues, xlWhole, xlByRows, xlPrevious)
 のあとに
 MsgBox 検索値 & vbLf & found.Address & vbLf & found.Value
 を一行だけ追加してください。

 そして、再度、入力すると、メッセージボックスが表示されるはずです。
 内容は、
 ・検索値
 ・ヒットしたアドレス
 ・そのヒット箇所の値
 です。

 なお、ヒットしない場合、表示が#VALUEに変わりますが、それは追加行の影響ですから気にする必要ありません。
 不要になったら、その追加行は消して下さい。
（xyz） 2024/03/02(土) 09:11:21

実行してみました。

結果
アドレスは関係ない箇所を示してみました。

考えてみたら
検索範囲が
広すぎてその中から該当するものを探していたみたいです。
大変失礼いたしました
そこで検索範囲を
検索する範囲!K:Sから検索する範囲!K:Kに変更したら該当しないものはFALSEと出ました。
（処理が） 2024/03/02(土) 10:17:13

コメント返信：

[ 一覧(最新更新順) ]

[[20240229171328]] 『配列数式をvbaで』（処理が） ページの最後に飛ぶ

[[20240229171328]]　『配列数式をvbaで』（処理が）　ページの最後に飛ぶ