同じ数式を持つセル範囲を取得する - FormulaR1C1

同じ数式が入っている(コピーされている)範囲に対し、
一括で処理を行いたい場合があります。

この時、例えばB1セルに「=A1」と入っていた場合は、
B2セルは「=A2」になるため、Formulaでは判定できません。

この判定を行うには、
数式をR1C1形式で取得するFormulaR1C1プロパティを使用します。

FormulaR1C1であれば上記のB1,B2セルの数式は、
どちらも「=RC[-1]」を返すため一致として判定できるようになります。

サンプルコード

Function Get同じ数式を持つセル範囲(代表セル As Range) As Range

    ' 代表のセルから判定用の数式を取得
    Dim R1C1数式 As String
    R1C1数式 = 代表セル.FormulaR1C1

    ' 数式があるセルをループ
    Dim rng結果値 As Range
    Dim 数式セル As Range
    For Each 数式セル In 代表セル.Worksheet.UsedRange
        If 数式セル.HasFormula Then
            
            ' 同一の数式であれば結果値に追加
            If 数式セル.Formula2R1C1 = R1C1数式 Then
            
                If rng結果値 Is Nothing Then
                    Set rng結果値 = 数式セル
                Else
                    Set rng結果値 = Union(rng結果値, 数式セル)
                End If
            
            End If
            
        End If
    
    Next
    
    Set Get同じ数式を持つセル範囲 = rng結果値

End Function

' 使用例：B1セルと同じ数式を持つセルを赤色に
Dim 同一数式範囲 As Range
Set 同一数式範囲 = Get同じ数式を持つセル範囲(Range("B1"))
    
同一数式範囲.Interior.Color = vbRed
    
End Sub

解説

1つのセルを代表として渡すと、
そのセルの数式と同じ数式を持つセル範囲を返してくれるFunction
を作成しました。

使用例の通り、セルを渡すとセル範囲を返してくれますので、
あとはそのセル範囲に処理を実行してください。

関数の中身は単純なコードで、

1. すべてのセルをループしR1C1数式が同じ数式かを判定
2. 同一だったセルをUnionメソッドで1つのセル範囲に集約

というロジックで返り値を生成しています。

Worksheet.Cellsをループすると膨大な時間がかかりますので、

• UsedRangeを使用してループを「現在使っている範囲に絞る」
• HasFormulaを使用して数式があるセルでだけ数式の判定を実施

という速度対策をしています。

Functionの中身を読まずに使っても特に問題はありませんが、
せっかくなのでFor Each文の参考としてもご活用ください。