【ExcelVBA】数式自体を文字列検索してどのセルで使われているかを特定するには

この記事では、数式自体を文字列検索してどのセルで使われているかを特定する方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

Table of Contents

【動画】数式自体を文字列検索してどのセルで使われているかを特定する実際の動き(ヘッダあり)

本題に入る前に、まずは次の動画をご覧ください。

Findメソッドを使って、数式自体を文字列検索しています。

複数存在する場合はFindNextメソッドを使ってセルのアドレス全てを特定しています。

見つかった場合は、A列のセルに見つかった分のセルのアドレスを出力しています。

目次へ戻る

マクロ作成の流れ

STEP.1

Findメソッドを実行して数式自体を文字列検索する

Findメソッドを実行して数式自体を文字列検索します。

STEP.2

FindNextメソッドを実行してさらに数式自体を文字列検索する

FindNextメソッドを実行してさらに数式自体を文字列検索します。

目次へ戻る

コードの例

Excelのマクロのコード(例)

Option Explicit

Sub findFormulaStr()

    Dim rng         As Range    'Rangeオブジェクト格納用変数
    Dim firstAddrs  As String   '最初に見つかった数式が入力されているセルのアドレス
    Dim formulaStr  As String   '検索する数式
    Dim cnt         As Long     'カウンタ用変数
    
    '検索する数式を取得する
    formulaStr = Worksheets("work").Range("searchFormula").Value
    
    'カウンタを初期化する(数式を出力するセルの行位置)
    cnt = 4
    
    '検索先のセル(の範囲)
    With Worksheets("work").Range("C1:J11")
    
        '数式を検索する
        Set rng = .Find(What:=formulaStr, lookin:=xlFormulas, LookAt:=xlPart)
        
        If Not rng Is Nothing Then
        
            '数式が存在する場合
        
            '(一番最初に)見つかった数式を変数に保持しておく
            firstAddrs = rng.Address
                        
            Do
                                                
                '数式・計算式が使われているセル位置をセルに設定する
                Worksheets("work").Range("A" & cnt).Value = Split(rng.Address, "$")(1) & rng.Row
                                
                'セルにハイパーリンクを設定
                'ハイパーリンクをクリックするとセルに移動する
                Call Hyperlinks.Add( _
                Anchor:=Cells(cnt, 1), _
                Address:="", _
                SubAddress:=Split(rng.Address, "$")(1) & rng.Row, _
                ScreenTip:=Split(rng.Address, "$")(1) & rng.Row)
            
                '(次に)見つかった数式を取得する
                Set rng = .FindNext(rng)
                
                cnt = cnt + 1
                
            '(一番最初に)見つかった数式と検索した数式が一致する場合はループを抜ける
            Loop While firstAddrs <> rng.Address
        
        Else
        
            '数式が存在しない場合
        
            MsgBox "数式が見つかりません"
        
        End If
    
    End With
    
    '後処理
    Set rng = Nothing

End Sub

注目すべきコード①

最初に見て頂きたいのは20行目です。

        '数式を検索する
        Set rng = .Find(What:=formulaStr, lookin:=xlFormulas, LookAt:=xlPart)

以上のコードは、Findメソッドを実行して数式自体を文字列検索する処理のコードです。

Findメソッドの引数には次の情報を指定して実行しています。

文字列検索する数式(what)
検索対象(lookin)←今回は数式を検索するので「xlFormulas」を指定
部分一致(LookAt)←今回は一部が一致するセルを検索するので「xlPart」を指定

注目すべきコード②

次に見て頂きたいのは22行目と50行目です。

        If Not rng Is Nothing Then

        Else

以上のコードは、Findメソッドを実行した結果検索対象の数式があるかないかを判定する条件文です。

数式がある場合は22行目の条件に合致し、数式が無い場合は50行目の条件に合致します。

注目すべきコード③

次に見て頂きたいのは27行目です。

            '(一番最初に)見つかった数式を変数に保持しておく
            firstAddrs = rng.Address

以上のコードは、一番最初に見つかった数式のアドレスを変数firstAddrsに保持しておくコードです。

なぜ一番最初に見つかった数式のアドレスを変数firstAddrsに保持しておくのかというと、後続のFindNextメソッドで数式を最後の数式まで探し終わったらFindNextメソッドはもう呼び出さないようにするための判定に使いたいからです。

FindNextメソッドを使って複数の数式を全て特定する場合、FindNextメソッドを繰り返し実行する必要があるのですが、最後の数式まで探し終わると再度最初の数式からFindNextメソッドは探そうとします。

最後の数式まで探し終わるとFindNextメソッドは次に最初の数式を特定するので、一番最初に見つかった数式のアドレス(変数firstAddrsに格納済み)と合致します。

なので、その合致したタイミングでループを抜けるよう判定しループ処理を終了させます。

ちなみにループ処理から抜けるかどうかを判定する条件判定は48行目で行っています。

            '(一番最初に)見つかった数式と検索した数式が一致する場合はループを抜ける
            Loop While firstAddrs <> rng.Address

注目すべきコード⑤

次に見て頂きたいのは32行目から40行目です。

                '数式・計算式が使われているセル位置をセルに設定する
                Worksheets("work").Range("A" & cnt).Value = Split(rng.Address, "$")(1) & rng.Row
                                
                'セルにハイパーリンクを設定
                'ハイパーリンクをクリックするとセルに移動する
                Call Hyperlinks.Add( _
                Anchor:=Cells(cnt, 1), _
                Address:="", _
                SubAddress:=Split(rng.Address, "$")(1) & rng.Row, _
                ScreenTip:=Split(rng.Address, "$")(1) & rng.Row)

以上のコードは、特定した数式のセル位置をA列のセルに出力し、ハイパーリンクを設定している処理のコードです。

32行目では、特定した数式のセル位置をA列のセルに出力ています。

36行目では、特定した数式のセル位置にハイパーリンクを設定しています。

注目すべきコード⑥

次に見て頂きたいのは32行目から40行目です。

            '(一番最初に)見つかった数式と検索した数式が一致する場合はループを抜ける
            Loop While firstAddrs <> rng.Address

以上のコードは、ループ処理から抜けるかどうかを判定する条件判定のコードです。

FindNextメソッドで数式を最後の数式まで探し終わったらfirstAddrsに格納されたアドレスと検索した数式のアドレスが一致するので、そのタイミングでループを抜けるという判定を行っています。

目次へ戻る

動作確認

マクロ実行前

今回用意したExcelファイルは以下で、I列のセルには「SUM」を使った数式が入力されており、J列のセルには「AVERAGE」を使った数式が入力されています。

このExcelのシートの表から、「SUM」と「AVERAGE」の数式を文字列検索してどのセルに使われているのかを検索します。

マクロ実行後

マクロを実行すると、「SUM」と「AVERAGE」の数式がどのセルに使われているのかを特定することができました。

下の画像は、まずは「SUM」で文字列検索した数式のセル位置をA列に出力しています。

下の画像は、「AVERAGE」で文字列検索した数式のセル位置をA列に出力しています。

目次へ戻る

最後に

本記事では、数式自体を文字列検索してどのセルで使われているかを特定する方法についてご説明しました。

例えばSUMやAVERAGEといった数式がどのセルに使われているのかを特定したいという場合には参考にしてみてくださいね。

Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら

Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。

Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。

→　受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら

目次へ戻る