異なるワークシートの値のリストを検索し、列と行のヘッダーの両方を返します。

Question

ワークブックの複数のワークシートにある値のリストを検索し、検索値の隣のセルに列ヘッダーと行ヘッダーの両方を返そうとしています。アクティブ ワークシートの最初のデータ列 (ColB) のすべての値に完璧に機能するコードを見つけました。別の列または別のワークシートにある値をリストに追加すると、循環参照エラーが発生し、関数セルに 0 が配置されます。検索する値のリストを最後のデータ行の下の列 1 に配置し、その隣の列に両方の関数を配置しました。

誰かこの問題を解決するのを手伝ってくれませんか?

私のワークブックは、同じ形式のワークシートがどんどん増えて構成されています。各ワークシートには、行 1 に列ヘッダー、列 1 に行ヘッダーがあり、81 行と 40 列にデータが入力されています。検索する値のリストは、最大 1000 個以上になることがあります。

    Function MyROW(MyVAL)
With ActiveSheet.UsedRange
    FFIND = False
    For J = .Column + 1 To .Column + .Columns.Count
        For I = .Row + 1 To .Row + .Rows.Count
            If (Cells(I, J) = MyVAL) Then
                FFIND = True
                GoTo MyEND
            End If
        Next I
    Next J
    MyEND:
    If (FFIND) Then MyROW = Cells(I, .Column)
End With

終了関数

    Function MyCOLUMN(MyVAL)
With ActiveSheet.UsedRange
    FFIND = False
    For J = .Column + 1 To .Column + .Columns.Count
        For I = .Row + 1 To .Row + .Rows.Count
            If (Cells(I, J) = MyVAL) Then
                FFIND = True
                GoTo MyEND
            End If
        Next I
    Next J
    MyEND:
    If (FFIND) Then MyCOLUMN = Cells(.Row, J)
End With

終了関数

セル B84 に次の関数を配置しました: =MyCOLUMN(A84) そして、それをその下のセルに引き下げました。セル C84 では、関数 =MyROW(A84) を使用して同じことを行いました。A2-A82 にある値を A84 に配置すると、両方の関数は正常に機能しますが、B2-AO82 にある値を a84 に配置すると機能しません。