CODEWITHSUNDEEP
excelvba
Alain
Alain

Reputation: 27240

Get the range that a cell points to

I'm trying to come up with a VBA formula that will take a cell, and return the range that that cell references. This is what I have so far:

Function GetAddressCellPointsTo(ByRef src As Range) As Range
    On Error GoTo InvalidSrc
    referenceText = Replace(src.Formula, "=", "")
    Set GetAddressCellPointsTo = Range(referenceText)
    Exit Function
InvalidSrc:
    err.Raise 1, "GetAddressCellPointsTo", "The formula of the source cell must be a single reference to another cell or range."
End Function

The problem is that I know this won't work reliably if the cell doesn't contain a valid range, or if the reference isn't fully qualified in the source cell (i.e. =A1 vs =Sheet1!A1), or maybe if the cell is pointing to a named range.

What I'd like to do is reliably pull the reference to a range if that's what the cell is pointing to, otherwise return an error message.

Upvotes: 1

Views: 125

Answers (1)

Daniel
Daniel

Reputation: 13132

Here's a slight modification that I believe fits your requirement:

Function GetAddressCellPointsTo(ByRef src As Range) As Range
    referenceText = Replace(src.Formula, "=", "")
    On Error Resume Next
    'Return err.Number 1004 if referenceText not on current sheet
    Set GetAddressCellPointsTo = src.Parent.Range(referenceText)
    If Err.Number <> 0 Then
        On Error GoTo InvalidSrc
        Set GetAddressCellPointsTo = Range(referenceText)
    End If
    Exit Function
InvalidSrc:
    Err.Raise 1, "GetAddressCellPointsTo", "The formula of the source cell must be a single reference to another cell or range."
End Function

This will first find the reference on the sheet that src is on. If that fails, which it does for any reference not on the sheet, it will try without specifying the sheet.

Upvotes: 1

Related Questions