VBA 数式の参照先のセルのアドレス取得方法

Question

VBA　数式の参照先のセルのアドレス取得方法

シート3のB5,B6,B7セルには数字が入っています
C5,C6,C7セルには手動で入力した単語が入っています。変更等で変わる場合があります。

シート4のE列4セルには、='シート3'!$C$5　のように数式を入れて参照させています。
同様にシート5のE列4セルには、='シート3'!$C$6　
シート6のE列4セルには　='シート3'!$C$7　のように数式を入れて参照させています。

今回やりたいことはシート3のB5セル+C5セルの値をシート4のシート名 例　1.1ゴリラ
B6セル+C6セルの値をシート5のシート名　　　　　 1.2りんご
B7セル+C7セルの値をシート6のシート名 　 1.3時計
にしたいです。

シート4、5，6のE列4セルからそれぞれ参照先のセル(C5,C6,C7)のアドレスを取得してその隣のセル(B5,B6,B7)のアドレスを取得してと考えているのですが可能でしょうか？
ややこしいですがどうにかしてこの方法で実現したいのですが詰まってしまったためよろしくお願いします。

Sub N()

Dim q As String
On Error Resume Next

o = Sheets("シート2").Index
p = Sheets("シート7").Index

Do While o < p

SheetName = Sheets(o)
SheetName.Activate

q = Worksheets(o).Cells(4, 5).Value

Worksheets(o).Name = q

o = o + 1

    Loop

End Sub

Answer 1

質問のようなセル参照でしか使えませんが、Application.Evaluate メソッド (Excel) を使うのも手かもしれません。

単純なセル参照であれば、参照先のセルのRangeオブジェクトを取得できます。

Sub N()
'https://teratail.com/questions/139224
    
    Dim wb As Excel.Workbook
    Set wb = Excel.ActiveWorkbook
    
    Dim ws2 As Excel.Worksheet
    Set ws2 = wb.Worksheets.Item("シート2")
    
    Dim ws7 As Excel.Worksheet
    Set ws7 = wb.Worksheets.Item("シート7")
    
    Dim o As Long
    For o = ws2.Index To ws7.Index - 1
        
        Dim tgtWs As Excel.Worksheet
        Set tgtWs = wb.Worksheets.Item(o)
        
        Dim E4Cell As Excel.Range
        Set E4Cell = tgtWs.Range("E4")
        
        '数式が単純なセル参照とかで無ければエラー（424:オブジェクトが必要です。）
        Dim srcRng As Excel.Range
        Set srcRng = Excel.Evaluate(E4Cell.Formula)
        
        With srcRng
            tgtWs.Name = .Offset(, -1).Text & .Text
        End With 'srcRng
        
    Next o

End Sub

Comments

[uho1234]

ありがとうございました！
助かりました

Answer 2

Option Explicit
Sub N()
Dim o As Long
Dim p As Long
Dim q As String
Dim r() As String
Dim s As Range
   o = Sheets("シート4").Index
   p = Sheets("シート7").Index
   Do While o < p
       q = Sheets(o).Cells(4, 5).Formula
       r = Split(Mid(q, 2), "!")
       Set s = Sheets(r(0)).Range(r(1))
       Sheets(o).Name = s.Offset(ColumnOffset:=-1).Value & s.Value
       o = o + 1
    Loop
End Sub

Comments

[uho1234]

ありがとうございました！
できました!

Answer 3

VBAなら、
Range.DirectPrecedents プロパティとRange.Offset プロパティメソッドですね。

追記
よく考えたら、DirectPrecedents は他のシートのセルの参照は取得できませんでした。

Answer 4

数式の INDIRECT、OFFSET を使えばできるかと。
後はご自分で考えてください。