同一シート内の同一数式を選択するEXCELマクロ

はじめに

セルに数式を入力し、そのセルをコピーした場合、セルに設定された数式は同じものですが、相対参照を使った場合、数式バーに表示される文字列は違ったものとなります。
この時に、数式バーに表示される文字列が異なっていても相対参照が同じ数式のセルを選択するマクロになります。

まずは数式をセルに入力

まずB2,B7,B12,B17セルにそれぞれ左上のセルを参照する数式を入力します。
その時
B2セルには絶対参照
B7セルには相対参照
B12セルには列のみ絶対参照
B17セルには行のみ絶対参照
となる数式を入力します。
その後それぞれのセルを横３列、縦３行にそれぞれコピーします。
実行結果はこうなります。

その内容を数式で表示した内容は以下になります。

この時、罫線で囲んだ部分がそれぞれ同じ数式ということになります。

ソースコード

Public Sub SelectSameFormula()
    'ActiveCellの数式と同一の数式のセルを検索の対象とする
    Dim strFormulaR1C1 As String
    strFormulaR1C1 = ActiveCell.FormulaR1C1
    If strFormulaR1C1 = "" Then
        Exit Sub
    End If
    
    '総当たりするセルの領域を数式の入力されたセルに限定する
    Dim objFormulaRange As Range
    Set objFormulaRange = Cells.SpecialCells(xlCellTypeFormulas)
    
    Dim Result As Range
    Set Result = ActiveCell
    Dim objCell As Range
        
    '数式の入力されたセルを総当たりして同じ数式のセルを取得
    For Each objCell In objFormulaRange
        If objCell.FormulaR1C1 = strFormulaR1C1 Then
            Set Result = Application.Union(Result, objCell)
        End If
    Next
    
    Call Result.Select
End Sub

使い方

選択したい数式の入力されたセルを選択して
SelectSameFormula()マクロを実行してください。
同じ数式の入力されたセルがすべて選択されれば成功です。
アクティブセルに数式が設定されていなければ何もしません。

解説

数式をR1C1形式で表示した結果をみれば一目瞭然のように、
R1C1形式で取得した数式が等しいセルをすべて選択しているだけです。

余談

ここからは以下の記事のための参考記事となります。

もし、RangeオブジェクトのプロパティにFormulaR1C1がなくFormulaしかなければ、実現は不可能でしょうか？
実は、VBAにはConvertFormulaという関数が用意されていてA1形式の数式をR1C1形式に変換することが出来ます。

上記のソースのFormulaR1C1を使用している個所を以下に書き換えれば同様の結果を得ることが出来ます。
変更前strFormulaR1C1 = ActiveCell.FormulaR1C1
変更後strFormulaR1C1 = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlR1C1, , ActiveCell)
変更前If objCell.FormulaR1C1 = strFormulaR1C1 Then
変更後If Application.ConvertFormula(objCell.Formula, xlA1, xlR1C1, , objCell) = strFormulaR1C1 Then

重要なのはConvertFormula()関数の第4引数と第5引数です。
第4引数についてはマイクロソフトのヘルプによれば次の通り

変換された参照型を指定するXlReferenceType の定数です。
この引数を省略すると、参照の種類は変更されません。

第5引数についてはマイクロソフトのヘルプによれば次の通り

1つのセルを含む Range オブジェクトを指定します。
このセルは、相対参照の基点となります。

マイクロソフトのヘルプは相変わらず不親切で意味が解りません、
内容は変更前または変更後の形式にR1C1形式を指定した場合の、
相対参照の起点となるセルを指定せよということです。
※引数を省略した場合は以下の実験用マクロの結果、セル関数から実行された場合は、セル関数の入力されているセル(Application.ThisCell)。マクロから実行された場合は、ActiveCellが適用されるようです。

つまり今回のケースでは
第4引数は省略することにより、絶対参照は絶対参照のまま、相対参照は相対参照のままR1C1形式に変換させます。
第5引数は変更後の形式にR1C1形式を指定したため、相対参照時の起点となるセル（今回のケースでは数式の入力されているセルそのもの）を指定します。

実験用

'*****************************************************************************
'[概要] ConvertFormula()関数の第5引数を省略した場合のR1C1形式の起点となるセルを調査する
'[戻値] R1C1形式の起点となるセルのアドレス
'*****************************************************************************
Public Function RelativeTo() As String
    Dim str As String
    str = Application.ConvertFormula("A1", xlA1, xlR1C1)
    'A1セルからの相対位置の符号を反転　例：R[-1]C[-1] → R[+1]C[+1]
    str = Replace(str, "-", "+")
    RelativeTo = Application.ConvertFormula(str, xlR1C1, xlA1, , Range("A1"))
End Function